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 ...
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.)