Home SQL Locking Scope, race condition prevention

SQL Locking Scope, race condition prevention

user8345
1#
user8345 Published in September 21, 2018, 8:07 am

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 Dec 5 '11 at 21:47
• @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 Dec 5 '11 at 21:52
• Is there any reason you can't change it to become an identity column? – Martin Smith Dec 5 '11 at 22:02
• @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 Dec 5 '11 at 22:12