Re: Do Sequences Have Any Disadvantages?

Tom McCready wrote:
>
> I would like to hear when it is appropriate to use Sequences
> and when it is not. Are there disadvantages to Sequences which
> would force us to keep our own table of high keys, incremented
> by one each time a record is added?
> Many of our applications use a "one-up" number. The Sybase
> implementation has some disadvantages. How about the Oracle
> implementation? Thank you,
> Tom McCready, DBA, Library of Congress 202-707-5511
> --
> MZThis is precisely what you should be using sequences for. They get you
around the potential problems you will encounter when trying to implement your alternative solution. Let's say you have a table called BOOKS, whose primary key is book_id, which is nothing more than a "one-up" number that you referred to earlier.

Now if you have an application used by more than one person that creates records in the BOOK table you have a potential problem. User A goes to commit a new record and you select the next sequence number from your table and put it in the record as book_id. While this is going on and before the record gets committed so that the rest of the world can see it User B goes to save a new record as well. Since User B can't see the new record User A created yet as the commit has not completed, User B ends up with the same book_id.

If you use a sequence to populate this column this cannot occur. The drawback is that you are not guaranteed a contiguous set of numbers (i.e., some numbers may get wasted). If the insert fails, the sequence will have been incremented in the database and there's no turning back. 99.9% of the time this is not an issue because you are using the sequence to populate an identifier that is meaningless to the end user (or anyone else for that matter) and is just used as an identifier.

Chris Halioris
Tactics, Inc. Received on Fri Feb 07 1997 - 00:00:00 CST