Oracle sequences and concurrency

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 CURRVAL. NEXTVAL increments the sequence and returns the next value. To get the current or next value in a sequence, use dot notation:

sequence_name.CURRVAL
sequence_name.NEXTVAL

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 CURRVAL and NEXTVAL only in a SELECT list, the VALUES clause, and the SET clause.

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 NEXTVAL.

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 CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.

A sequence can be accessed by many users concurrently with no waiting or locking.

Conclusion:

From my research here is what I have determined:

1. 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.
2. 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&#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