If it was a business requirement that you have perfectly sequential invoice numbers with no gaps, do it at the application level, not at the storage level.
Let the database do what it's great at doing: efficiently store and retrieve data.
Let the database do what it's great at doing: efficiently store and retrieve data.
MYSQL - https://stackoverflow.com/questions/449346/mysql-auto-increm...
POSTGRES - https://stackoverflow.com/questions/2095917/sequences-not-af...
thanks!
This is the same problem as multiple threads trying to increment the same global variable. Unless there is mutual exclusion while the variable is being read/incremented there will be race conditions.
I think the only way to enforce mutual exclusion for applications would be at the database layer (or any other layer where there is 1 resource that is shared between each application peer).