Menu
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
610
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.
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 .
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.
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.
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
