Code Sleuth’s Blog

I’ve been writing T-SQL stored procedures for many years now, and I never fully understood how the concurrency of multiple users truly affects the asynchronous operation of certain types of paradigms until now.
Take the following T-SQL:

SELECT @ID = ID FROM tblTable WHERE nvhSomeText = 'match'

IF @ID IS NULL
INSERT INTO tblTable (nvhSomeText, intSomeNumber) VALUES ('match', 10)
ELSE
UPDATE tblTable SET nvhSomeValue = 10 WHERE ID = @ID

Imagine this section of T-SQL being part of a stored procedure that is executed an insanely large amount of times per second by multiple different users. There is the possibility that two or more executing instances of the stored procedure both find @ID to be NULL at the same time (a race condition), and then both add the same record which should not happen due to business logic or whatever. Of course, if you have checks on your tblTable this may prevent a catastrophe, but you still should prevent that check being necessary in the first place (at least in this case).
The trick to solving the race condition here is to use table hints such as UPDCHECK or HOLDLOCK within a transaction as follows:

BEGIN TRAN

SELECT @ID = ID FROM tblTable WITH (UPDCHECK, HOLDLOCK) WHERE nvhSomeText = 'match'

IF @ID IS NULL
INSERT INTO tblTable (nvhSomeText, intSomeNumber) VALUES ('match', 10)
ELSE
UPDATE tblTable SET nvhSomeValue = 10 WHERE ID = @ID

COMMIT

Placing the UPDLOCK within the SELECT statement forces a hold lock on the table specified before the WITH keyword. The official text for the use of UPDLOCK in the SQL Server 2008 Documentation is:

Specifies that update locks are to be taken and held until the transaction completes.

Placing a HOLDLOCK on a table simply specifies that the lock is to be held for only the table it was specified for, and that it should only exist for the current transaction (BEGIN TRAN -> COMMIT block). Official text is:

HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

Please review the site here for some extra information on the topic.