Re: User Sequence Skipping Numbers???

From: Rudy Zung <_spamkill_zungr_at_prograph-inc.com>
Date: Tue, 6 Apr 1999 18:21:09 -0400
Message-ID: <7edtqv$m4m$1@mailhost.prograph-inc.com>

klabranche wrote in message ...
>I was just wondering...Wouldn't you have the same problems with your
>table as those described by Rudy Zung(the first response)?
>
>I have gotten around this problem by using a select statement with
>finding the max value in the primary key field and adding one to it.
>
>I don't know if this is the best way to get around skipping values
but
>so far I have had no problems with it and I haven't noticed any side
>effect.
>
>Kevin LaBranche

The trade off is reasonably quick response at the risk of holes in your ID/sequence/counter values, or guaranteeing no holes at the expense of having later transactions block while awaiting earlier transactions to commit or rollback; depending on how long your transactions work, it could be neglible, or it may look like Oracle is not responding. You can block other trasactions by select for update the record which represents your max value; you might also consider select for update no wait to prevent blocking, but if unsuccessful, will raise an exception. Once you block other transactions pending a commit or rollback of the first transaction, you guarantee that by the time the second transaction manages to get a good ID value, the first transaction is no longer in a position to have gotten an ID value and then wasted it by doing a rollback which opens up the hole (as long as the ID getting SQL statement and other SQL statements that use the ID belong in the same transaction.)

When you select the max of the PK field, Oracle will select whatever is the max COMMITTED value. For instance, your table has PK 1, 2, and 3. Along comes Tom, he figures that he should insert PK of 4 (max + 1); before he can commit, Jane comes along, her select will also/only see the committed 1, 2, 3, so she'll also use PK of 4. At this point, Jane will block pending resolution of Tom's transaction. If Tom rollsback, Jane can proceed; if Tom commits, Jane will get an ORA-00001 exception.

You can see this happen by:

    create table foo (k number(5));
    alter table foo add constraint primary key foo_pk (k);

    insert into foo values (1);
    insert into foo values (2);
    insert into foo values (3);

    commit;

What you have now is a table with 3 records, and PKed on its sole field.
Now, start up 2 SQLPlus sessions and log in both sessions with either the same username, or a username that will have access and update privs on the foo table.

Session1: select max(k) + 1 from foo;
Session1: insert into foo values (4);
Session2: select max(k) + 1 from foo; -- notice also 4
Session2: insert into foo values (4)

At this point, Session 2 gives you that "query is executing" dialog; it is blocked.
Go back to session1 and either commit or rollback; session 2 will almost immediate finish its query and either get a violation exception (if session 1 commits) or will succeed if session 1 rollsback. So if your transactions are short and quick, you might be lucky in that transactions never stay uncommitted long enough to block, but there is always that small percentage of time when you will get some exception like the above if session 1 commits.

...Ru Received on Tue Apr 06 1999 - 17:21:09 CDT