sql server死锁深入介绍

【1】死锁介绍

【1.1】死锁定义

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

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

【1.2】如何知道是否产生了死锁

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

消息1205，级别13，状态51，第6行
事务（进程ID 62）在锁定资源上与另一个进程死锁，并被选择为死锁受害者。重新运行事务。

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

【1.3】循环锁死锁 与  转换锁死锁

有两种不同类型的死锁。

（1）循环锁死锁

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

　　

图1：循环锁的图像

 

（2）转换锁死锁定义

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

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

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

【1.4】SQL Server如何处理死锁

SQL Server中的锁管理器会自动搜索死锁，这个称为 LOCK_MONITOR 的线程每5秒就会查找一次死锁。

　　它查看所有等待的锁，以确定是否存在任何周期。当检测到死锁时，它将选择其中一个事务作为受害者，并将1205错误发送给拥有连接的客户端。

　　然后，该事务终止并回滚，从而释放其持有锁定的所有资源，从而使死锁中涉及的其他事务继续进行。

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

【1.5】SQL Server如何选择死锁牺牲哪个事务？

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

SET DEADLOCK_PRIORITY LOW;

死锁优先级的典型值为：

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

 

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

【2】跟踪死锁

【2.1】死锁跟踪的几种办法

下面写的比较笼统，想要实操参考：https://www.cnblogs.com/gered/p/10811758.html

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

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

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

　　

图2：设置扩展事件会话以捕获死锁

 

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

【2.2】如何最小化死锁

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

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

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

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

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

5. 使用READ COMMITTED SNAPSHOT ISOLATION或SNAPSHOT ISOLATION

    

【3】使用图形界面发现查看死锁

【3.1】图形界面死锁查看的几种方式

我确定您已经知道什么是死锁，但是以防万一，您不熟悉SQL时，死锁就是当两个查询相互阻塞时，两个查询都无法完成。

例如，两个查询在一个表上持有共享锁，但是两个查询都需要将其锁升级为互斥锁才能执行更新。由于无法进行任何查询，因此需要干预。SQL Server常规执行死锁检查，并将选择终止其中一个进程以允许其他进程继续进行。这个被杀死的过程称为死锁受害者。

解决死锁的方法有多种，包括：

• 跟踪标志1222、1204

• 探查器（跟踪事件）

• 扩展事件

在这篇特别的文章中，我将只关注死锁图或.rdl格式的文件（图形表示），通过查看图图像可以知道什么，如果您只是想快速获得基本信息，这将非常方便。通常，但是查看文件的XML版本会显示更多信息，这些信息可以使死锁的疑难解答变得更加容易。

我将在下一步骤中介绍：理解死锁图第2部分：XML描述。

【3.2】死锁构造演示

出于此演示的目的，我将在AdvetureWorks2012数据库中创建一个非常简单的死锁，以使我们看到从死锁图中学到的知识。

我将同时执行2个查询：

查询1

BEGIN TRAN
 
UPDATE Person.Address set StateProvinceID = 78 where AddressID = 1
WAITFOR DELAY '00:00:05'
UPDATE Person.Person  set FirstName = 'John' where BusinessEntityID = 1
 
COMMIT TRAN

查询2

BEGIN TRAN
 
UPDATE Person.Person  SET FirstName = 'John' WHERE BusinessEntityID = 1
WAITFOR DELAY '00:00:05'
UPDATE Person.Address SET StateProvinceID = 78 WHERE AddressID = 1
 
COMMIT TRAN

这些查询非常简单，如您所见，查询1尝试更新Person.Address，然后等待5秒钟，然后尝试更新Person.Person。查询2尝试执行相同操作，但顺序相反。

由于无法进行任何查询，因此将导致死锁，并且其中一个将显示死锁消息，这意味着该查询已终止并回滚（死锁受害者），而另一个查询将成功完成。

查询1个结果

（影响1行）

消息1205，级别13，状态51，第5行
事务（进程ID 61）在锁定资源上与另一个进程死锁，并被选择为死锁受害者。重新运行事务。

查询2个结果

（影响1行）

（影响1行）

在这种情况下，查询1被选择为死锁受害者。正如我在上一篇有关“死锁”的文章中提到的那样，选择受害者时会考虑各种因素。

【3.3】如何获得死锁图（profiler）

获取死锁图的最简单方法是使用Profiler。您可以使用标准的TSQL_Locks模板。我们感兴趣的事件是“死锁图”。

　　　　

图1：死锁图事件

在“事件提取设置”选项卡上，您可以选择将死锁事件保存到XML文件。

　　　　

图2：将事件保存到XML

这会将事件数据保存到.xdl文件，然后可以在SSMS中打开该文件。有时您可能会遇到以下错误：

初始化死锁控制失败

发生这种情况时，最简单的方法是转到探查器中的死锁图线，右键单击并选择“提取事件数据”。

图3：提取事件数据

 

保存文件，然后在SSMS中打开它。

【4】如何看死锁图

【4.1】死锁图的3个概念

死锁图由3个概念组成，如下所示

图4：死锁图

           

  流程节点   资源节点     边缘

（1）资源节点：显示死锁中涉及的对象。如表，索引或行。

（2）流程节点：显示有关流程本身的信息。这是执行数据修改的过程。

（3）边缘指示资源和流程节点之间的关系。边缘有2种类型：

• 请求移动

  当资源正在等待进程时发生。在这种情况下，SPID 61（查询1）正在等待访问Person.Person表。Spid 62正在等待访问Person.Address表。

• 拥有者移动

  在资源等待进程时发生。在这种情况下，Person.person表正在等待过程62释放它。

【4.2】那么，我们能立马查看出什么呢？

1. 涉及的过程

   从该图中我们可以看到，此死锁涉及两个过程。处理61和62。

   每个过程节点（上面的浅橙色表示）显示以下数据：

   进程ID（The process id）	这是流程的SPID（会话流程ID）
   服务器批次ID（The server batch id）	服务器批次的标识符。如果没有批次，则为0。
   执行上下文ID（The execution context id）	如果该线程与另一个SPID关联，并且不是主线程，则它将具有一个标识子线程的数字。如上例所示，如果它是父线程，它将为0。
   死锁优先级（The deadlock priority）	如果为特定进程设置了死锁优先级，将在此处显示。典型值为 低：-5 正常：0和， 高：5
   日志使用（Log use）	进程使用的事务日志中的空间量
   所有者ID（Owner id）	这是拥有请求的交易的交易ID。此值对应于sys.dm_tran_locks DMV中的request_owner_id字段。
   交易描述符（Transaction descriptor）	事务获取状态的指针
   输入缓冲区（Input buffer）	当您将鼠标悬停在图形中的流程节点上时，这是可见的，在这种情况下，它将显示该流程执行的Transact-SQL命令。
   说明（Statement）	根据msdn，这是语句的类型，可以是：插入，更新，删除，未知或NOP（无操作）

2. 死锁受害者

   通过查看该图，我们可以看到的最明显的信息是，进程61被选择为死锁受害者。我们知道这是因为它被删除了。通常也显示在左侧。SQL Server将选择成本最低的进程作为受害者。标准之一是使用的日志量。由于两个进程的死锁优先级均为0，并且进程61使用的日志少于进程62，因此它可以更快地回滚，因此在这种情况下是受害者。

   

   图5：受害者过程

    

3. 涉及的资源

   接下来，我们可以了解死锁中涉及哪些对象。在这种情况下，所涉及的对象是AdventureWorks2012.Person.Person和AdventureWorks2012.Person.Address。

   

   图6：涉及的资源

    

   资源节点中显示的其他信息是：

   锁类型	这可能是键锁（在这种情况下），页面锁等。
   HoBT ID	这标识了死锁中涉及的堆或b树。
   关联的objid（associated objid）	根据sys.partitions相关分区的ID
   对象名称(Object name)	发生锁定的对象的名称
   索引名称(Index name)	涉及的索引

4. 持有和要求锁定的锁定方式

   通过查看边缘的描述，您可以了解正在持有或请求哪些锁定模式。

   在这种特殊情况下，每个进程都在请求更新锁（请求模式：U），每个资源都具有排他锁（所有者模式：X）。要获取有关锁定模式和锁定兼容性的更多信息，请查看Technet上有关锁定模式的文章。

5. 涉及的查询

   要查看死锁所涉及的SQL，可以将鼠标悬停在流程节点上以查看工具提示中显示的查询。

   

   图7：查看查询

    

【5】如何获取xml 死锁信息？

XML包含在“死锁图”的图形表示中不可见的信息，这使我们有必要查看XML描述。

【5.1】从死锁图中提取XML描述

根据捕获死锁图的方式，您可以通过多种方式查看XML文本。

在文本查看器（如记事本）中打开相同的.xdl文件

 

　　

　　　　　　　　　　图1：记事本中的死锁XML

（1）将文件扩展名从.xdl更改为.xml并在SSMS中打开 　　　　

　　　　

　　　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　图2：SSMS中的XML死锁图

如果使用扩展事件提取了死锁，则可以双击事件详细信息中的xml_report行，这将在SQL Server Management Studio中将xml描述作为.xml文件打开。

　　　　

　　　　　　　　　　　　图3：双击以获取XML报告

【6】分解死锁XML信息

死锁XML由3个部分组成：

• 受害者名单（victim-list）
• 流程清单（process-list）
• 资源列表（resource-list）

 

　　图4：死锁XML部分

现在，我们将详细研究每个。

【6.1】受害者名单（victim-list）

受害者列表正是它所说的，它是已选择作为死锁受害者的进程ID的列表。在此特定情况下，只有一个受害者，但有可能使多个受害者陷入僵局。

　　　　图5：受害者列表

【6.2】流程清单（process-list）

进程列表为死锁中涉及的每个进程包含一个节点。每个MSDN的每个进程显示以下详细信息：

Id	The process id of the specific process
Task priority	This is the deadlock priority. Typical values are LOW : -5, NORMAL : 0 and , HIGH: 5
Log used	The amount of transaction log used by the process
Waitresource	The waitresource has a format like this: KEY: 20:72057594045595648 (8194443284 a0) The first part: KEY indicates the resource type. In this case a key. The second part: 20 is the database id The third part: 72057594045595648 is the Heap or B tree ID The part in brackets is the hash value for the index key
Waittime	The amount of time the process waited for the resource to become available
Ownerid	This displays the transaction id of the transaction which owns the process. This value corresponds to the request_owner_id field in the sys.dm_tran_locks DMV.
Input buffer	This describes the event type and the code that is executed. In other words the SQL script that is being executed will be shown here.
Statement	The statement type such as: SELECT UPDATE INSERT Delete NOP (No Operation)

如果查看XML本身，您会注意到MSDN中未提及的其他一些细节。似乎MSDN指的是仅在死锁图的图形表示形式中可用的细节。这就是为什么我们需要查看XML的原因。其他字段包括：

 

transactionname	The name of the transaction or the transaction type: I.e. user_transaction
Lasttranstarted	The date time the last transaction started
XDES	Transaction description structures
LockMode	The type of lock the process wants to take
Schedulerid	The ID of the processor on which the process is running
Kpid	The Windows thread ID
Status	The status of the process. I.e.: Suspended Dormant Running Background Rollback Pending Runnable Spinloop
Spid	The SQL Process ID
Sbid	The batch ID
Ecid	This is the execution context when the process is executed in parallel. If not executed in parallel this value will be 0.
Priority	The same as the deadlock task priority
Trancount	The number of transactions involved in the deadlock
lastbatchstarted	Date time the last batch was started
lastbatchcompleted	Date time the last batch completed
lastattention	The timestamp of the processing of the last attention event such as a timeout or a cancellation on the thread involved in the deadlock.
Clientapp	The client application that created the process
Hostname	The host name on which the client is running
Hostpid	The process ID of the client application
Loginname	Name of the logged in user
Isolationlevel	The isolation level of the process
Xactid	The transaction ID of the process
Currentdb	Database ID of the database on which the process was run
lockTimeout	The maximum time a process can wait for a lock to be released
Clientoption1	These are SET options such as ANSI_NULLS or QUOTED_IDENTIFIER
Clientoption2	More SET options

我在流程列表中发现的最感兴趣的东西（在图形的图形视图上看不到）是流程的隔离级别。例如：如果隔离级别可序列化，则很可能是造成死锁的原因。

然后，您必须返回并确定是否需要此隔离级别，如果需要，请复查所涉及的所有查询，以确保它们始终以相同的顺序访问表。 　　

　　

　　　　　　　　　　　　　　　　　　　　图6：流程列表

【6.3】资源列表（resource-list）

资源列表提供有关死锁中涉及的每个资源的详细信息。资源可以是行，键，页面，扩展区，堆或B树（HoBT），表，文件，分配单元或元数据。在此示例中，死锁涉及键或索引。

 

<keylock hobtid="72057594045595648" dbid="20" objectname="AdventureWorks.Person.Person" indexname="1" id="lock46d8c8b80" mode="X" associatedobjectid="72057594045595648">
<owner-list>  
<owner id="process46e0a4928" mode="X">
   </owner></owner-list>
   <waiter-list>
    <waiter id="process46e0a4188" mode="U" requesttype="wait">
   </waiter></waiter-list>
  </keylock>

 

资源列表中提供了以下信息：

HoBTId	The heap or B-tree ID
dbID	The database id on which the resource exists
objectName	The name of the resource
indexname	This is the ID or name of the index involved in the deadlock
Id	The id of the key lock
Mode	The lock mode
associatedObjectId	The id of the object on which deadlock occurred, mostly the same as the HoBTID.
Owner-list	Information about the owner of the process and corresponding lock mode
Waiter-list	Information on the process waiting on the resource, lock type requested and the request type.

 

死锁中涉及的每个资源都将包含一个资源节点。

　　　　 

 

【参考文档】

 参考翻译自：https://www.sqlshack.com/understanding-the-xml-description-of-the-deadlock-graph-in-sql-server/

官网：https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15