You'll have a problem with sequences anyway. As in multi-user environments, two users could add something and one cancels and the other not. Then it isn't even just a problem about the split second of the moment of saving.
Anything you want to have a final and mandatory sequence number needs to be queued up to the definite storage and only after that, you can know the number.
Autoinc is designed to protect against double numbers, so it's not reverted. It's also more perfect for that case than anything you can roll yourself, as it can make use of the locks it needs anyway for any write, automatic locks you even have without manually locking anything. But the design is not for gap-free numbering, just also think of the case two users get number N and N+1 and the user having N cancels, the user having N+1 then doesn't automatically change to N. The number N+1 in memory of the client would need to have a quantum mechanically entanglement with the central counter to know it has to change and that's not what we have (and also won't ever have with quantum computers). The design of the counter is only to care for itself, not care about gaps and fill them in later.
I once programmed a table holding the next 10 numbers of which one is reserved by a user and when he cancels it is put back into that pool. That was making it more comprehensible, but that customer still was aiming for the impossibility of never having any gap. With this pool, you do have a gap until the next invoice takes the gap number. And then you still have a later invoice with a lower invoice number.
If you need numbers in chronological order you have one extremely simple solution, which also satisfies the IRS: You never cancel and revert/not store invoices, you give them a voided status.
So any number generated by autoinc means you store that record, you never revert it, if the invoice should be voided, you do exactly that, you void it. That's what accounting teaches, doesn't it? Even if you implement the queue idea and invoices only get their number at saving and not before, you do have the problem of an error about the invoice being recognized after it has its number, so voiding is something you'd implement anyway.
Bye, Olaf.
Olaf Doschke Software Engineering