問題描述

我有一系列需要以原子方式運行的 T-SQL 查詢.(見下文)...目的是允許 1 個用戶一次檢索一個唯一的行，并防止其他用戶同時檢索同一行.

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.

到目前為止，我已經看到了兩種可能的解決方案.1) 表提示 (HOLDLOCK, TABLOCKX) 和 2) 事務隔離級別 (SERIALIZABLE)...

So far I have seen two possible solutions. 1) Table Hints (HOLDLOCK, TABLOCKX) and 2) Transaction Isolation Level (SERIALIZABLE)...

我的問題:

1. 哪個選項更好?

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

推薦答案

在這種情況下，

• HOLDLOCK = SERIALIZABLE = 持續時間，并發
• TABLOCKX = 獨占表鎖

這兩個概念是不同的，你想要的也不是.

The 2 concepts are different and neither does what you want.

做你想做的事，避免競爭條件，需要強制一個非阻塞(READPAST)排他(UPDLOCK)行級(ROWLOCK)鎖，.您還可以使用 OUTPUT 子句使其成為具有原子性的單個語句.這可以很好地擴展.

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

總的來說，鎖有3個方面

In general, locks have 3 aspects

• 粒度 = 鎖定的內容 = 行、頁、表(PAGLOCK、ROWLOCK、TABLOCK)
• 隔離級別 = 鎖定持續時間、并發性(HOLDLOCK、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE)
• 模式 = 共享/排他性 (UPDLOCK, XLOCK)

和

• 組合"例如 NOLOCK, TABLOCKX

這篇關于TABLOCKX 與可序列化的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！