UPDATE 锁如何防止常见形式的死锁? [英] How do UPDATE locks prevent a common form of deadlock?

问题描述

SQL Server 在线书籍 说 UPDATE 锁可以防止死锁的常见形式:

The SQL Server books online say that UPDATE locks prevent a common form of deadlock:

更新 (U) 锁可防止常见形式的死锁.

Update Locks

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

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

How do they prevent a common form of deadlock?

一种常见的死锁形式是两个进程试图从共享 (S) 锁(即读锁)升级到排他 (X) 锁:

A common form of deadlock is when two processes attempt to escalate from a Shared (S) lock (i.e. read lock) to a Exclusive (X) lock:

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

僵局.两个进程都在互相等待.

Deadlock. Both processes are waiting on each other.

这在 BOL 中都有说明:

This is all explained in the BOL:

典型的更新模式包括一个事务读取记录，获取资源(页或行)上的共享(S)锁，然后修改行，这需要将锁转换为排他(X)锁.如果两个事务在资源上获取共享模式锁，然后尝试并发更新数据，则一个事务会尝试将锁转换为排他 (X) 锁.共享模式到排他锁的转换必须等待，因为一个事务的排他锁与另一个事务的共享模式锁不兼容；发生锁定等待.第二个事务尝试为其更新获取排他 (X) 锁.由于两个事务都转换为排他 (X) 锁，并且每个事务都在等待另一个事务释放其共享模式锁，因此会发生死锁.

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) 锁如何防止这种常见形式的死锁，他们只说:

Use an update lock to prevent the deadlock

The Books Online doesn't explain how the update (U) lock prevents this common form of deadlock, all they do say is:

为了避免这种潜在的死锁问题，使用了更新 (U) 锁.一次只有一个事务可以获得对资源的更新 (U) 锁.如果事务修改了资源，则更新 (U) 锁将转换为排它 (X) 锁.否则，该锁将转换为共享模式锁.

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) 锁.但无论如何让我们试着弄清楚:

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. But lets try to figure it out anyway:

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

First what we assume is a normal update process:

• 获取共享 (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

僵局.两个进程都在互相等待.

Deadlock. Both processes are waiting on each other.

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

How does an update (U) lock prevent a common form of deadlock?

推荐答案

描述不完整.如果你看这句话只有一个事务可以一次获得一个资源的更新(U)锁".这与排他 (X) 锁没有什么不同——只有一个事务可以同时获得一个资源的排他(X)锁.

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 锁)，但仍允许其他读取器.

U-locks are compatible with S-locks which is not the case for X-locks. This means that while the rows to be written to are determined (using U-locks), other readers are still allowed.

现在添加第二个进程...

So now add a second process...

这里的误解是，作家从 S 升级到 U.事实并非如此.他们从一开始就使用 U.他们稍后从 U 升级到 X，但在这种情况下，这对死锁没有意义.

The misunderstanding here is, that writers upgrade from S to U. This is not the case. They use U from the start. They upgrade from U to X later, but that has no meaning regarding deadlocks in this case.

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

To make this more clear: Let's assume we run the following statement:

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 锁将提前释放.

Assume, that this is executed using a range scan on the clustered index on ID, and that SomeOtherCond = 1 is only true for the row ID = 2. This will get you U-locks for both rows, and an upgrade to X for the row with ID = 2. The U-lock for row ID = 2 will be released early.

这篇关于UPDATE 锁如何防止常见形式的死锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！