sequence numbers - with no gaps!
ChrisS.Oct 1 2009 — edited Oct 2 2009I seek some advice from anyone with first hand experience in this area.
My need is simple; generate a new ID number for every order for use as the primary key.
Obviously this simple exercise could be a bottleneck as volumes ramp up, so I was pleased to find the Oracle feature to generate such numbers (efficiently presumably). However, upon closer reading it was apparent that aborted transactions would result in the loss of the number allocated to to that order if another transaction had obtained the next sequence number already (as one would want it to, rather than serializing transactions).
I don't want to have gaps in the sequence as the system needs a high degree of auditability. I am thinking of utilising the Autonomous Transaction feature to write the newly acquired sequence number to the order file with a flag indicating that the record is incomplete, and that record is to be skipped by normal system applications (but available to audit reports which will interpret the reason correctly), then returning to the far more extensive main transaction which updates many tables.
The other alternative is to control sequence numbers within the application itself, or try to get the sequence number as the very last step before writing the new order record, but this probably won't guarantee gapless reliability either. The ID itself has no significance, and it doesn't matter if an ID was used out of strict date/time sequence, so I really don't want to execute these updates serially.
So, my question is this:
a) do most people use the builtin sequence number feature?
b) if so, is there a standard technique for avoiding or accounting for gaps?
c) if the application must take over this task, should the counter for the last used ID be in a single row table to minimise bottlenecks caused by the thisrecord being locked? There will be several such counters for different aspects of the application, and i thought of having each in its own row in a small table, but I'm concerned about the whole table getting locked and freezing out otherwise-unrelated updates.
Thanks for any advice you may have.
CS