Re: Sequence without holes

P B <pbeliveau_at_avcorp.com> wrote in
news:1j5uqu4r412rcqfjg4d978mhhal5qkimmc_at_4ax.com:

> Question #1
>
> I would like to have a sequence but as I understand it, if for
> some reason a user make a rollback or what so ever, I loose his
> sequence number.
>
> I don't want that. I want a sequence that has no hole, so if a
> user make a rollback, the next user that request a number would
> receive the number that was rolled back.

As others have pointed out, sequences don't work that way and that's by design, they are meant to perform quickly which means no logging -- thus no rollback! :)

If you're intent on requiring gap-less numbers, you're going to need to build your own sequence table. Your first stab might be to build a table with a single row and use it to dispense values. The problem is that this will be a bottleneck in a system with a high number of transactions.

What you should consider doing is building a sequence table with multiple rows. The rows will map to sets of users; ideally one row per connection. Each row will have a block of id's that it can dispense.

Now you can write your dispensing code to only lock the row for that particular connection. You won't have gaps but you also won't have monotonic numbers dispensed either.

This may or may not satisfy your client.

HTH

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com