2

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. enter image description here

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

0
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.

implicit

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.

Distinct Sort

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.

1

Based on trancount="2" in the XML, it looks like these statements are being run as part of an explicit transaction1. 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.

1 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

1
  • run the SQL, please see the execution plan, all the SQL only update the number of rows will change, other will not change, thank you
    – Jack Feng
    Apr 19 at 3:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.