Key lock triggered by concurrent batch update

Question

I'm doing a batch update operation,but it appears key lock I think I'm in trouble.

I don't understand why the deadlock is happening.

Environment: SQL Server 2016

Work mode：concurrent batch update

deadlock graph detailed：

<deadlock-list>
 <deadlock victim="process29617519088">
  <process-list>
   <process id="process29617519088" taskpriority="0" logused="0" waitresource="KEY: 14:72057594042122240 (aff034961e05)" waittime="2834" ownerId="59072177" transactionname="UPDATE" lasttranstarted="2021-04-12T16:50:19.377" XDES="0x295a2ef6e58" lockMode="U" schedulerid="7" kpid="7808" status="suspended" spid="91" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-04-12T16:50:19.340" lastbatchcompleted="2021-04-12T16:50:19.343" lastattention="1900-01-01T00:00:00.343" clientapp="Core .Net SqlClient Data Provider" hostname="FENGL-PC" hostpid="1294251125" loginname="sa" isolationlevel="read committed (2)" xactid="59072177" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="2" stmtstart="6" stmtend="942" sqlhandle="0x0200000010732f2ceb34267aaa1b31c44ae92e2ec57b3e1e0000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>

 WITH Activitys AS (
 SELECT N'169489381063868436' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381059674113' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381013536784' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381017731089' AS Id,N'Name-2' AS Name,1 AS Version) 
UPDATE S
SET S.Name = T.Name,
S.Version = S.Version + 1
FROM dbo.ActivityDto S JOIN Activitys T ON S.[Id]=T.[Id] AND S.[Version]=T.[Version];
 IF @@ROWCOUNT=4 BEGIN SELECT CAST(0 AS BIGINT) AS Id,0 AS Version END ELSE BEGIN
 WITH Ids AS (
 SELECT N'169489381063868436' AS Id 
UNION ALL 
 SELECT N'169489381059674113' AS Id 
UNION ALL 
 SELECT N'169489381013536784' AS Id 
UNION ALL 
 SELECT N'169489381017731089' AS Id) 

 SELECT S.Id,S.Version FROM dbo.ActivityDto S  JOIN Ids T ON S.[Id]=T.[Id] END    </inputbuf>
   </process>
   <process id="process295a1298ca8" taskpriority="0" logused="0" waitresource="KEY: 14:72057594042122240 (075b1091eea1)" waittime="2826" ownerId="59071581" transactionname="UPDATE" lasttranstarted="2021-04-12T16:50:19.353" XDES="0x2959a542e58" lockMode="U" schedulerid="9" kpid="5136" status="suspended" spid="139" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-04-12T16:50:19.337" lastbatchcompleted="2021-04-12T16:50:19.343" lastattention="1900-01-01T00:00:00.343" clientapp="Core .Net SqlClient Data Provider" hostname="FENGL-PC" hostpid="1294251125" loginname="sa" isolationlevel="read committed (2)" xactid="59071581" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="2" stmtstart="6" stmtend="1258" sqlhandle="0x020000006468c32fdcd8118755392016036171d4d3a33c1d0000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>

 WITH Activitys AS (
 SELECT N'169489381063868437' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381013536775' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381013536789' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381063868431' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381009342465' AS Id,N'Name-2' AS Name,1 AS Version  
UNION ALL 
 SELECT N'169489381097422858' AS Id,N'Name-2' AS Name,1 AS Version) 
UPDATE S
SET S.Name = T.Name,
S.Version = S.Version + 1
FROM dbo.ActivityDto S JOIN Activitys T ON S.[Id]=T.[Id] AND S.[Version]=T.[Version];
 IF @@ROWCOUNT=6 BEGIN SELECT CAST(0 AS BIGINT) AS Id,0 AS Version END ELSE BEGIN
 WITH Ids AS (
 SELECT N'169489381063868437' AS Id 
UNION ALL 
 SELECT N'169489381013536775' AS Id 
UNION ALL 
 SELECT N'169489381013536789' AS Id 
UNION ALL 
 SELECT N'169489381063868431' AS Id 
UNION ALL 
 SELECT N'169489381009342465' AS Id 
UNION ALL 
 SELECT N'169489381097422858' AS Id) 

    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594042122240" dbid="14" objectname="DbTest.dbo.ActivityDto" indexname="PK_ActivityDto" id="lock296177e3c80" mode="U" associatedObjectId="72057594042122240">
    <owner-list>
     <owner id="process295a1298ca8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process29617519088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594042122240" dbid="14" objectname="DbTest.dbo.ActivityDto" indexname="PK_ActivityDto" id="lock295ac129d80" mode="U" associatedObjectId="72057594042122240">
    <owner-list>
     <owner id="process29617519088" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process295a1298ca8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Activitydto table script

CREATE TABLE [dbo].[ActivityDto](
    [Id] [bigint] NOT NULL,
    [ProcInstId] [bigint] NULL,
    [Name] [nvarchar](500) NULL,
    [Version] [int] NULL,
 CONSTRAINT [PK_ActivityDto] 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]
GO

I looked at two SQL statements and found no duplicate key, So I don't understand what this is. Please let me know

2021-04-12 add:

lock res：

waitresource="KEY: 14:72057594042122240 (aff034961e05)"

lockResKey	Id	ProcInstId
(aff034961e05)	169489381013536784	169489376328499300

waitresource="KEY: 14:72057594042122240 (075b1091eea1)"

lockResKey	Id	ProcInstId
(075b1091eea1)	169489381059674113	169489376324304968

execution plan:

https://www.brentozar.com/pastetheplan/?id=Hyd-39WUd

Explain SQL: I will combine multiple pieces of data that need to be updated to form an update table, and then connect them together to do the update operation. If the number of affected rows is equal to the number of rows that I need to update, it means that all data updates are successful. If not, it means that some data updates fail. I need to query the current status of the data and return it to the program

thank

Answer 1

It looks like the reason you are getting this issue is that the the index seeks are not ordered. This is caused by an implicit conversion from nvarchar to bigint.

This is causing the compiler to not pre-sort the values, because it doesn't realize that the values in the CTE are being used for the seek. You can see in the plan that it is adding a Sort Distinct for no reason, which it obviously doesn't need.

Once you change the data type to bigint, the values will be pre-sorted, which means that the seeks happen in order of Id. This then means that any other transaction will also lock in the same order, thereby avoiding the deadlock.

Answer 2

Based on trancount="2" in the XML, it looks like these statements are being run as part of an explicit transaction¹. I would check to make sure that previous statements in the same transaction aren't holding these unexpected locks.

Also, confirm there are no triggers running on this table that could be taking more locks.

Based on the information you've provided, the victim process was waiting on a key lock for Id 169489381013536784. The other (winning) process was holding that lock, and waiting on a lock for Id 169489381059674113.

Since neither of those Ids appear in the <inputbuf> text for the winning process, the only explanation I can think of is that more code was run inside this transaction than is visible in the deadlock XML. That's where you'll need to focus your attention.

See Erik Darling's blog post on Deadlock Graph Frustrations for some details and examples of this:

Even though it’s the updates holding locks, the select queries show up as owners.

¹ The current transaction, in the deadlock graph, named "UPDATE" is the "2nd" transaction, because UPDATEs are transactions on their own as far as this is concerned