SQL Locking Scope, preventing race conditions

Question

I have a process that inserts data into a database (SQL Server 2008), whose schema I cannot change. There is int PK in the table, but no auto-increment. Therefore, I need to get the largest identifier, increase it, and then insert (and return a new identifier). This transaction should also update several other tables at the same time. I obviously try to avoid the race condition of simultaneous inserts.

Begin Transaction (Read Committed) DECLARE @MyVar int; --here be the race condition SET @MyVar = (( SELECT MAX(value) FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK)) + 1); INSERT INTO MyTable .... UPDATE MyOtherTable SET Val = @MyVar WHERE WhatEver SELECT MyRetValName = @MyVar INSERT INTO MyThirdTable ... Commit Transaction 

Are transaction isolation levels and table lock prompts sufficient to prevent race conditions, or do I need UPDLOCK instead of ROWLOCK? (I have a separate “repeat” process if the insertion does not work.)

Answer 1

 SELECT MAX(value) FROM MyTable WITH (XLOCK, HOLDLOCK) 

Should be enough. HOLDLOCK provides serializable semantics, which means that key range locks will be accepted in the range at the end of the index supporting the primary key. XLOCK means that two simultaneous transactions cannot acquire this lock at the same time.

This means that any simultaneous calls to your insert procedure will ultimately be blocked for the duration of the transaction.

A less blocking solution, if you can add a new table, would be to create another table with an identity column and insert into it, as shown below.

 CREATE TABLE dbo.Sequence( val int IDENTITY (10000, 1) /*Seed this at whatever your current max value is*/ ) GO CREATE PROC dbo.GetSequence @val AS int OUTPUT AS BEGIN TRAN SAVE TRAN S1 INSERT INTO dbo.Sequence DEFAULT VALUES SET @val=SCOPE_IDENTITY() ROLLBACK TRAN S1 /*Rolls back just as far as the save point to prevent the sequence table filling up. The id allocated won't be reused*/ COMMIT TRAN