SQL Server 死锁问题的分析

一、什么是死锁？

简单来说，我和你，金锁和银锁。

我拿着金锁，我需要再拿到银锁，才能完成任务，

你拿着银锁，你需要再拿到金锁，才能完成任务。

我拿不到银锁，你拿不到金锁，这就形成死锁了。

二、死锁发生后，SQL Server怎么处理？

SQL Server内置有死锁侦测和处理机制，每5S会检测一次，如果有死锁，就会评估下哪个事务回滚的开销比较低，将其kill掉，然后反馈1205错误。

实际上并没有这么简单，比如可以设置会话的优先级，优先级越低，被选为牺牲品的可能性就越大。

三、死锁发生后怎么处理？

捕获死锁>>分析死锁>>解决方案

先模拟获取死锁的demo

/*建表*/
CREATE TABLE [dbo].[deadlockTest](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[userid] [varchar](10) NULL,
	[num] [int] NULL,
 CONSTRAINT [PK_deadlockTest] PRIMARY KEY CLUSTERED 
(
	[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]

/*建索引*/
create index ix_userid on deadlockTest(userid)

/*生成测试数据*/
insert into deadlockTest select 1,1
insert into deadlockTest select 2,2

/*事务1*/		--我
begin tran 
update deadlockTest set num=100 where id=1	--金锁

update deadlockTest set num=100 where id=2	--银锁   ！！注意这一句执行事务2后，回来再执行

/*事务2*/		--你
begin tran 
update deadlockTest set num=100 where id=2 --银锁

update deadlockTest set num=100 where id=1	--金锁

结果：

1、捕获死锁，方法较多，常用的两种方法

>>打开跟踪标志 1222 [RDS不支持]，可以从SQL SERVER日志中获取到死锁信息

DBCC TRACEON(1222,-1)

>>SQL Server Profiler抓取

2、分析死锁

完整的死锁信息【敏感信息XXX脱敏】 绿底和红字是分析文字

第一部分：牺牲品

<deadlock-list>

 <deadlock victim="process40fb278">  

第二部分：进程信息

  <process-list>

   <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">

    <executionStack>

     <frame procname="adhoc" line="2" stmtstart="38" sqlhandle="0x0200000077ab791ef537624961d9a096ad0bd30107468a9100000000000000000000000000000000">

UPDATE [deadlockTest] set [num] = @1  WHERE [id]=@2     </frame>

     <frame procname="adhoc" line="2" stmtstart="4" sqlhandle="0x0200000008921014831d58ffb23c0b6e1bd701b2c9540a4a00000000000000000000000000000000">

update deadlockTest set num=100 where id=2     </frame>

    </executionStack>

    <inputbuf>

update deadlockTest set num=100 where id=2    </inputbuf>

   </process>

   <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">

    <executionStack>

     <frame procname="adhoc" line="4" stmtstart="38" sqlhandle="0x0200000077ab791ef537624961d9a096ad0bd30107468a9100000000000000000000000000000000">

UPDATE [deadlockTest] set [num] = @1  WHERE [id]=@2     </frame>

     <frame procname="adhoc" line="4" stmtstart="118" sqlhandle="0x02000000db1bf83585d4aab1d8142f5298a82fc3111dd0bb00000000000000000000000000000000">

update deadlockTest set num=100 where id=1

 --commit     </frame>

    </executionStack>

    <inputbuf>

begin tran 

update deadlockTest set num=100 where id=2

update deadlockTest set num=100 where id=1

 --commit    </inputbuf>

   </process>

  </process-list>

第三部分：资源信息

  <resource-list>

   <keylock hobtid="72057594039238656" dbid="5" objectname="blockTest.dbo.deadlockTest" indexname="PK_deadlockTest" id="lock2c69480" mode="X" associatedObjectId="72057594039238656">

    <owner-list>

     <owner id="process40eb468" mode="X"/>

    </owner-list>

    <waiter-list>

     <waiter id="process40fb278" mode="X" requestType="wait"/>

    </waiter-list>

   </keylock>

   <keylock hobtid="72057594039238656" dbid="5" objectname="blockTest.dbo.deadlockTest" indexname="PK_deadlockTest" id="lock2c68680" mode="X" associatedObjectId="72057594039238656">

    <owner-list>

     <owner id="process40fb278" mode="X"/>

    </owner-list>

    <waiter-list>

     <waiter id="process40eb468" mode="X" requestType="wait"/>

    </waiter-list>

   </keylock>

  </resource-list>

 </deadlock>

</deadlock-list>

根据上面的死锁信息，可以得到：

可以看到死锁的原因，两个事务都想获取对方持有资源上的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锁的持有时间会较短，但是这个方法大部分情况下是不可能采纳的。