Forum Discussion
sql server key lock updating different rows
Hi All
I have a program to do batch updates, but there is always a key lock
I need help. I have a problem that I can't understand. This is deadlock graph：
<deadlock-list>
<deadlock victim="process1831bd11c28">
<process-list>
<process id="process1831bd11c28" taskpriority="0" logused="0" waitresource="KEY: 11:72057594045595648 (50a083c8507e)" waittime="455" ownerId="6681936" transactionname="UPDATE" lasttranstarted="2021-03-25T10:42:50.833" XDES="0x182c0e54490" lockMode="U" schedulerid="9" kpid="33172" status="suspended" spid="145" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-03-25T10:42:50.800" lastbatchcompleted="2021-03-25T10:42:50.803" lastattention="1900-01-01T00:00:00.803" clientapp="Core .Net SqlClient Data Provider" hostname="FENGL-PC" hostpid="1029947883" loginname="test" isolationlevel="read committed (2)" xactid="6681936" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="2" stmtstart="6" stmtend="3806" sqlhandle="0x02000000f6ce2629ab0b4638a2fe81b7ce651293781b6ad90000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
WITH Activitys AS (
SELECT N'162873912634589184' AS ActivityId,0 AS Status,0 AS Status_Changed,NULL AS FinishDate,0 AS FinishDate_Changed,NULL AS DataFields,0 AS DataFields_Changed,NULL AS ErrorLog,0 AS ErrorLog_Changed,NULL AS ExtendData,0 AS ExtendData_Changed,NULL AS Historys,0 AS Historys_Changed,NULL AS Events,0 AS Events_Changed,NULL AS Worklist,0 AS Worklist_Changed,0x90 AS CheckPoints,1 AS CheckPoints_Changed,1 AS IsCheckPoint,1 AS IsCheckPoint_Changed,N'4' AS Version
UNION ALL
SELECT N'162873912672337920' AS ActivityId,0 AS Status,0 AS Status_Changed,NULL AS FinishDate,0 AS FinishDate_Changed,NULL AS DataFields,0 AS DataFields_Changed,NULL AS ErrorLog,0 AS ErrorLog_Changed,NULL AS ExtendData,0 AS ExtendData_Changed,NULL AS Historys,0 AS Historys_Changed,NULL AS Events,0 AS Events_Changed,NULL AS Worklist,0 AS Worklist_Changed,0x90 AS CheckPoints,1 AS CheckPoints_Changed,1 AS IsCheckPoint,1 AS IsCheckPoint_Changed,N'4' AS Version)
UPDATE S WITH(ROWLOCK)
SET S.Status = CASE WHEN T.Status_ </inputbuf>
</process>
<process id="process182e603b088" taskpriority="0" logused="0" waitresource="KEY: 11:72057594045595648 (d28e5e4a0ca6)" waittime="453" ownerId="6684790" transactionname="UPDATE" lasttranstarted="2021-03-25T10:42:50.900" XDES="0x1831b100490" lockMode="U" schedulerid="5" kpid="4000" status="suspended" spid="143" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-03-25T10:42:50.870" lastbatchcompleted="2021-03-25T10:42:50.873" lastattention="1900-01-01T00:00:00.873" clientapp="Core .Net SqlClient Data Provider" hostname="FENGL-PC" hostpid="1029947883" loginname="test" isolationlevel="read committed (2)" xactid="6684790" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="2" stmtstart="6" stmtend="3806" sqlhandle="0x0200000001c2f50a761a16df973e5eca36314be8034318fa0000000000000000000000000000000000000000">
unknown </frame> </executionStack>
<inputbuf>
WITH Activitys AS (
SELECT N'162873909157511171' AS ActivityId,0 AS Status,0 AS Status_Changed,NULL AS FinishDate,0 AS FinishDate_Changed,NULL AS DataFields,0 AS DataFields_Changed,NULL AS ErrorLog,0 AS ErrorLog_Changed,NULL AS ExtendData,0 AS ExtendData_Changed,NULL AS Historys,0 AS Historys_Changed,NULL AS Events,0 AS Events_Changed,NULL AS Worklist,0 AS Worklist_Changed,0x90 AS CheckPoints,1 AS CheckPoints_Changed,1 AS IsCheckPoint,1 AS IsCheckPoint_Changed,N'4' AS Version
UNION ALL
SELECT N'162873909153316864' AS ActivityId,0 AS Status,0 AS Status_Changed,NULL AS FinishDate,0 AS FinishDate_Changed,NULL AS DataFields,0 AS DataFields_Changed,NULL AS ErrorLog,0 AS ErrorLog_Changed,NULL AS ExtendData,0 AS ExtendData_Changed,NULL AS Historys,0 AS Historys_Changed,NULL AS Events,0 AS Events_Changed,NULL AS Worklist,0 AS Worklist_Changed,0x90 AS CheckPoints,1 AS CheckPoints_Changed,1 AS IsCheckPoint,1 AS IsCheckPoint_Changed,N'4' AS Version)
UPDATE S WITH(ROWLOCK)
SET S.Status = CASE WHEN T.Status_ </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594045595648" dbid="11" objectname="F3.dbo.RU_Activity" indexname="PK_RU_Activity" id="lock1831eaa8300" mode="U" associatedObjectId="72057594045595648">
<owner-list>
<owner id="process182e603b088" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process1831bd11c28" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594045595648" dbid="11" objectname="F3.dbo.RU_Activity" indexname="PK_RU_Activity" id="lock182df44cc80" mode="U" associatedObjectId="72057594045595648">
<owner-list>
<owner id="process1831bd11c28" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process182e603b088" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Key = ActivityId
I compared the two SQL and found that the key was not duplicated, so I couldn't understand why the key lock occurred
Environment: SQL Server 2017
Thank
