I can't see how using an id_table should avoid broken sequence nr. in a transaction context.

simple test case: current nr=100;
1. User_A needs a nr so he occupy 101
2. User_B needs a nr so he occupy 102
3. User_B finishes transaction with 102
4. User_A failed so 101 is not used
5. User_C needs a nr so system is suggesting 101 (maybe nr is used or not...)
6. Meanwhile User_D will provide a report and miss 101

Working with locking is not recommended in c/s environment. From theory there should not be a broken sequence nr in database at any time. That's only possible if you create these numbers in one transaction context. Multi user systems are working in different transaction context. So there are always possibilities to create gaps in sequences. Of course you can avoid it with special business rules (don't post this before finishing this if not take phone and make a call etc.) but this another topic :)

BTW, your test case doesn't reflect random delays in completing transactions!

Mit freundlichem Gruss & Best Regards

Andreas Pohl
apohl@...
www.ibp-consult.com