SQL Locking Scope, race condition prevention

Question

I have a process that is inserting data into a database (SQL Server 2008) whose schema I cannot modify. The table has an int PK, but no auto-increment. So, I need to get the largest id, increment it and then insert (and return the new id.) This transaction also needs to update a number of other tables at the same time. I'm obviously trying 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 the transaction isolation level and the table locking hints enough to prevent the race condition or do I need UPDLOCK instead of ROWLOCK? (I have a separate 'retry' process if the insert fails.)

Are you allowed to add an additional table? If so it will be possible to come up with a solution with less blocking. — Martin Smith
@Martin Not currently allowed to add an additional table unfortunately, but might be able to make a case if there's no other acceptably performant approach. This was previously implemented with Serializable isolation level and full table lock on MyTable. I've been tasked with optimizing as that solution caused lots and lots of performance problems. — Jacob G
Is there any reason you can't change it to become an identity column? — Martin Smith
@Martin I can't change it to an Identity column because this is a database belonging to a different, off the shelf application. The code in this application does a great deal of processing on this particular column and uses it for many, many inappropriate things. I fear that if I don't insert the data in a manner similar to how the application inserts the data, that I will cause serious downstream problems. — Jacob G

Martin Smith Martin Smith · Accepted Answer · 2011-12-05T21:57:43

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

Ought to be sufficient. The HOLDLOCK gives serializable semantics which means a key range lock will be taken on the range at the end of the index backing up the primary key. The XLOCK means that 2 concurrent transactions can't both acquire this lock simultaneously.

This does mean that any concurrent callers to your insert procedure will end up being 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 that as 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

SQL Locking Scope, race condition prevention

1 Answers