On Mon, 24 May 1999, Andreas Reinbrecht wrote:
> Hi there - I have a question that is giving everybody in our company
> headaches. We are using oracle as the database for a MAGIC application
> (which has been running without any problems for at least 5 years).
>
> The problem we have is that we have a 0 record created (unique key) in one
> of tables. The programs that create a new 'Product' in the table, call a
> separate little resident task that grabs the next oracle sequence in
> embedded SQL:
>
> SELECT psseq.nextval FROM dual
>
> Is the what the query looks like.
>
> Now to the question: I need to know exactly how oracle assigns sequences,
> how locking is handled by the database, and what kind of locking is taking
> place.
>
> A theory that I have is that this program that returns the new sequence
> after the SQL is executed, might return a zero if 2 users do the .nextval at
> exactly the same time. But before I can change the program, I need to know
> whether you think I am looking at the right place, the red-tape involved in
> updating the system is extensive .
>
> The task that runs the query is set to "Lock before Update", "Retry on
> Locked Record", and "Skip on Fail". Seeing as sequences aren't physical
> 'records' because the number is assigned dynamically, I think the record
> locking that MAGIC implements falls through, and the "Skip on Fail" might
> actually tell the program to abort if Oracle encounters an error, *other*
> than a locked table/record.
>
> Well, I surely HOPE you can try and help me explaining exactly what happens
> in the oracle process!
>
> Thanks in advance,
> Andi
>
Andi,
I hope that I can help out. Oracle does not handle sequences like it does
other objects such as tables. For example, if you insert 10 records with
the NEXTVAL and then issue a ROLLBACK command, the sequence does not get
rolled back. Rather, the 10 records will have incremented the sequence and
the next insert will have the 11th value of the sequence. This will result
in "holes" in the sequence. Oracle chose this so that multiple people can
safely use a sequence without fear of duplicate values.
So, if two users simultaneously grab the NEXTVAL, they will each be
assignment unique numbers. Oracle can do this because it caches the
sequence in memory. The init.ora parameter SEQUENCE_CACHE_ENTRIES controls
the size of this cache.
If you want to "skip on fail" then you should determine the error NUMBER
being returned and then have the program decide if it is a "fail" type of
error.
For the "retry on lock" you can determine if the record is locked first.
Review the V$LOCK data dictionary view for more information on how to do
this.
Best regards to everyone in the ".za" domain!
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 295+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page