Does tsql have an Insert with Select statement that is safe from a concurrency point of view?

Question

In my answer to this SO question, I suggest using a single insert statement with a selection that increments the value as shown below.

Insert Into VersionTable (Id, VersionNumber, Title, Description, ...) Select @ObjectId, max(VersionNumber) + 1, @Title, @Description From VersionTable Where Id = @ObjectId 

I suggested this because I believe that this statement is safe from a concurrency point of view, because if another insert for the same object identifier is started at the same time, there is no chance of duplicate version numbers.

Am I right?

Answer 1

As Paul writes: No, this is unsafe for which I would like to add empirical evidence. Create a table Table_1 with one ID field and one record with a value of 0 Then execute the following code simultaneously in two Management Studio query windows :

 declare @counter int set @counter = 0 while @counter < 1000 begin set @counter = @counter + 1 INSERT INTO Table_1 SELECT MAX(ID) + 1 FROM Table_1 end 

Then do

 SELECT ID, COUNT(*) FROM Table_1 GROUP BY ID HAVING COUNT(*) > 1 

On my SQL Server 2008, one identifier ( 662 ) was created twice. Thus, the default isolation level applied to single statements is insufficient.

---

EDIT: Obviously, the INSERT wrapper with BEGIN TRANSACTION and COMMIT will not be fixed because the default isolation level for transactions is still READ COMMITTED , which is not enough. Note that setting the transaction isolation level to REPEATABLE READ also insufficient. The only way to make this code safe is to add

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

up. This, however, caused deadlocks from time to time in my tests.

EDIT: The only solution I found that is safe and does not create deadlocks (at least in my tests) is to explicitly lock the table exclusively (the default transaction isolation level is sufficient here). Beware; this solution may result in reduced performance:

 ...loop stuff... BEGIN TRANSACTION SELECT * FROM Table_1 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0 INSERT INTO Table_1 SELECT MAX(ID) + 1 FROM Table_1 COMMIT ...loop end... 

Answer 2

The default read isolation makes this unsafe, if two of them run in perfect parall, you will get a duplicate because reading lock is not applied.

For safety, you need REPEAT READ or SERIALIZABLE isolation levels.

Answer 3

I think you are wrong. When you query the VersionNumber table, you put a read lock on the row. This does not prevent other users from reading the same row from the same table. Therefore, two processes can simultaneously read the same row in the VersionNumber table and generate the same VersionNumber value.

Answer 4

• You need a unique constraint on (Id, VersionNumber) to enforce it.

• I would use the ROWLOCK, XLOCK hints to block other people looking at the locked row where you calculate

• or wrap INSERT in TRY / CATCH. If I get a duplicate, try again ...