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

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语句为特定事务设置自定义优先级。 死锁优先级最低的进程将被选为死锁受害者。