CODEWITHSUNDEEP
oracle-databasesequence
Rafael Winterhalter
Rafael Winterhalter

Reputation: 44032

Are inserts with sequence numbers nextval atomic for this number?

If I am inserting rows into a table in auto-commit mode where one column is defined by a sequence's nextval value, are these values guaranteed to become visible in the order they are inserted? I am wondering if a scenario where from three concurrent connections:

  1. Inserts foo
  2. Inserts bar
  3. Select all, observes bar with sequence number 2 but not foo with sequence number 1.

is possible.

Upvotes: 0

Views: 1801

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17924

"Auto-commit" is not a concept of the Oracle database. That is, there is no "auto-commit" mode or feature in the database -- it is only implemented in tools (like SQL*Plus).

A tool can implement "auto-commit" in different ways, but in most cases, it's probably along the lines of this:

(user's command, e.g., INSERT INTO ...)
<success response from Oracle server>
COMMIT;

In this case, the COMMIT does not get issued by the tool until there is a positive response from the server that the user's command has been executed. In a networked environment with >10ms latency, plus the vagaries of multithreading on the Oracle server itself, I would say there could be situations where session #2's automatic COMMIT gets processed on the server before session #1's and that, therefore, it is possible for session #3 to observe "bar" but not "foo".

The COMMIT timing of each session relative to the time at which session #3 starts its query is the only thing that matters. Session #3 will see whatever work session #1 and/or session #2 have committed as of the time session #3's query starts.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112342

The Oralce sequences are thread safe and are always created in order. It is guaranteed that the numbers produced are unique.

But you might not see an insert of an other session immediatley, if this other session has still an open transaction. This might create a temporary gap in the sequence you are seeing from SELECTs.

Further more, if a transaction which has called NEXTVAL is rolled back, then this will cause permanent gaps in the sequence. Sequences are not affected by roll backs or commits. An increment is always immediate and definitive.

See: CREATE SEQUENCE (Oracle Help Center)

Upvotes: 2

Related Questions