Home SQL Locking Scope, race condition prevention
Reply: 1

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302564 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO