sql-server - UPDATE 锁如何防止常见形式的死锁？

Question

SQL Server books online假设 UPDATE 锁可以防止常见形式的死锁:

Update Locks

Update (U) locks prevent a common form of deadlock.

它们如何防止常见形式的死锁？

我这是什么意思？

死锁的常见形式是两个进程尝试从共享 (S) 锁(即读锁)升级为独占 (X) 锁:

Process A                       Process B
========================        ========================
Acquire Shared lock
                                Acquire Shared lock
Attempt to escalate to X
Escalation waits on B 
                                Attempt to escalate to X
                                Escalation waits on A

死锁。两个进程都在互相等待。

这一切都在 BOL 中进行了解释:

A typical update pattern consists of a transaction reading a record, acquiring a shared (S) lock on the resource (page or row), and then modifying the row, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

使用更新锁来防止死锁

在线图书没有解释更新 (U) 锁如何防止这种常见形式的死锁，他们只是说:

To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.

描述不完整。如果您查看短语“一次只有一个事务可以获取资源的更新 (U) 锁”。这与排它 (X) 锁没有什么不同 - 同一时间只有一个事务可以获得资源的排它 (X) 锁。但无论如何，让我们试着弄清楚:

首先我们假设是一个正常的更新过程:

• 获取共享(S)锁
• 尝试升级以更新 (U) 锁定
• 执行更新
• 需要更改值，升级为排它 (X) 锁
• 升级为独占 (X) 锁
• 执行更新
• 释放所有锁

现在添加第二个进程

Process A                       Process B
========================        ========================
Acquire Shared lock
                                Acquire Shared lock
Attempt to escalate to U
Escalation waits on B 
                                Attempt to escalate to U
                                Escalation waits on A

死锁。两个进程都在互相等待。

更新 (U) 锁如何防止常见形式的死锁？

Answer 1

The description is incomplete. If you look at the phrase "only one transaction can obtain an update (U) lock to a resource at a time". That is no different from an exclusive (X) lock - only one transaction can obtain an exclusive (X) lock to a resource at at time.

U 型锁与 S 型锁兼容，但 X 型锁则不然。这意味着虽然要写入的行已确定(使用 U 锁)，但仍然允许其他读取器。

So now add a second process...

这里的误解是，作家从S升级到U。事实并非如此。他们从一开始就使用U。他们稍后从 U 升级到 X，但这对于本例中的死锁没有任何意义。

为了更清楚地说明这一点:假设我们运行以下语句:

UPDATE T SET SomeCol = 1 WHERE (ID BETWEEN 1 AND 2) AND (SomeOtherCond = 1)

假设这是通过对 ID 上的聚集索引进行范围扫描来执行的，并且 SomeOtherCond = 1 仅对于行 ID = 2. .这将为您提供两行的 U 锁，并为 ID = 2 的行升级到 X。 ID = 2行的U锁将提前释放。