sql server死锁_SQL Server如何选择死锁受害者

最新推荐文章于 2024-05-13 17:36:43 发布

culuo4781

最新推荐文章于 2024-05-13 17:36:43 发布

阅读量425

点赞数

文章标签： mysql 数据库 java python oracle

原文链接：https://www.sqlshack.com/sql-server-selects-deadlock-victim/

版权

本文详细介绍了SQL Server如何检测和选择死锁受害者。默认情况下，它会选择回滚成本最低的事务，但可以通过设置死锁优先级来影响这个选择。通过示例展示了当优先级设置为高时，如何影响死锁的解决过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql server死锁

In the article on Deadlock Analysis and Prevention, we saw how deadlock occurs. We saw that SQL Server selects one of the processes involved in the deadlock as deadlock victim. In this article, we will look at the criteria on which SQL server selects a deadlock victim. Why one process is selected as deadlock victim and not the other.

在有关死锁分析和预防的文章中，我们了解了死锁是如何发生的。我们看到SQL Server选择了死锁中涉及的进程之一作为死锁的受害者。在本文中，我们将研究SQL Server选择死锁受害者的标准。为什么选择一个进程作为死锁受害者而不是另一个进程。

死锁检测 (Deadlock Detection)

SQL Server runs a lock monitor thread every five seconds to check if any deadlocks have occurred. If a deadlock is found, a victim is selected and the interval for the lock monitor thread is reduced, this can be to as low as 100 milliseconds in some cases. If the lock monitor thread stops finding deadlocks the interval for deadlock detection is then periodically increased up to the default five seconds interval.

SQL Server每五秒钟运行一次锁监视器线程，以检查是否发生了死锁。如果发现死锁，则选择受害者，并缩短锁定监视器线程的间隔，在某些情况下，该间隔可以低至100毫秒。如果锁定监视器线程停止查找死锁，则将死锁检测的间隔定期增加到默认的五秒间隔。

When a deadlock is detected, SQL Server then needs to select a victim of the deadlock.

当检测到死锁时，SQL Server然后需要选择死锁的受害者。

Once this has been done, the victim transaction is rolled back and all of the resources held by the victim are released. A 1205 error is sent to the application that was running the victim transaction.

完成此操作后，将撤消受害人事务，并释放受害人拥有的所有资源。 1205错误被发送到正在运行受害者事务的应用程序。

死锁优先 (Deadlock Priority)

By default, SQL Server chooses as the deadlock victim the transaction that is least expensive to rollback. In simple terms, a transaction that makes the fewest changes to the database is considered the least expensive.

默认情况下，SQL Server选择回滚成本最低的事务作为死锁受害者。简单来说，对数据库进行最少更改的事务被认为是最便宜的。

However, users can set custom priorities for a particular transaction using the SET DEADLOCK_PRIORITY statement. The process with the lowest deadlock priority will then be the one chosen as the deadlock victim.

但是，用户可以使用SET DEADLOCK_PRIORITY语句为特定事务设置自定义优先级。死锁优先级最低的进程将被选为死锁受害者。

Example: SET DEADLOCK_PRIORITY NORMAL

示例：SET DEADLOCK_PRIORITY NORMAL

By default the priority for all the processes is normal. It can be set to LOW, NORMAL or HIGH. It can also be set to an integer value in the range of -10 to 10.

默认情况下，所有进程的优先级都是正常的。可以将其设置为LOW，NORMAL或HIGH。也可以将其设置为-10到10范围内的整数。

As noted earlier, the process or transaction with the lowest deadlock priority is chosen as the deadlock victim. If the priority of all the transactions involved in the deadlock is same, the transaction that is least expensive is chosen as deadlock victim. In the unlikely event that both the deadlock priority and the cost of the transactions involved in the deadlock are equal, then deadlock victim will be selected randomly.

如前所述，选择具有最低死锁优先级的进程或事务作为死锁受害者。如果死锁中涉及的所有事务的优先级相同，则将开销最小的事务选择为死锁受害者。万一死锁优先级和死锁中涉及的事务成本相等，则死锁受害者将被随机选择。

This is most easily explained by working through an example.

通过一个示例最容易解释这一点。

创建虚拟数据 (Creating Dummy Data)

Let’s create some dummy data. We will use this data in our deadlock example. Execute the following script on your SQL Server.

让我们创建一些虚拟数据。我们将在死锁示例中使用此数据。在您SQL Server上执行以下脚本。

 
CREATE DATABASE testdb;
 
GO
 
USE testdb;
 
CREATE TABLE table1
(
	id INT IDENTITY PRIMARY KEY,
	student_name NVARCHAR(50)
 
)
 
INSERT INTO table1 values ('James')
INSERT INTO table1 values ('Andy')
INSERT INTO table1 values ('Sal')
INSERT INTO table1 values ('Helen')
INSERT INTO table1 values ('Jo')
INSERT INTO table1 values ('Wik')
 
 
CREATE TABLE table2
(
	id INT IDENTITY PRIMARY KEY,
	student_name NVARCHAR(50)
 
)
 
INSERT INTO table2 values ('Alan')
INSERT INTO table2 values ('Rik')
INSERT INTO table2 values ('Jack')
INSERT INTO table2 values ('Mark')
INSERT INTO table2 values ('Josh')
INSERT INTO table2 values ('Fred')

死锁优先级示例 (Deadlock Priority Example)

First, let’s take a look at a practical example of how deadlock victims being selected based on their deadlock priority.

首先，让我们看一个实际示例，说明如何根据死锁优先级选择死锁受害者。

We will run two parallel instances of SSMS. Each instance will run one transaction that will be accessing a resource locked by the other transaction. A deadlock will occur and SQL server will then select as the victim the transaction that is the least expensive to roll back.

我们将运行两个并行的SSMS实例。每个实例将运行一个事务，该事务将访问另一事务锁定的资源。将发生死锁，然后SQL Server将选择回滚成本最低的事务作为受害者。

Script for the First Transaction:

第一次交易的脚本：

 
USE testdb;
 
-- Transaction1
BEGIN TRAN
 
UPDATE table1
SET student_name = student_name + 'Transaction1'
WHERE id IN (1,2,3,4,5)
 
UPDATE table2
SET student_name = student_name + 'Transaction1'
WHERE id = 1
 
COMMIT TRANSACTION

Script for the Second Transaction:

第二笔交易的脚本：

 
USE testdb;
 
-- Transaction2
BEGIN TRAN
 
UPDATE table2
SET student_name = student_name + 'Transaction2'
WHERE id = 1
 
UPDATE table1
SET student_name = student_name + 'Transaction2'
WHERE id IN (1,2,3,4,5)
 
COMMIT TRANSACTION

The process of creating a deadlock is simple. First execute the first update statement from the first transaction and then execute the first update statement from the second transaction.

创建死锁的过程很简单。首先从第一笔交易中执行第一条更新语句，然后从第二笔交易中执行第一条更新语句。

This will create locks on table1 and table2. Now execute the second update statement from transaction1. This statement tries to access table2 which is locked by transaction2.

这将在table1和table2上创建锁。现在，从transaction1执行第二个更新语句。该语句尝试访问由transaction2锁定的table2。

Finally, execute the second update statement in transaction2. This statement tries to access table1 which is locked and at this stage a deadlock will occur.

最后，在transaction2中执行第二条更新语句。该语句尝试访问已锁定的table1，在此阶段将发生死锁。

Now we know that by default, the deadlock priority for both of the transactions is NORMAL, therefore SQL server will select that transaction as deadlock victim which is least expensive to rollback.

现在我们知道默认情况下，两个事务的死锁优先级都为NORMAL，因此SQL Server将选择该事务作为死锁受害者，这回滚成本最低。

The first UPDATE statement of transaction1, it is updating five rows of table1 whereas the first UPDATE statement of transaction2 is updating only one row in table2. This means that transaction2 is the least expensive to rollback.

transaction1的第一个UPDATE语句正在更新table1的五行，而transaction2的第一个UPDATE语句仅更新table2的一行。这意味着transaction2的回滚成本最低。

After some time, you will see SQL Server select transaction2 as the deadlock victim and it will then roll it back as expected. The error message is shown in the following screenshot:

一段时间后，您将看到SQL Server选择transaction2作为死锁受害者，然后它将按预期回滚。错误消息显示在以下屏幕截图中：

Now if you select the data from table1 you should see updated data as shown below:

现在，如果您从表1中选择数据，则应该看到更新的数据，如下所示：

死锁优先级设置为高的死锁 (Deadlock with Deadlock Priority set to High)

In the next example, we will set the deadlock priority of transaction2 to HIGH and will again create a deadlock.

在下一个示例中，我们将Transaction2的死锁优先级设置为HIGH，然后再次创建死锁。

This time transaction1 will be selected as deadlock victim since it will have deadlock priority of NORMAL by default. Before we do that let’s clean our tables and re-insert the data into it.

由于默认情况下，此事务1的死锁优先级为NORMAL，因此它将被选择为死锁受害者。在此之前，我们先清理表并将数据重新插入其中。

Let’s truncate both the tables using following commands:

让我们使用以下命令截断两个表：

 
TRUNCATE TABLE table1;
TRUNCATE TABLE table2;

Now again add some data in table1 and table2 by executing the following script:

现在，通过执行以下脚本再次在表1和表2中添加一些数据：

 
USE testdb;
 
INSERT INTO table1 values ('Andy')
INSERT INTO table1 values ('Sal')
INSERT INTO table1 values ('Helen')
INSERT INTO table1 values ('Jo')
INSERT INTO table1 values ('Wik')
 
INSERT INTO table2 values ('Rik')
INSERT INTO table2 values ('Jack')
INSERT INTO table2 values ('Mark')
INSERT INTO table2 values ('Josh')
INSERT INTO table2 values ('Fred')

Again, lets run two instances of SSMS in parallel and execute two transactions in them.

同样，让我们并行运行两个SSMS实例并在其中执行两个事务。

The script for transaction1 is same as in the previous example. In transaction2, we need to set the deadlock priority to HIGH. The script for transaction2 is as follows:

transaction1的脚本与前面的示例相同。在transaction2中，我们需要将死锁优先级设置为HIGH。 transaction2的脚本如下：

 
USE testdb;
-- Transaction2
SET DEADLOCK_PRIORITY HIGH
BEGIN TRAN
 
UPDATE table2
SET student_name = student_name + 'Transaction2'
WHERE id = 1
 
UPDATE table1
SET student_name = student_name + 'Transaction'
WHERE id IN (1,2,3,4,5)
 
COMMIT TRANSACTION

Now, repeat the same scenario as we did in the previous example and create a deadlock. This time you will see that transaction1 running in the first instance will be chosen as deadlock victim as shown in the following screenshot.

现在，重复与上一个示例相同的方案，并创建一个死锁。这次，您将看到在第一个实例中运行的transaction1将被选作死锁受害者，如以下屏幕截图所示。

结论 (Conclusion)

SQL Server selects deadlock victim following these rules:

SQL Server遵循以下规则选择死锁受害者：

The process with the lowest deadlock priority is set as deadlock victim.
死锁优先级最低的进程被设置为死锁受害者。
If the deadlock priority of all the processes involved in deadlock is same, then the process that is least expensive to rollback is selected as deadlock victim.
如果涉及死锁的所有进程的死锁优先级都相同，则选择回滚成本最低的进程作为死锁受害者。
If both the deadlock priority and cost of processes involved in deadlock is same, then the process a process is selected randomly as deadlock victim.
如果死锁优先级和死锁中涉及的进程成本相同，则将进程中的一个进程随机选择为死锁受害者。