Re: Sequence without holes

P B wrote:
> 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.
>

You don't even have to rollback. I can put a 'select sequence_name.nextval from dual' in a pl/sql loop and burn up a bunch of sequence numbers.

Sequence numbers are not meant to have any intelligence, so having gaps is not a problem. If you want a gap-less sequence number, you'll have to manufacture your own by putting it in a "lookup" table. Of course, as soon as you do that, you'll run into all kinds of locking problems as multiple people go after the same row in update mode.

> Question #2
>
> How do I lock a row that does not exist? I did not yet insert the row
> but I will soon and I don't want other user to use my key. I dont want
> to lock the whole table because I'm not the only who use it.
Where are you getting your 'key'? Isn't it better to lock the key than to lock the new record or - gasp! - the whole table? Received on Thu Oct 17 2002 - 15:32:05 CDT