I've a table:
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]
This is not full DDL, but I'm assuming I chosen all important parts.
I noticed repeating deadlocks when users updated by primary key. Here is deadlock graph:
<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>
I gathered if from server using this query:
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;
I'm new to debugging deadlocks in MS SQL. As I understand, deadlock caused by trigger which trying to update last_modify_date
column after row updated by someone else.
Who specifically updates row in the first place, I couldn't figure it out: such a query is very confusing to me (leaving it as is, without any formatting).
(@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
The question is how to avoid this deadlock? Do I need to fix trigger, some DB connect params, or my application code?
Server:
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)
I'm using Hibertate 5.4.20.Final.
Best Answer
Your trigger's broken, but it's not to blame. SQL Server has Statement Triggers, not Row Triggers, so it should be
This deadlock is between a PK index key lock and a heap RID lock for two sessions that are uptating the same row (or rows that happen to have the same lockid), and one starts with the heap, and the other starts with the non-clustered PK. To fix just give the table a clustered index, and there will be only a single data structure to update, and no choice of which one to update first. eg
Clustered indexes are strongly preferred in SQL Server. There's just no good reason to have two data structures here. Dropping and recreating all the FKs is a pain, and will require re-validating the FKs on recreation. But it's probably still worth doing.
If you don't consolidate the PK and the heap into a single clustered index, you may be able to use lock hints or the Query Store to freeze the query plan, or simply retry after deadlock errors in the application.
Or you could escalate to an INSTEAD OF trigger where you can update the whole row in one statement, and control the order of lock acquisition with a lock hint, or even fully serialize the trigger execution.
I took another look at the locking this morning, and here's what's going on:
When you have a non-clustered PK, the update-by-id query first
SQL Server is releasing the PK key U lock at the end of the UPDATE and before the trigger to maximize concurrency, but since the trigger needs the same lock that creates the possibility for a deadlock. To prevent SQL Server from releasing a lock in the middle of a transaction, the lock hint is (holdlock). SO
update users with (holdlock) set ...