TABLOCKX versus SERIALIZABLE

Question

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:

1. Which option is better?

2. 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;

Answer 1

In this case,

• HOLDLOCK = SERIALIZABLE = duration, concurrency
• TABLOCKX = an exclusive table lock

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

• Granularity = what is locked = row, page, table (PAGLOCK, ROWLOCK, TABLOCK)
• Isolation Level = lock duration, concurrency (HOLDLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE)
• Mode = sharing/exclusivity (UPDLOCK, XLOCK)

And

• "combined" eg NOLOCK, TABLOCKX