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.