Perhaps an AQ...
September 03, 2008 - 9:19 pm UTC
Reviewer: Warwick Sands from Sydney, NSW
I remember the topic, but couldn't find it either. From memory, another respondent suggested using a "non-supported" feature something like:
SELECT value FROM some-table FOR UPDATE skip locked
As I remember it the final thoughts were to create a number of sequence values and then insert these into an AQ.
The calling process would retrieve the next-sequence-value from the queue. If the user rolled-back then the value would be returned into the queue.
I haven't tried this but it sounded promising at the time.
Love the site. Thank you and looking forward to seeing you at the Gold Coast conference in October.
September 04, 2008 - 8:45 am UTC
but you see the 'bug' waiting to happen there right. It is NOT GAP FREE.
the end of month processing would easily see gaps, easily. And therein lies the rub - you are back to not being gap free, hence it will not work.
do not use skip locked, it works in a very special way with a subset of SQL and is ignored in many cases (eg: it doesn't work the way you think it should, it works the way it needs to work for AQ)