When I used to write SQL, I always worried that the value of current () would not be absolutely equal to the value I got last time Nextval ().
Nextval () can be accessed concurrently by other threads.
Let's start with the conclusion:
When you get a database connection, first nextval (),
And then whatever else you do with this sequence, the value you get with current () will always be the same as the value you just Nextval ().
At this time to understand why when I want to view the database directly in a sequence currval, error, must first Nextval (), to execute Currval ()
I did an experiment, using the same user to open two database connections A, B
The first step
Call Nextval () with connection A to get a value of 1
Using connection A to call current (), the obtained value is always 1;
Step Two
Call Nextval () with connection B and get a value of 2
Using connection B to call current (), the obtained value is always 2;
Step Three
Then the current () is called with connection A, and the obtained value is still 1;
The following transfer from Baidu Bar http://tieba.baidu.com/f?kz=185551317, the text should be specifically for the Informix database, some may not be fully applicable and other data.
Using Nextval
The first time a sequence is accessed, the reference sequence. You must first refer to sequence before currval. Nextval. For the first time, Nextval is referenced, returning the initial value of the sequence. Each subsequent reference to Nextval, increments the sequence value with the defined step and returns the new value of the sequence after the increment.
You can add only one time to a given sequence in an SQL statement. Even if sequence is specified more than once in a single statement. Nextval, the sequence is also added only once, so each sequence. Nextval appears in the same SQL statement that returns the same value.
use Currval
if sequence. Currval and sequence. Nextval are present in an SQL statement, the sequence is incremented only once. In this case, each sequence. Currval and sequence. The Nextval expression returns the same value, regardless of sequence in the statement. Currval and sequence. The order of the Nextval.
If you want to get the value of just Nextval (), use Currval () with confidence, and the return value of Currval () will not be confused by the concurrency of Nextval ()