並發批量更新觸發鍵鎖

我正在做一個批量更新操作，但它出現了鍵鎖我覺得我有麻煩了。

我不明白為什麼會發生僵局。

環境：SQL Server 2016

工作模式：並發批量更新

死鎖圖詳解：

<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 表腳本

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

看了兩條sql語句，沒有發現重複的key，所以沒看懂這是什麼。請告訴我

2021-04-12 添加：

lock res：

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

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

execution plan:

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

解釋SQL：我將多條需要更新的數據組合成一個更新表，然後將它們連接在一起做更新操作。如果受影響的行數等於我需要更新的行數，則表示所有數據更新成功。如果不是，則意味著某些數據更新失敗。我需要查詢數據的目前狀態並返回給程序

謝謝

看起來您遇到此問題的原因是索引查找沒有排序。這是由 to 的隱式轉換引起nvarchar的bigint。

這導致編譯器不對值進行預排序，因為它沒有意識到 CTE 中的值正在用於查找。您可以在計劃中看到它無緣無故地添加了一個不同的排序，它顯然不需要。

將數據類型更改為 後bigint，值將被預先排序，這意味著搜尋按Id. 這意味著任何其他事務也將以相同的順序鎖定，從而避免死鎖。

引用自：https://dba.stackexchange.com/questions/289837