SQL Server 死锁案例分析-CSDN博客

概述

当两个或者多个进程相互阻塞时，形成一个复杂的阻塞链，参与的进程都在等待其他进程放弃其获取到的锁，没有系统的干预，无法解开这个阻塞链，这种情况就是死锁。

有些死锁是意料之内的，可以说是故意为之的。例如，为了一致性，使用死锁来防止丢失更新。有一些死锁是意料之外的，由于缺少索引或者事务运行时间较长导致，我们遇到的大部分都是意料之外的死锁。

SQL Server内部有个死锁的检测机制，当发生死锁时，SQL Server根据会话优先级以及工作量进行评估，选择终止其中一个事务，解开死锁，并且向发起该事务的客户端发送下面的错误信息。

Error Message：

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

死锁发生原理

死锁其实本质上是两个或者多个session互相阻塞。阻塞的发生，实际上是不同的连接申请的锁互相不兼容而产生的。例如，当一个连接持有X锁时，另外一个连接想要来申请同一资源上面的其他任意的锁，都会被阻塞。SQL Server中锁的兼容性表如下。

99b468bf1865287a1cfabbca569ed576f76970bd

案例分析

1. 收集死锁信息

如果是ECS上自建的SQL Server实例，那么很幸运，有种便捷的方法，可以开启trace flag 1222，将死锁实时记录在错误日志中。

DBCC TRACEON(1222,-1)

对于RDS SQL Server死锁信息的采集方法，之前在 RDS for SQL Server 死锁处理方法中有详细的描述，例如sys.sysprocesses和SQL Server Profiler，详细步骤在此就不赘述了。

2. 死锁日志分析

一般来说，SQL Server Profiler和1222记录下来的死锁日志是比较全面的。SQL Server Profiler收集死锁日志，消耗性能较高，但是自动显示出死锁图谱，便于分析。

对于1222在错误日志中收集死锁信息，可以一直开启，只会在发生死锁是记录在日志中，对性能影响较小，不过分析稍微复杂些。错误日志的分析过程如下：

死锁发生的主要时间段是6月3号10:00至11:38， 10:00:06.16发生的死锁的进程，锁和资源信息如下：

进程process89a9a6748 在资源pageid=227126上面持有IX锁，此时process43c824748想要申请该page上的U锁，被阻塞。同时process43c824748持有pageid=11768上持有U锁，此时process89a9a6748需要申请该页上的U锁，被阻塞。此时，两个进程互相阻塞，形成死锁。

死锁资源	资源1	资源2
资源类型	pagelock	pagelock
具体内容	pageid=11768 dbid=37	pageid=227126 dbid=37
持有资源进程	process43c824748	process89a9a6748
等待资源进程	process89a9a6748	process43c824748

进程process89a9a6748执行语句

UPDATE C SET TaskTitle=T.TaskTitle FROM WF.C***Task AS C
INNER JOIN @complateTask AS T
ON T.TaskID=C.TaskID
EXEC WF.PROC_UpdateTask_Opin  @INTRANSACTION,@processID,@isClearUnread,@task,@complateTask,@opin

进程process43c824748执行语句

UPDATE C SET TaskTitle=T.TaskTitle FROM WF.***Task AS C
INNER JOIN @complateTask AS T
ON T.TaskID=C.TaskID
EXEC WF.PROC_**Task_Opin  @INTRANSACTION,@processID,@isClearUnread,@task,@complateTask,@opin