0

Is there a way (using config + transaction isolation levels) to ensure that there are no interim holes in a SQL Server IDENTITY column? Persistent holes are OK. The situation I am trying to avoid is when one query returns a hole but a subsequent similar query returns a row that was not yet committed when the query had been run the first time.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Szymon Pobiega
  • 3,358
  • 17
  • 18
  • This sounds more like you need to get your isolation levels right more than anything. – Oded May 13 '13 at 08:58

1 Answers1

1

Your question is one of isolation levels and has nothing to do with IDENTITY. The same problem applies to any update/insert visibility. The first query can return results which had include an uncommited row in one and only one situation: if you use dirty reads (read uncommited). If you do, then you deserve all the inconsistent results you'll get and you deserve no help.

If you want to see stable results between two consecutive reads you must have a transaction that encompases both reads and use SERIALIZABLE isolation level or, better, use a row versioning based isolation level like SNAPSHOT. My recommendation would be to enable SNAPSHOT and use it. See Using Snapshot Isolation.

All I need is the promise that inserts to a table are committed in order of identity values they claim.

I hope you read this again and realize the impossibility of the request ('promise ... commit..'). You can't ask for something to guarantee success before it finished. What you're asking eventually boils down to asking not to allocate a new identity before the previous allocated one has committed successfully. In other words, full serialization of all insert transactions.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I don't need stable results. All I need is the promise that inserts to a table are committed in order of identity values they claim. – Szymon Pobiega May 13 '13 at 09:27
  • Yes, I am asking for full serialization of inserts. And I hoped it is possible – Szymon Pobiega May 13 '13 at 09:42
  • 1
    Oh, it is possible, but will be slow. One approach is to use an application lock, [`sp_getapplock`](http://msdn.microsoft.com/en-us/library/ms189823.aspx), and have INSERT transactions acquire it in X mode before the INSERT statement. Another approach is to use a TABLOCKX on insert, but that will also block reads. – Remus Rusanu May 13 '13 at 09:44
  • Thanks. Performance is not a problem for me if it is reasonable. I am basically looking for poor man's a equivalent of an event database (geteventstore.com) built on top of SQLServer – Szymon Pobiega May 13 '13 at 09:51
  • How about using rowVersion instead of identity? – Szymon Pobiega May 13 '13 at 10:25