TABLOCKX vs SERIALIZABLE

Question

I have a series of T-SQL queries that I need to run atomically. (See below) ... The goal is to allow one user to receive a single unique row at a time and not allow other users to simultaneously receive the same row.

So far I have seen two possible solutions. 1) Table prompts (HOLDLOCK, TABLOCKX) and 2) Transaction isolation level (SERIALIZABLE) ...

My questions:

• Which option is better?

• Is there any other / 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 = exclusive table lock

2 concepts are different and do not do what you want.

, , , (READPAST) (UPDLOCK) (ROWLOCK), OUTPUT, , . .

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

3

• = = , , (PAGLOCK, ROWLOCK, TABLOCK)
• = , concurrency (HOLDLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE)
• = / (UPDLOCK, XLOCK)

• "", NOLOCK, TABLOCKX

Answer 2

, tablockx, serializable, . , , . :

• (!)
• OUTPUT
• READPAST