死鎖問題，想不明白為什么會死鎖，求大佬分析詳細原因和加鎖、等待之類的詳細程序程序，以便理解 解決
資訊如下：
tOnlineUser 死鎖發生表的索引資訊：
名稱：IX_tOnlineUser
型別：nonclustered, ignore duplicate keys, unique located on PRIMARY
索引列：iUserID
死鎖圖片資訊：
--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)
死鎖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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/167232.html
標籤：疑難問題