Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle Sequence nextval is jumping number back and forth

I created a new table and a new sequence, I have two C# web services trying to insert records into this table using same query utilizing mySequence.nextval (and yes I checked it many times, they both use mySequence.nextval).

The two web services are inserting rows to the table, but the mySequence.nextval is returning numbers out of sequence

Here is how the records were created, showing PrimaryKey which gets its value from mySequence.nextval

1 21 22 23 2 3 24 25 4 27 28 5

So far no duplicates but why is mySequence.nextval jumping back and forth? and should I worry about it

Update: The sequence is created with cache_size = 20

like image 610
EGN Avatar asked Jun 26 '14 21:06

EGN


People also ask

Does Nextval increment the sequence?

The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value.

How do you use seq Nextval?

You can use NEXTVAL (or CURRVAL) in the SET clause of the UPDATE statement, as the following example shows: UPDATE tab1 SET col2 = seq_2. NEXTVAL WHERE col1 = 1; In the previous example, the incremented value of the seq_2 sequence, which is 2 , replaces the value in col2 where col1 is equal to 1 .

Is sequence Nextval thread safe?

Yes it is threadsafe. Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.


1 Answers

I will wager that your database is running RAC (Real Application Clusters). Assuming that is the case and that you create the sequence with all the default settings, that's the expected behavior.

The default setting is to cache 20 values. Each node in the RAC cluster, by default, will have a separate cache. Assuming that you have a cluster with two nodes A and B, the first time a nextval is requested on A, A will cache values 1-20 and return a value of 1. If the next request for a nextval is made on B, B will cache values 21-40 and return a value of 21. From there, the value you get will depend on the node that your connection happens to be running on.

Generally, this shouldn't be a problem. Sequences generate unique numbers. The numbers generally need not be consecutive. If you really need values to be returned sequentially because you are doing something like ordering by the sequence-generated value to determine the "first" or "last" row, you can use the ORDER clause when you create the sequence to force values to be returned in order. That has a negative performance implication in a RAC database, however, because it increases the amount of communication that needs to go on between the nodes to synchronize the values being returned. If you need to determine the "first" or "last" row, it's generally better to add a date or a timestamp column to the table and order by that rather than assuming that the primary key is generated sequentially.

like image 187
Justin Cave Avatar answered Nov 15 '22 16:11

Justin Cave