Home SQL Locking Scope, race condition prevention

# SQL Locking Scope, race condition prevention

Jacob G
1#
Jacob G Published in 2011-12-05 21:44:40Z
 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.)
Martin Smith
2#
Martin Smith Reply to 2011-12-05 21:57:43Z
 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 
 You need to login account before you can post.
Processed in 0.302564 second(s) , Gzip On .