sql server update与delete引发的死锁

【1】死锁发生及基本信息

死锁问题，想不明白为什么会死锁，求大佬分析详细原因和加锁、等待之类的详细过程过程，以便理解解决
信息如下：

【1.1】被死锁的基本信息

tOnlineUser 死锁发生表的索引信息：
名称：IX_tOnlineUser
类型：nonclustered, ignore duplicate keys, unique located on PRIMARY
索引列：iUserID

【1.2】死锁图片与死锁代码信息

--SP1  被牺牲（图中左边）
SELECT @iDbGsID=iGameServerID,@bDbState=bState FROM tOnlineUser WHERE iUserID = @iUserID
UPDATE tOnlineUser SET bState=3 WHERE iUserID = @iUserID


--SP2 （图中右边）
DELETE FROM tOnlineUser WHERE iUserID=@iUserID
INSERT tOnlineUser (iUserID,iGameServerID,bState) VALUES (@iUserID,@iGsID,@bOnlineState)

【1.3】死锁XML信息

Deadlock graph    <deadlock-list>
 <deadlock victim="process47704d8">
  <process-list>
   <process id="processff12e8" taskpriority="0" logused="84" waitresource="KEY: 14:72057594038976512 (9e00c0f50f63)" waittime="3187" ownerId="862602370" transactionname="DELETE" lasttranstarted="2020-09-24T20:46:44.200" XDES="0xffffffff80048380" lockMode="X" schedulerid="7" kpid="1236" status="suspended" spid="71" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2020-09-24T20:46:44.200" lastbatchcompleted="2020-09-24T20:46:44.200" hostname="BOX-5" hostpid="1932" loginname="BOX_User" isolationlevel="read committed (2)" xactid="862602370" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="BOX_RunCenter.dbo.me_UserOnlineState" line="35" stmtstart="890" stmtend="1014" sqlhandle="0x03000e005cbf2019d1d51601ada700000100000000000000">
DELETE FROM tOnlineUser WHERE iUserID=@iUserID

    --在线 或 掉线     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 14 Object Id = 421576540]    </inputbuf>
   </process>
   <process id="process47704d8" taskpriority="0" logused="0" waitresource="RID: 14:1:185:196" waittime="3187" ownerId="862602372" transactionname="SELECT" lasttranstarted="2020-09-24T20:46:44.200" XDES="0xffffffff9f80a7b0" lockMode="S" schedulerid="15" kpid="8704" status="suspended" spid="129" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2020-09-24T20:46:44.200" lastbatchcompleted="2020-09-24T20:46:44.200" hostname="iZjcdsetuetu8jZ" hostpid="692" loginname="BOX_User" isolationlevel="read committed (2)" xactid="862602372" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="BOX_RunCenter.dbo.me_GetUserOnlineServerID" line="39" stmtstart="1206" stmtend="1404" sqlhandle="0x03000e00b1524416c8d51601ada700000100000000000000">
SELECT @iGsID = iGameServerID,@bState=bState FROM tOnlineUser WHERE iUserID = @iUserID

    --没有记录     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 14 Object Id = 373576369]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594038976512" dbid="14" objectname="BOX_RunCenter.dbo.tOnlineUser" indexname="IX_tOnlineUser" id="lockffffffffd7158ac0" mode="U" associatedObjectId="72057594038976512">
    <owner-list>
     <owner id="process47704d8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processff12e8" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
   <ridlock fileid="1" pageid="185" dbid="14" objectname="BOX_RunCenter.dbo.tOnlineUser" id="lockfffffffffd721240" mode="X" associatedObjectId="72057594038910976">
    <owner-list>
     <owner id="processff12e8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process47704d8" mode="S" requestType="wait"/>
    </waiter-list>
   </ridlock>
  </resource-list>
 </deadlock>
</deadlock-list>

【2】分析

【2.1】加锁过程分析

（1）看你的死锁示意图，SP1应该是在执行update，update会在涉及到的键列上先放置U锁，然后通过where条件是定位行。

（2）但是这个时候SP2的delete已经在定位到的行上放置了X锁，然后就死锁了

（3）为什么SP2 还需要键锁的X呢？因为已经确定删除行了，那么对应的索引键值也要删除掉。

总结就是：

　　我们都知道update和delete 等操作，是需要先查询，然后再进行操作的，那么总结核心过程应该如下：

（1）SP1的 update和 sp2 delete同时运行针对同一个索引IUserID 值，先同时获取到索引的S锁用来查询

（2）然后SP2的 delete 先一步获取到了对应行的 RID锁（行锁），它操作删除行。而此时SP1在等待SP2释放该索引键值IUserID对应的 RID页锁。

（3）这个时候SP2 的 delete 删除完行数据后，想要获取 IUserID 的键锁，因为是要把对应的索引键值一起删掉。但这个时候，索引键值的 S锁还持有在 SP1的 update上。

（4）最终， SP1的 update 拥有IUserID 的索引键值锁（S），SP2的 delete 有用 IUserID 所对应行的 RID行锁（X），他们互相需要对方的资源锁，然后又互相等待，形成了死锁的循环等待。

【3】解决办法

（1）UPDLOCK

最终使用了这种办法解决

指定采用更新锁并保持到事务完成。 UPDLOCK 仅对行级别或页级别的读操作采用更新锁。

如果将 UPDLOCK 与 TABLOCK 组合使用或出于一些其他原因采用表级锁，将采用排他 (X) 锁。

SELECT @iDbGsID=iGameServerID,@bDbState=bState 
FROM tOnlineUser with(updlock) WHERE iUserID = @iUserID

（2）消除额外的键查找锁需的锁

直接在 iUserID 上加上聚集索引，不过要是大表代价太大了，很影响性能，不太可取

（3）读操作时取消获取锁

使用 with nolock 、或者切换快照、读已提交快照隔离级别解决。

【参考文档】

【0】必看参考：SQL SERVER - 谈死锁的监控分析解决思路：https://www.cnblogs.com/xinysu/p/6511360.html

【1】select 与 update 的死锁：https://blog.csdn.net/ajianchina/article/details/46807131

【2】高并发select 与 update引起的死锁：https://blog.csdn.net/weixin_44774463/article/details/108204456

posted @ 2020-10-10 16:02 郭大侠1 阅读(2681) 评论(0) 编辑收藏举报

刷新页面返回顶部