Having done gapless numbering for some users of accounting software,
there are two suggestions I would make.
The first is that any sort of gapless numbering inherently runs into
scalability. You HAVE to lock relevant records, and this means that
only one insert can run at a time, and it must commit before the next
insert can run. This means you have to keep your transactions short
and predictable in terms of table order. The suggestion of using for
update is a good one, but it doesn't entirely get rid of the problem,
which is inherent in ensuring gapless numbering in a system with
concurrent transactions.
The second is that you absolutely should use this approach as rarely
as you can get away with. If it isn't required, don't use it!
Best Wishes,
Chris Travers