SQL Server 死锁案例分析

清疚 2022-04-06 09:47 302阅读 0赞

摘要： 概述 当应用程序频繁读写某个表或者资源时，很容易出现死锁现象。出现死锁时，SQL Server会选择终止其中一个事务，并且向发起该事务的客户端发送下面的错误信息。Error Message：Msg 1205, Level 13, State 47, Line 1Transaction (Process ID 53) was deadlocked on lock resources with an  
概述  
当应用程序频繁读写某个表或者资源时，很容易出现死锁现象。出现死锁时，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.  
收集死锁信息  
如果是ECS上自建的SQL Server实例，那么很幸运，有种便捷的方法，可以开启trace flag 1222，将死锁实时记录在错误日志中。  
DBCC TRACEON(1222,-1)  
对于RDS SQL Server死锁信息的采集方法，之前在RDS for SQL Server 死锁处理方法中有详细的描述，例如sys.sysprocesses和SQL Server Profiler，详细步骤在此就不赘述了。  
死锁日志分析  
一般来说，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  
解决意见  
根据观察表结构和语句执行计划等信息，建议在表WF.CompletedTask的TaskID字段上面加一个非聚集索引，提升update执行速度，减少U锁的持有时间。