Recently I had a question about sequences and their behavior when they are accessed concurrently by multiple users/sessions.
Surprisingly there is very little on this topic from Oracle (or for that matter on the web - search).
First some background (jump to the end if you wish to see my conclusion based on my research):
CURRVAL and NEXTVAL (collated from various documents on the net)
A sequence is a schema object that generates sequential numbers. When you create a sequence, you can specify its initial value and an increment.
CURRVAL returns the current value in a specified sequence. Before you can reference
CURRVAL in a session, you must use
NEXTVAL to generate a number. A reference to
NEXTVAL stores the current sequence number in
NEXTVAL increments the sequence and returns the next value. To get the current or next value in a sequence, use dot notation:
Each time you reference the
NEXTVAL value of a sequence, the sequence is incremented immediately and permanently, whether you commit or roll back the transaction.
After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. You can use
NEXTVAL only in a
SELECT list, the
VALUES clause, and the
Any reference to
CURRVAL always returns the sequence's current value, which is the value returned by the last reference to
NEXTVAL. Note that before you use
CURRVAL for a sequence in your session, you must first initialize the sequence with
Within a single SQL statement, Oracle will increment the sequence only once per row. If a statement contains more than one reference to
NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of
NEXTVAL. If a statement contains references to both
NEXTVAL, Oracle increments the sequence and returns the same value for both
NEXTVAL regardless of their order within the statement.
A sequence can be accessed by many users concurrently with no waiting or locking.
From my research here is what I have determined:
- Concurrent access of NextVal on a sequence will always return separate values to each caller. (Unless the sequence has been setup to cycle values - in which case duplicate values are possible).
An important point to remember is that a sequence is under no circumstances ever rolled-back. For this reason sequences can end up with holes.
- CurrVal returns the last value generated for a sequence when NextVal was called on it. The CurrVal is independent for each session (where a session is a distinct connection, as a single database user, to the database by an external program). This means that CurrVal will always return last value generated for you, regardless of how many times NextVal is called by other users on that sequence.
So sequence can be safely used concurrently and you do not have to worry about CurrVal returning you some other users current value for the sequence.
As an aside, if you are using a sequence to generate a record id and if you are trying to determine the last inserted rowid then you can use the returning keyword in Oracle.
eg: insert into tableName(table_id,.....) values (mySequence.NextVal,......) returning table_id into variable_name;
(this forum post shows how returning keyword can be used when the row id is updated using an on insert-before trigger using an ODP.net example - http://forums.oracle.com/forums/thread.jspa?messageID=1719562�)
Here is a good article that delves into the depths of Oracle Sequences: http://www.oracle.com/technology/products/rdb/pdf/0307_sequences.pdf