September 5, 2019 at 5:28 am
Hi,
We are getting the following deadlock and can't figure out how to resolve it.
The parameter @LDG_I is different for each delete statement.
<deadlock>
<victim-list>
<victimProcess id="processabfa5f468" />
</victim-list>
<process-list>
<process id="processabfa5f468" taskpriority="0" logused="115648" waitresource="KEY: 9:72058742013100032 (d8d3e860806c)" waittime="1911" ownerId="4040504704" transactionname="user_transaction" lasttranstarted="2019-09-04T16:15:01.660" XDES="0x5308ef1c0" lockMode="U" schedulerid="2" kpid="18928" status="suspended" spid="195" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-09-04T16:15:02.850" lastbatchcompleted="2019-09-04T16:15:02.850" lastattention="1900-01-01T00:00:00.850" clientapp="IPOSScheduledProcesses" hostname="CHAVMWSSWBPDBE1" hostpid="5368" loginname="CITY\SQLSvcAcc" isolationlevel="read committed (2)" xactid="4040504704" currentdb="9" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="SunSystemsData.dbo.PA_LDG_EXT_POST_JRNAL" line="348" stmtstart="30456" stmtend="30804" sqlhandle="0x030009009efc9775372d2b014aaa000001000000000000000000000000000000000000000000000000000000">
delete lad from PA_LDG_HOLD_JRNAL_LINES_LAD lad inner join PA_LDG_JRNAL jrl on (lad.JRNAL_ID = jrl.HOLD_JRNAL_ID and lad.LINE_ID = jrl.HOLD_LINE_ID) where jrl.LDG_ID = @LDG_I </frame>
<frame procname="SunSystemsData.dbo.PA_LDG_EXT_POST_JRNAL_WRAP" line="10" stmtstart="608" stmtend="876" sqlhandle="0x03000900d7208c76ef2d2b014aaa000001000000000000000000000000000000000000000000000000000000">
exec PA_LDG_EXT_POST_JRNAL @JRNAL_ID, @USER_ID, @POST_JOURNAL, @JOURNAL_NUMBERS output, @ERROR_STR output, @RET_CODE output </frame>
<frame procname="SunSystemsData.dbo.PA_IPOS_POST_JOURNAL" line="36" stmtstart="2458" stmtend="2594" sqlhandle="0x0300090056ac6a3de1fa4801bdaa000001000000000000000000000000000000000000000000000000000000">
exec PA_LDG_EXT_POST_JRNAL_WRAP @jrnal_id, @sun_user, 'Y' </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 9 Object Id = 1030401110] </inputbuf>
</process>
<process id="processc18c93848" taskpriority="0" logused="129944" waitresource="KEY: 9:72058742013100032 (0d4a60692817)" waittime="350" ownerId="4040507716" transactionname="user_transaction" lasttranstarted="2019-09-04T16:15:02.963" XDES="0x883875350" lockMode="U" schedulerid="2" kpid="5380" status="suspended" spid="158" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-09-04T16:15:05.210" lastbatchcompleted="2019-09-04T16:15:05.210" lastattention="1900-01-01T00:00:00.210" clientapp="IPOSXMLService" hostname="CHAVMWSSWBPDBE1" hostpid="7672" loginname="CITY\SQLSvcAcc" isolationlevel="read committed (2)" xactid="4040507716" currentdb="9" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="SunSystemsData.dbo.PA_LDG_EXT_POST_JRNAL" line="348" stmtstart="30456" stmtend="30804" sqlhandle="0x030009009efc9775372d2b014aaa000001000000000000000000000000000000000000000000000000000000">
delete lad from PA_LDG_HOLD_JRNAL_LINES_LAD lad inner join PA_LDG_JRNAL jrl on (lad.JRNAL_ID = jrl.HOLD_JRNAL_ID and lad.LINE_ID = jrl.HOLD_LINE_ID) where jrl.LDG_ID = @LDG_I </frame>
<frame procname="SunSystemsData.dbo.PA_LDG_EXT_POST_JRNAL_WRAP" line="10" stmtstart="608" stmtend="876" sqlhandle="0x03000900d7208c76ef2d2b014aaa000001000000000000000000000000000000000000000000000000000000">
exec PA_LDG_EXT_POST_JRNAL @JRNAL_ID, @USER_ID, @POST_JOURNAL, @JOURNAL_NUMBERS output, @ERROR_STR output, @RET_CODE output </frame>
<frame procname="SunSystemsData.dbo.PA_IPOS_POST_JOURNAL" line="36" stmtstart="2458" stmtend="2594" sqlhandle="0x0300090056ac6a3de1fa4801bdaa000001000000000000000000000000000000000000000000000000000000">
exec PA_LDG_EXT_POST_JRNAL_WRAP @jrnal_id, @sun_user, 'Y' </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 9 Object Id = 1030401110] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72058742013100032" dbid="9" objectname="SunSystemsData.dbo.PA_LDG_HOLD_JRNAL_LINES_LAD" indexname="PK_PA_LDG_HOLD_JRNAL_LINES_LAD" id="lock1be09c680" mode="X" associatedObjectId="72058742013100032">
<owner-list>
<owner id="processc18c93848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processabfa5f468" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72058742013100032" dbid="9" objectname="SunSystemsData.dbo.PA_LDG_HOLD_JRNAL_LINES_LAD" indexname="PK_PA_LDG_HOLD_JRNAL_LINES_LAD" id="lock35cbc8ab80" mode="X" associatedObjectId="72058742013100032">
<owner-list>
<owner id="processabfa5f468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processc18c93848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Here is the create table statement.
CREATE TABLE [dbo].[PA_LDG_HOLD_JRNAL_LINES_LAD](
[JRNAL_ID] [numeric](16, 0) NOT NULL,
[LINE_ID] [numeric](7, 0) NOT NULL,
[GNRL_DESCR_01] [nvarchar](30) NULL,
[GNRL_DESCR_02] [nvarchar](30) NULL,
[GNRL_DESCR_03] [nvarchar](30) NULL,
[GNRL_DESCR_04] [nvarchar](30) NULL,
[GNRL_DESCR_05] [nvarchar](30) NULL,
[GNRL_DESCR_06] [nvarchar](30) NULL,
[GNRL_DESCR_07] [nvarchar](30) NULL,
[GNRL_DESCR_08] [nvarchar](30) NULL,
[GNRL_DESCR_09] [nvarchar](30) NULL,
[GNRL_DESCR_10] [nvarchar](30) NULL,
[GNRL_DESCR_11] [nvarchar](30) NULL,
[GNRL_DESCR_12] [nvarchar](30) NULL,
[GNRL_DESCR_13] [nvarchar](30) NULL,
[GNRL_DESCR_14] [nvarchar](30) NULL,
[GNRL_DESCR_15] [nvarchar](30) NULL,
[GNRL_DESCR_16] [nvarchar](30) NULL,
[GNRL_DESCR_17] [nvarchar](30) NULL,
[GNRL_DESCR_18] [nvarchar](30) NULL,
[GNRL_DESCR_19] [nvarchar](30) NULL,
[GNRL_DESCR_20] [nvarchar](30) NULL,
[GNRL_DESCR_21] [nvarchar](30) NULL,
[GNRL_DESCR_22] [nvarchar](30) NULL,
[GNRL_DESCR_23] [nvarchar](30) NULL,
[GNRL_DESCR_24] [nvarchar](30) NULL,
[GNRL_DESCR_25] [nvarchar](30) NULL,
[GNRL_1_DATETIME] [datetime] NULL,
[GNRL_2_DATETIME] [datetime] NULL,
[GNRL_3_DATETIME] [datetime] NULL,
[GNRL_4_DATETIME] [datetime] NULL,
[GNRL_5_DATETIME] [datetime] NULL,
CONSTRAINT [PK_PA_LDG_HOLD_JRNAL_LINES_LAD] PRIMARY KEY CLUSTERED
(
[JRNAL_ID] ASC,
[LINE_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
September 5, 2019 at 11:19 am
Hi Paul!
If i'm not mistaken, you have issue with running the same proc in parallel sessions simultaneously.
I suspect there's lock escalation happens which affects second session.
I would try to disable TABLE lock escalation on PA_LDG_HOLD_JRNAL_LINES_LAD table, if it's not large, and check the effect.
However, the more efficient way is to amend the code of the proc (Database Id = 9 Object Id = 1030401110) or do not run the proc in parallel.
September 5, 2019 at 3:45 pm
Could you post the query plan, estimated is good enough for now, for this SQL:
delete lad from PA_LDG_HOLD_JRNAL_LINES_LAD lad inner join PA_LDG_JRNAL jrl on (lad.JRNAL_ID = jrl.HOLD_JRNAL_ID and lad.LINE_ID = jrl.HOLD_LINE_ID) where jrl.LDG_ID = @LDG_I
What's the DDL for the other table, PA_LDG_JRNAL? How many rows are in that table, and what indexes exist on that table? I'd suspect you already had an index on LDG_ID on that table, but it'd be nice to be sure and to see all the details.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 6, 2019 at 1:28 am
Thanks for all your responses. Your idea's did prompt us to rethink what we where doing.
We where able to fix this problem by removing the link to the PA_LDG_JRNAL table even though it did not form part of the deadlock chain.
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply