TABLOCKX versus SERIALIZABLE
I have a series of T-SQL queries that I need to run atomically. (See Below)... The purpose is to allow 1 user to retrieve a single, unique row at a time and prevent other users from retrieving the same row simultaneously.
So far I have seen two possible solutions. 1) Table Hints (HOLDLOCK, TABLOCKX) and 2) Transaction Isolation Level (SERIALIZABLE)...
My Questions:
Which option is better?
Is there another/better solution?
DECLARE @recordId int;
SELECT @recordId = MIN([id])
FROM Exceptions
WHERE [status] = 'READY';
UPDATE Exceptions
SET [status] = 'PROCESSING',
[username] = @Username
WHERE [id] = @recordId;
SELECT *
FROM Exceptions
WHERE [id] = @recordId;
In this case,
The 2 concepts are different and neither does what you want.
To do what you want, to avoid race conditions, you need to force a non-blocking (READPAST) exclusive (UPDLOCK) row level (ROWLOCK) lock,. You can also use the OUTPUT clause to make it a single statement that will be atomic. This scales well.
UPDATE
E
SET
[status] = 'PROCESSING', [username] = @Username
OUTPUT
INSERTED.*
FROM
(
SELECT TOP 1 id, [status], [username]
FROM Exceptions (ROWLOCK, READPAST, UPDLOCK)
WHERE [status] = 'READY'
ORDER BY id
) E
In general, locks have 3 aspects
PAGLOCK, ROWLOCK, TABLOCK ) HOLDLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE ) UPDLOCK, XLOCK ) And
NOLOCK, TABLOCKX You are describing typical queue processing and neither tablockx, nor serializable are required, nor will they actually work. I recommend you go over Using tables as Queues for an in dept discussion at what is possible and what not. The gist of it is: