Re: [SQL] begin/end/abort work for sequences?

Thus spake Kyle Bateman
> Do you know if there is a workaround?  In my particular situation, it is critical
> that all instances of the sequence actually get used.  If a transaction is
> aborted, I lose an instance and everything gets messed up.

I had to solve this once in another RDBMS (Progress) and this is how I
did it.  I had a table of available sequence numbers consisting of two
fields, a name and a number.  The table was seeded with 10 entries for
each required sequence numbered 1 to 10.  When I needed a sequence I
started a transaction and grabbed and locked the lowest numbered record
for that sequence that wasn't locked.  I save the number then add 10 to
it.  If I abort the transaction, the number remains unchanged.  Under
this scheme 10 users can be working on the same sequence without locking
others out.  Numbers can be used out of sequence from time to time if
someone aborts but they all get used eventually.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.