MS SQL Server 2019 - 主鍵更新行後出現死鎖，可能由觸發器引起

我有一張桌子：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [username] [nvarchar](65) NOT NULL,
   [nickname] [nvarchar](65) NOT NULL,
   [status] [tinyint] NOT NULL,
   [email_address] [nvarchar](255) NULL,
   [activation_date] [datetime] NOT NULL,
   [deactivation_date] [datetime] NULL,
   [language] [nvarchar](16) NULL,
   [last_modify_date] [datetime] NULL,
   [creation_date] [datetime] NOT NULL,
   [suspension_start_date] [datetime] NULL,
   [suspension_end_date] [datetime] NULL,
   [authentication_code] [int] NOT NULL,
   [federation_id] [int] NOT NULL,
CONSTRAINT [PK_users] PRIMARY KEY NONCLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

CREATE TRIGGER [dbo].[trg_users_modify_date] ON [dbo].[users] FOR INSERT,UPDATE 
AS
BEGIN
   DECLARE @UserID INT
   
   SELECT @UserID = id FROM INSERTED
   
   UPDATE users SET last_modify_date = dbo.fn_GetSystemDate()
   WHERE id = @UserID
END
GO
ALTER TABLE [dbo].[users] ENABLE TRIGGER [trg_users_modify_date]

這不是完整的 DDL，但我假設我選擇了所有重要的部分。

當使用者通過主鍵更新時，我注意到重複死鎖。這是死鎖圖：

<deadlock>
 <victim-list>
   <victimProcess id="process203fe3dd468"/>
 </victim-list>
 <process-list>
   <process XDES="0x2043ed48428" clientapp="Microsoft JDBC Driver for SQL Server" clientoption1="671088672" clientoption2="128058" currentdb="7" currentdbname="wchess-dev" ecid="0" hostname="pod-backend" hostpid="0" id="process203fe3dd468" isolationlevel="read committed (2)" kpid="3848" lastattention="1900-01-01T00:00:00.740" lastbatchcompleted="2020-11-24T13:51:32.740" lastbatchstarted="2020-11-24T13:51:32.743" lasttranstarted="2020-11-24T13:51:32.740" lockMode="U" lockTimeout="4294967295" loginname="login_wchess_dev" logused="0" ownerId="195074352" priority="0" sbid="0" schedulerid="1" spid="111" status="suspended" taskpriority="0" trancount="2" transactionname="implicit_transaction" waitresource="RID: 7:1:1911:29" waittime="5003" xactid="195074352">
     <executionStack>
       <frame line="1" procname="adhoc" sqlhandle="0x02000000fa186a2f32f20072197494be8c0806011fa23fc20000000000000000000000000000000000000000" stmtend="848" stmtstart="324">
unknown    </frame>
       <frame line="1" procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
     </executionStack>
     <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 smallint,@P5 date,@P6 date,@P7 nvarchar(4000),@P8 date,@P9 date,@P10 date,@P11 int,@P12 int)update users set username=@P0, nickname=@P1, federation_id=@P2, language=@P3, status=@P4, suspension_start_date=@P5, suspension_end_date=@P6, email_address=@P7, activation_date=@P8, deactivation_date=@P9, creation_date=@P10, authentication_code=@P11 where id=@P12   </inputbuf>
   </process>
   <process XDES="0x204399e4428" clientapp="Microsoft JDBC Driver for SQL Server" clientoption1="671219744" clientoption2="128058" currentdb="7" currentdbname="wchess-dev" ecid="0" hostname="pod-backend" hostpid="0" id="process203c32feca8" isolationlevel="read committed (2)" kpid="2680" lastattention="1900-01-01T00:00:00.743" lastbatchcompleted="2020-11-24T13:51:32.743" lastbatchstarted="2020-11-24T13:51:32.743" lasttranstarted="2020-11-24T13:51:32.740" lockMode="U" lockTimeout="4294967295" loginname="login_wchess_dev" logused="628" ownerId="195074353" priority="0" sbid="0" schedulerid="1" spid="109" status="suspended" taskpriority="0" trancount="2" transactionname="implicit_transaction" waitresource="KEY: 7:72057594071285760 (208031161cd1)" waittime="5003" xactid="195074353">
     <executionStack>
       <frame line="8" procname="wchess-dev.dbo.trg_users_modify_date" sqlhandle="0x030007006ab0ff55e20ae70063ac000000000000000000000000000000000000000000000000000000000000" stmtend="456" stmtstart="302">
UPDATE users SET last_modify_date = dbo.fn_GetSystemDate()
 WHERE id = @UserI    </frame>
       <frame line="1" procname="adhoc" sqlhandle="0x02000000fa186a2f32f20072197494be8c0806011fa23fc20000000000000000000000000000000000000000" stmtend="848" stmtstart="324">
unknown    </frame>
       <frame line="1" procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
     </executionStack>
     <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 smallint,@P5 date,@P6 date,@P7 nvarchar(4000),@P8 date,@P9 date,@P10 date,@P11 int,@P12 int)update users set username=@P0, nickname=@P1, federation_id=@P2, language=@P3, status=@P4, suspension_start_date=@P5, suspension_end_date=@P6, email_address=@P7, activation_date=@P8, deactivation_date=@P9, creation_date=@P10, authentication_code=@P11 where id=@P12   </inputbuf>
   </process>
 </process-list>
 <resource-list>
   <ridlock associatedObjectId="72057594044743680" dbid="7" fileid="1" id="lock203f0b88200" mode="X" objectname="wchess-dev.dbo.users" pageid="1911">
     <owner-list>
       <owner id="process203c32feca8" mode="X"/>
     </owner-list>
     <waiter-list>
       <waiter id="process203fe3dd468" mode="U" requestType="wait"/>
     </waiter-list>
   </ridlock>
   <keylock associatedObjectId="72057594071285760" dbid="7" hobtid="72057594071285760" id="lock2040a141880" indexname="PK_users" mode="U" objectname="wchess-dev.dbo.users">
     <owner-list>
       <owner id="process203fe3dd468" mode="U"/>
     </owner-list>
     <waiter-list>
       <waiter id="process203c32feca8" mode="U" requestType="wait"/>
     </waiter-list>
   </keylock>
 </resource-list>
</deadlock>

我使用此查詢從伺服器收集是否：

WITH fxd
AS (SELECT CAST(fx.event_data AS XML) AS Event_Data
   FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx )
SELECT dl.deadlockgraph
FROM
(
   SELECT dl.query('.') AS deadlockgraph
   FROM fxd
       CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl)
) AS dl;

我是調試 MS SQL 中的死鎖的新手。last_modify_date據我了解，由嘗試在其他人更新的行後更新列的觸發器引起的死鎖。

誰首先專門更新了行，我想不通：這樣的查詢對我來說非常混亂（保持原樣，沒有任何格式）。

(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000),@P4 smallint,@P5 date,@P6 date,@P7 nvarchar(4000),@P8 date,@P9 date,@P10 date,@P11 int,@P12 int)update users set username=@P0, nickname=@P1, federation_id=@P2, language=@P3, status=@P4, suspension_start_date=@P5, suspension_end_date=@P6, email_address=@P7, activation_date=@P8, deactivation_date=@P9, creation_date=@P10, authentication_code=@P11 where id=@P12

問題是如何避免這種僵局？我需要修復觸發器、一些數據庫連接參數或我的應用程式碼嗎？

伺服器：

Microsoft SQL Server 2019 (RTM-CU5) (KB4552255) - 15.0.4043.16 (X64)    Jun 10 2020 18:25:25    Copyright (C) 2019 Microsoft Corporation    Web Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

我正在使用 Hibertate 5.4.20.Final。

你的觸發器壞了，但這不是罪魁禍首。SQL Server 有語句觸發器，而不是行觸發器，所以應該是

CREATE TRIGGER [dbo].[trg_users_modify_date] ON [dbo].[users] FOR INSERT,UPDATE 
AS
BEGIN
   UPDATE users SET last_modify_date = dbo.fn_GetSystemDate()
   WHERE id in ( select id FROM INSERTED);
END

對於更新同一行（或恰好具有相同 lockid 的行）的兩個會話，此死鎖位於 PK 索引鍵鎖和堆 RID 鎖之間，一個從堆開始，另一個從非集群PK。要修復，只需給表一個聚集索引，並且將只有一個資料結構要更新，而不能選擇先更新哪個資料結構。例如

alter table users drop constraint PK_users;

alter table users add constraint PK_users 
primary key clustered (id);

SQL Server 強烈推薦聚集索引。這裡沒有充分的理由有兩個資料結構。刪除和重新創建所有 FK 是一件痛苦的事情，並且需要重新驗證重新創建的 FK。但這可能仍然值得做。

如果您不將 PK 和堆合併到單個聚集索引中，您可能能夠使用鎖定提示或查詢儲存來凍結查詢計劃，或者只是在應用程序中出現死鎖錯誤後重試。

或者您可以升級為 INSTEAD OF 觸發器，您可以在一個語句中更新整行，並使用鎖定提示控制鎖定獲取的順序，甚至完全序列化觸發器執行。

今天早上我又看了一眼鎖定，這是發生了什麼：

當你有一個非集群的 PK 時，update-by-id 查詢首先

1. 會話 1 UPDATE 讀取 PK 以找到行的 RID，對鍵進行 U 鎖定。
2. 會話 1 更新堆中的行，對該行使用 X 鎖。
3. 會話 1 釋放鍵上的 U 鎖，保留修改行上的 X 鎖。
4. 會話 2 UPDATE 讀取 PK 以找到行的 RID，對行進行 U 鎖
5. 會話 2 UPDATE 請求 RID 上的 X 鎖，但被會話 1 阻止
6. 會話 1 請求對密鑰進行 U 鎖定，但被會話 2 阻止。
7. 檢測到死鎖並終止會話 2 的事務（未進行任何更改）。

SQL Server 在 UPDATE 結束時和触發器之前釋放 PK 鍵 U 鎖以最大限度地提高並發性，但由於觸發器需要相同的鎖，這會產生死鎖的可能性。為防止 SQL Server 在事務中間釋放鎖，鎖提示為 (holdlock)。所以

update users with (holdlock) set ...

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