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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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