TABLOCKX 与可序列化
我有一系列需要以原子方式运行的 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)...
我的问题:
哪个选项更好?
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
相关文章