4 ms·
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
by balls187 7y ago
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.
- seppel 7y agoA database is also exceptionally good at doing transactional stuff like atomically incrementing something. I'd even say: This is something that belongs in the database and not in some brittle application logic.
- mtVessel 7y agoGenerally, I agree. But in many RDBMSs, auto-increment features explicitly do not guarantee gap-less sequential ordering. If anything throws, it's easy to end up with discarded numbers, in which case you need to catch all errors, inspect the state of the sequence and/or the entity you're populating, and re-seed the sequence. At this point, I'd argue you've already left the pristine gardens of set theory and wandered into the thorny brambles of app dev.
- LoSboccacc 7y agoah! TIL: MYSQL - https://stackoverflow.com/questions/449346/mysql-auto-increment-does-not-rollback https://stackoverflow.com/questions/449346/mysql-auto-increm... POSTGRES - https://stackoverflow.com/questions/2095917/sequences-not-affected-by-transactions https://stackoverflow.com/questions/2095917/sequences-not-af... thanks!
- vivekseth 7y agoI don't think it is not possible to enforce perfectly sequential numbers at the application level. 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).
- dkhenry 7y agoI think by "application level" they are saying "using transactions at the application level". If you require a strict sequence of numbers then you BEGIN, READ, INSERT, COMMIT, that is really what you want so just do it explicitly. If the COMMIT fails because of a duplicate key then you start over.