摘要： SQL Server死锁的捕获、分析和解决方法。
一、什么是死锁？
简单来说，我和你，金锁和银锁。
我拿着金锁，我需要再拿到银锁，才能完成任务，
你拿着银锁，你需要再拿到金锁，才能完成任务。
我拿不到银锁，你拿不到金锁，这就形成死锁了。
二、死锁发生后，SQL Server怎么处理？
SQL Server内置有死锁侦测和处理机制，每5S会检测一次，如果有死锁，就会评估下哪个事务回滚的开销比较低，将其kill掉，然后反馈1205错误。
实际上并没有这么简单，比如可以设置会话的优先级，优先级越低，被选为牺牲品的可能性就越大。
三、死锁发生后怎么处理？
捕获死锁>>分析死锁>>解决方案
先模拟获取死锁的demo
/*建表*/CREATETABLE[dbo].[deadlockTest]([id][int]IDENTITY(1,1)NOTNULL,[userid][varchar](10)NULL,[num][int]NULL,CONSTRAINT[PK_deadlockTest]PRIMARYKEYCLUSTERED([id]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]/*建索引*/createindexix_useridondeadlockTest(userid)/*生成测试数据*/insertintodeadlockTestselect1,1insertintodeadlockTestselect2,2/*事务1*/--我begintranupdatedeadlockTestsetnum=100whereid=1--金锁updatedeadlockTestsetnum=100whereid=2--银锁 ！！注意这一句执行事务2后，回来再执行/*事务2*/--你begintranupdatedeadlockTestsetnum=100whereid=2--银锁updatedeadlockTestsetnum=100whereid=1--金锁
结果：
1、捕获死锁，方法较多，常用的两种方法
>>打开跟踪标志 1222[RDS不支持]，可以从SQL SERVER日志中获取到死锁信息
DBCC TRACEON(1222,-1)
>>SQL Server Profiler抓取
2、分析死锁
完整的死锁信息【敏感信息XXX脱敏】绿底和红字是分析文字
第一部分：牺牲品
第二部分：进程信息
<process id="process40fb278"taskpriority="0" logused="144" waitresource="KEY: 5:72057594039238656 (61a06abd401c)" waittime="3421" ownerId="83492" transactionname="user_transaction" lasttranstarted="2018-01-31T00:19:12.110" XDES="0xa9ac290" lockMode="X" schedulerid="4" kpid="4312" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-31T00:19:17.500" lastbatchcompleted="2018-01-31T00:19:12.110" lastattention="1900-01-01T00:00:00.110" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="ALI-XXX" hostpid="13556" loginname="HZ\XXX" isolationlevel="read committed (2)" xactid="83492" currentdb="5" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
UPDATE [deadlockTest] set [num] = @1 WHERE [id]=@2
update deadlockTest set num=100 where id=2
update deadlockTest set num=100 where id=2
<process id="process40eb468" taskpriority="0" logused="144" waitresource="KEY: 5:72057594039238656 (8194443284a0)" waittime="6676" ownerId="83494" transactionname="user_transaction" lasttranstarted="2018-01-31T00:19:14.247" XDES="0x4126378" lockMode="X" schedulerid="2" kpid="4340" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-31T00:19:14.247" lastbatchcompleted="2018-01-31T00:19:08.843" lastattention="1900-01-01T00:00:00.843" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="ALI-XXX" hostpid="13556" loginname="HZ\XXX" isolationlevel="read committed (2)" xactid="83494" currentdb="5" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
UPDATE [deadlockTest] set [num] = @1 WHERE [id]=@2
update deadlockTest set num=100 where id=1
--commit
begin tran
update deadlockTest set num=100 where id=2
update deadlockTest set num=100 where id=1
--commit
第三部分：资源信息
hobtid="72057594039238656" dbid="5" objectname="blockTest.dbo.deadlockTest" indexname="PK_deadlockTest" id="lock2c69480" mode="X" associatedObjectId="72057594039238656">
<waiter id="process40fb278" mode="X" requestType="wait"/>
根据上面的死锁信息，可以得到：
可以看到死锁的原因，两个事务都想获取对方持有资源上的X锁进行update，互不相让，所以就形成了死锁。
KEY: 5:72057594039238656 这个说明，锁定的资源是KEY，数据库是5，资源是72057594039238656
这些可以通过脚本获取到具体的信息，但是没有必要，第三部分resource-list完全可以获取到，数据库是blockTest，表示deadlockTest，键是PK_deadlockTest
解决方案，
由于两个事务执行的SQL顺序相反，所以产生了这种情况，该case的解法就是将两个事务SQL执行顺序设为一致即可。
四、死锁总结
死锁不能完全避免，只能是尽量降低，一般常用的方法【降低互斥发生的风险、减少锁的申请数量、降低锁持有的时间】：
1、按相同顺序访问对象
这样就会降低互斥发生的风险，也就是DEMO的这个案例
2、事务尽量简短
因为事务commit后，才会释放该事务中持有的锁，所以事务越简短，持有锁的时间就会越短，从而降低死锁发生的概率。
3、优化SQL，尽量避免table scan，index scan这种方式
这样SQL执行扫描/查找的数量就会减少，从而达到减少申请锁的数量的目的。
4、事务中避免与用户的交互
这个会大大增长锁持有的时间，DEMO的这个案例，也可以理解为与用户交互了，因为事务1第二个update，测试时是等待第二个事务执行后，再去手动执行的。
还有一个方法，就是降低事务的隔离级别，低隔离级别S锁的持有时间会较短，但是这个方法大部分情况下是不可能采纳的。
版权声明：本文内容由互联网用户自发贡献，版权归作者所有，本社区不拥有所有权，也不承担相关法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@service.aliyun.com 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。
网友评论