TL;DR: The question below boils down to: When inserting a row, is there a window of opportunity between the generation of a new Identity
value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity
value inserted by a concurrent transaction? (In SQL Server.)
Detailed version
I have a SQL Server table with an Identity
column called CheckpointSequence
, which is the key of the table's clustered index (which also has a number of additional nonclustered indexes). Rows are inserted into the table by several concurrent processes and threads (at isolation level READ COMMITTED
, and without IDENTITY_INSERT
). At the same time, there are processes periodically reading rows from the clustered index, ordered by that CheckpointSequence
column (also at isolation level READ COMMITTED
, with the READ COMMITTED SNAPSHOT
option being turned off).
I currently rely on the fact that the reading processes can never "skip" a checkpoint. My question is: Can I rely on this property? And if not, what could I do to make it true?
Example: When rows with identity values 1, 2, 3, 4, and 5 are inserted, the reader must not see the row with value 5 prior to seeing the one with value 4. Tests show that the query, which contains an ORDER BY CheckpointSequence
clause (and a WHERE CheckpointSequence > -1
clause), reliably blocks whenever row 4 is to be read, but not yet committed, even if row 5 has already been committed.
I believe that at least in theory, there may be a race condition here that might cause this assumption to break. Unfortunately, documentation on Identity
doesn't say a lot about how Identity
works in the context of multiple concurrent transactions, it only says "Each new value is generated based on the current seed & increment." and "Each new value for a particular transaction is different from other concurrent transactions on the table." (MSDN)
My reasoning is, it must work somehow like this:
- A transaction is started (either explicitly or implicitly).
- An identity value (X) is generated.
- The corresponding row lock is taken on the clustered index based on the identity value (unless lock escalation kicks in, in which case the whole table is locked).
- The row is inserted.
- The transaction is committed (possibly quite a lot of time later), so the lock is removed again.
I think that between step 2 and 3, there is a very tiny window where
- a concurrent session could generate the next identity value (X+1) and execute all the remaining steps,
- thus allowing a reader coming exactly at that point of time to read the value X+1, missing the value of X.
Of course, the probability of this seems extremely low; but still - it could happen. Or could it?
(If you're interested in the context: This is the implementation of NEventStore's SQL Persistence Engine. NEventStore implements an append-only event store where every event gets a new, ascending checkpoint sequence number. Clients read events from the event store ordered by checkpoint in order to perform computations of all sorts. Once an event with checkpoint X has been processed, clients only consider "newer" events, i.e., events with checkpoint X+1 and above. Therefore, it is vital that events can never be skipped, as they'd never be considered again. I'm currently trying to determine if the Identity
-based checkpoint implementation meets this requirement. These are the exact SQL statements used: Schema, Writer's query, Reader's Query.)
If I'm right and the situation described above could arise, I can see only two options of dealing with them, both of which are unsatisfactory:
- When seeing a checkpoint sequence value X+1 before having seen X, dismiss X+1 and try again later. However, because
Identity
can of course produce gaps (e.g., when the transaction is rolled back), X might never come. - So, same approach, but accept the gap after n milliseconds. However, what value of n should I assume?
Any better ideas?