记一次公司仓库数据库服务器死锁过程及解决办法

死锁的四个必要条件：

互斥条件(mutual exclusion)：资源不能被共享，只能由一个进程使用。

请求与保持条件(hold and wait)：已经得到资源的进程可以再次申请新的资源。

非剥夺条件(no pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。

循环等待条件(circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

仓库拣货卡死，排查了数据库的很多地方，都没有头绪，最后到sql server 错误日志里查看，终于发现了蛛丝马迹

exec xp_readerrorlog 0,1,null,null,'2015-09-21','2015-10-10','desc'
   waiter id=process5c30e08 mode=u requesttype=wait
  waiter-list
   owner id=process5c26988 mode=x
  owner-list
  keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.orderxxx indexname=ix_pricingexpressproductcode_state id=lock17fa96980 mode=x associatedobjectid=72057597785604096
   waiter id=process5c26988 mode=u requesttype=wait
  waiter-list
   owner id=process5c30e08 mode=x
  owner-list
  keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.orderxxx indexname=ix_pricingexpressproductcode_state id=lock87d69e780 mode=x associatedobjectid=72057597785604096
 resource-list
(@operatestate money,@handledbynewwms bit,@state int,@orderout int)
update [orderxx] set [operatestate] = @operatestate,[handledbynewwms] = @handledbynewwms where (([orderxxx].[state] = @state) and ([orderxxx].[orderout] = @orderout) and ([orderxxx].[pricingexpressproductcode] in ('uknir')))  
  inputbuf
unknown   
   frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
update [orderxxx] set [operatestate] = @operatestate,[handledbynewwms] = @handledbynewwms where (([orderxxx].[state] = @state) and ([orderxxx].[orderout] = @orderout) and ([orderxxx].[pricingexpressproductcode] in ('uknir')))   
   frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
  executionstack
  process id=process5c30e08 taskpriority=0 logused=10320 waitresource=key: 33:72057597785604096 (112399c2054a) waittime=4813 ownerid=31578743038 transactionname=user_transaction lasttranstarted=2015-09-24t10:22:58.410 xdes=0x372e95950 lockmode=u schedulerid=17 kpid=8496 status=suspended spid=153 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24t10:22:58.540 lastbatchcompleted=2015-09-24t10:22:58.540 clientapp=.net sqlclient data provider hostname=ck1-win-web02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31578743038 currentdb=33 locktimeout=4294967295 clientoption1=671088672 clientoption2=128056
(@operatestate money,@handledbynewwms bit,@state int,@orderout int)update [orderxxx] set [operatestate] = @operatestate,[handledbynewwms] = @handledbynewwms where (([orderxxx].[state] = @state) and ([orderxxx].[orderout] = @orderout) and ([orderxxx].[pricingexpressproductcode] in ('uknir')))  
  inputbuf
unknown   
   frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
update [orderxxx] set [operatestate] = @operatestate,[handledbynewwms] = @handledbynewwms where (([orderxxx].[state] = @state) and ([orderxxx].[orderout] = @orderout) and ([orderxxx].[pricingexpressproductcode] in ('uknir')))   
   frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
  executionstack
  process id=process5c26988 taskpriority=0 logused=9892 waitresource=key: 33:72057597785604096 (70f5b089bb2b) waittime=4813 ownerid=31579268946 transactionname=user_transaction lasttranstarted=2015-09-24t10:27:01.357 xdes=0x98312f950 lockmode=u schedulerid=16 kpid=9184 status=suspended spid=454 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24t10:27:01.490 lastbatchcompleted=2015-09-24t10:27:01.487 clientapp=.net sqlclient data provider hostname=ck1-win-web02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31579268946 currentdb=33 locktimeout=4294967295 clientoption1=671088672 clientoption2=128056
 process-list
 deadlock victim=process5c26988
deadlock-list

咋一看上面的错误信息，可以发现两条相同的语句造成的死锁，但是这么短的语句不可能持有排他锁太久

再仔细分析一下错误日志，发现都死锁在同一个非聚集索引上，再问了一下开发，开发那边说，这条语句是在一个大事务里面，这个事务会做7、8件事

索引属性

还有索引里面的数据，发现很多重复值

sql语句是这样的

(@operatestate money,@handledbynewwms bit,@state int,@orderout int)
@handledbynewwms=(1) @operatestate=($1.0000) @orderout=(4055484) @state=(3) 
update [orderxxx] set [operatestate] = $1.0000,[handledbynewwms] = 1
where (([orderxxx].[state] = 3) and ([orderxxx].[orderout] = 4055484) and ([orderxxx].[pricingexpressproductcode] in ('ukrrm','ukrle')))

下图为语句生成的执行计划

当时的情况是大量sql语句被阻塞，而阻塞的语句正是下面这条语句

update [orderxxx] set [operatestate] = $1.0000,[handledbynewwms] = 1
where (([orderxxx].[state] = 3) and ([orderxxx].[orderout] = 4055484) and ([orderxxx].[pricingexpressproductcode] in ('ukrrm','ukrle')))

解决方法

上面得出几个症状

1、update语句是在一个大事务里面，事务太大导致其他session等待排他锁的时间变长

2、大家都在使用同一个非聚集索引，并扫描pricingexpressproductcode字段

3、索引里的重复值很多

从上面的症状基本可以判断，这个非聚集索引无啥用，可以禁用之

alter index [ix_pricingexpressproductcode_state] on [dbo].[orderxxx] disable

禁用之后，死锁消失，问题解决，仓库的怨气也随之消失

这一次排查过程时间有点长，但是很好定位，sql server错误日志给出了足够的信息定位死锁问题，所以遇到问题的时候一定要分析清楚日志