sql server死锁_SQL Server死锁定义和概述

sql server死锁

介绍 (Introduction)

In this series, I will provide all of the information you need to understand in order to deal with deadlocks.

在本系列中，我将提供您需要了解的所有信息，以处理死锁。

In part 1 (this article) I will explain:

在第1部分（本文）中，我将解释：

• Deadlock definition
  死锁定义
• the different types of deadlocks
  不同类型的死锁
• how SQL Server handles deadlocks
  SQL Server如何处理死锁

死锁定义 (Deadlock Definition)

A deadlock occurs when 2 processes are competing for exclusive access to a resource but is unable to obtain exclusive access to it because the other process is preventing it. This results in a standoff where neither process can proceed. The only way out of a deadlock is for one of the processes to be terminated. SQL Server automatically detects when deadlocks have occurred and takes action by killing one of the processes known as the victim.

当两个进程正在争用对资源的独占访问但由于另一个进程阻止了它而无法获得对该资源的独占访问时，将发生死锁。 这导致僵局，两个过程都无法进行。 摆脱僵局的唯一方法是终止其中一个进程。 SQL Server自动检测何时发生死锁，并通过杀死称为受害者的进程之一来采取措施。

Deadlocks do not only occur on locks, from SQL Server 2012 onward, deadlocks can also happen with memory, MARS (Multiple Active Result Sets) resources, worker threads and resources related to parallel query execution.

从SQL Server 2012开始，死锁不仅发生在锁上，死锁还可能发生在内存，MARS（多个活动结果集）资源，工作线程以及与并行查询执行相关的资源上。

我怎么知道是否有僵局？ (How do I know if I have a deadlock?)

The first sign you will have of a deadlock is the following error message which will be displayed to the user who own the process that was selected as the deadlock victim.

出现死锁的第一个迹象是以下错误消息，该消息将显示给拥有被选为死锁受害者的进程的用户。

Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

讯息1205，第13级，州立51，第6行
事务（进程ID 62）已在另一个进程的锁定资源上死锁，并且已被选择为死锁受害者。 重新运行事务。

The other user whose process was not selected as the victim will be most likely be completely unaware that their process participated in a deadlock.

未选择进程作为受害者的其他用户很可能完全不知道他们的进程陷入了僵局。

死锁定义类型 (Deadlocks definitions types)

There are 2 different types of deadlocks.

有2种不同类型的死锁。

Cycle locks deadlock definition

循环锁死锁定义

A cycle deadlock is what happens when a process A which is holding a lock on resource X is waiting to obtain an exclusive lock on resource Y, while at the same time process B is holding a lock on resource Y and is waiting to obtain an exclusive lock on resource X.

当持有资源X的锁的进程A等待获得资源Y的排他锁，而同时进程B持有资源Y的锁并等待获得排他的锁时，会发生循环死锁。锁定资源X。

Conversion locks deadlock definition

转换锁死锁定义

A conversion deadlock occurs when a thread tries to convert a lock from one type to another exclusive type but is unable to do so because another thread is already also holding a shared lock on the same resource.

当线程试图将锁从一种类型转换为另一种互斥类型但由于另一线程已经在同一资源上也持有共享锁而无法这样做时，就会发生转换死锁。

There are 3 types of conversions locks in SQL Server.

SQL Server中有3种类型的转换锁。

Type	Name	Description
SIU	Share with Intent Update	The thread holds some shared locks but also has update locks on some components (page or row).
SIX	Share with Intent Exclusive	The thread has both a shared lock and an exclusive lock on some components (page or row).
UIX	Update with Intent Exclusive	Both a U lock and an IX lock are taken separately but held at the same time.

类型	名称	描述
SIU	分享意向更新	该线程拥有一些共享锁，但在某些组件（页面或行）上也具有更新锁。
六	与Intent Exclusive分享	该线程在某些组件（页面或行）上具有共享锁和排他锁。
UIX	使用Intent Exclusive更新	U锁和IX锁是分开使用的，但同时持有。

SQL Server如何处理死锁 (How SQL Server handles deadlocks)

The lock manager in SQL Server automatically searches for deadlocks, this thread which is called the LOCK_MONITOR looks for deadlocks every 5 seconds. It looks at all waiting locks to determine if there are any cycles. When it detects a deadlock it chooses one of the transactions to be the victim and sends a 1205 error to the client which owns the connection. This transaction is then terminated and rolled back which releases all the resources on which it held a lock, allowing the other transaction involved in the deadlock to continue.

SQL Server中的锁管理器会自动搜索死锁，这个称为LOCK_MONITOR的线程每5秒就会查找一次死锁。 它查看所有等待的锁，以确定是否存在任何周期。 当检测到死锁时，它将选择其中一个事务作为受害者，并将1205错误发送给拥有连接的客户端。 然后终止此事务并回滚，释放释放该事务的所有资源，该资源保留了该锁定，从而使死锁中涉及的其他事务得以继续。

If there are a lot of deadlocks SQL Server automatically adjusts the frequency of the deadlock search, and back up to 5 seconds if deadlocks are no longer as frequent.

如果存在大量死锁，SQL Server会自动调整死锁搜索的频率，如果死锁不再那么频繁，则备份时间将最多恢复5秒。

SQL Server如何选择受害者？ (How does SQL Server choose the victim?)

There are a couple of factors that come into play here. The first is the deadlock priority. The deadlock priority of a transaction can be set using the following command:

这里有几个因素起作用。 首先是死锁优先级。 可以使用以下命令设置事务的死锁优先级：

 
  SET DEADLOCK_PRIORITY LOW; 
 

The typical values for the deadlock priority are:

死锁优先级的典型值为：

Priority	Value	Result
LOW	-5	If other transactions have a priority of NORMAL or HIGH or numerically higher than -5 , this transaction will be chosen as the deadlock victim
NORMAL	0	This is the default priority. The transaction could be chosen as the victim if other transactions have a priority higher than 0.
HIGH	5	This process will not be selected as the victim unless there is a process with a numeric priority higher than 5.
<numeric>	-10 to 10	This can be used to manage deadlock priority at a more granular level.

优先	值	结果
低	-5	如果其他事务的优先级为NORMAL或HIGH或在数值上高于-5，则该事务将被选为死锁受害者
正常	0	这是默认优先级。 如果其他事务的优先级高于0，则可以选择该事务作为受害者。
高	5	除非存在数字优先级高于5的进程，否则不会选择该进程作为受害者。
<数字>	-10至10	这可以用于更精细地管理死锁优先级。

If the transactions involved in a deadlock have the same deadlock priority, the one with the lowest cost is rolled back. In an example the one where the least amount of transaction log has been used, indicating that there is less data to roll back.

如果涉及死锁的事务具有相同的死锁优先级，则回退成本最低的事务。 在一个示例中，已使用最少数量的事务日志，表明有较少的数据要回滚。

跟踪死锁 (Keeping track of deadlocks)

There are various tools that can be used to obtain the details of deadlocks. These include trace flags 1204 and 1222. You can also capture the deadlock graph event using SQL Profiler.

有多种工具可用于获取死锁的详细信息。 其中包括跟踪标志1204和1222。您还可以使用SQL事件探查器捕获死锁图事件。

Personally I find that when I suspect that deadlocking is occurring in my server, that setting up and extended event session to log the deadlock graph each time it happens is the easiest.

我个人发现，当我怀疑服务器中正在发生死锁时，建立和扩展事件会话以在每次死锁图发生时记录日志是最简单的。

From SQL Server 2012 onwards this can be done in SQL Server Management Studio under Management \ Extended Events:

从SQL Server 2012起，可以在SQL Server Management Studio中的“管理\扩展事件”下完成此操作：

Using extended events you will be able to see quite easily how frequently deadlocks occur in your database, and immediately have the deadlock graph available for each deadlock which occurred in order to help you resolve it.

使用扩展事件，您将可以很容易地查看死锁在数据库中发生的频率，并立即为发生的每个死锁提供可用的死锁图，以帮助您解决它。

如何最小化死锁 (How to minimize deadlocks)

Here are a couple of tips to minimize deadlocks

这里有一些技巧可以最大程度地减少死锁

1. Always try to hold locks for as short a period as possible.

   始终尝试将锁保持尽可能短的时间。

2. Always access resources in the same order

   始终以相同的顺序访问资源

3. Ensure that you don’t have to wait on user input in the middle of a transaction. First, get all the information you need and then submit the transaction

   确保您不必在交易中间等待用户输入。 首先，获取您需要的所有信息，然后提交交易

4. Try to limit lock escalation, by using hints such as ROWLOCK etc

   尝试通过使用诸如ROWLOCK等提示来限制锁升级

5. Use READ COMMITTED SNAPSHOT ISOLATION or SNAPSHOT ISOLATION

   使用READ COMMITTED SNAPSHOT ISOLATION或SNAPSHOT ISOLATION

解决死锁 (Resolving deadlocks)

Resolving deadlocks can be a tricky business, and is beyond the scope of this article. Look out for my next articles which explain how to read the deadlock graph which is the most useful in understanding the cause of your deadlock and will give you the insight on how to tackle a deadlock.

解决死锁可能是一件棘手的事情，这不在本文的讨论范围之内。 留意我的下一篇文章，这些文章解释如何读取死锁图，这对于理解死锁的原因最有用，并且可以使您深入了解如何解决死锁。

本系列的其他文章： (Other articles in this series:)

• Understanding the graphical representation of the SQL Server Deadlock Graph了解SQL Server死锁图的图形表示
• Understanding the XML description of the Deadlock Graph in SQL Server了解SQL Server中的死锁图的XML描述

翻译自: https://www.sqlshack.com/what-is-a-sql-server-deadlock/

sql server死锁