July 17, 2014 at 7:59 am
I am wrestling with an issue where sessions are deadlocking when trying to update a table. The table is relatively large, having just over 10 million rows. The table has a clustered index on the unique ID column. Multiple sessions attempt to update the table using ID = <somevalue> as the UPDATE predicate. And each session is trying to update a different row. I see deadlocking occurring quite often where one session has an IX lock on a lock partition and is attempting to obtain an X lock on the object. Another session will also be attempting to obtain an X lock. The second session is blocked by the IX lock when it tries to obtain the X lock on the locked partition, and the first session is blocked trying to obtain an X lock on lock partition 0.
Since the same table, and the same index is used by both, I am unable to figure out how to resolve the deadlocking scenario. Looking for suggestions.
As a temporary solution, I have introduced code wrapping the access to this table with sp_getapplock functionality. However, I would prefer to find another solution.
Thanks in advance.
Gordon
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
July 17, 2014 at 8:21 am
Can you post the deadlock graph from the system_health extended event?
July 17, 2014 at 8:24 am
<deadlock-list>
<deadlock victim="processde6c988">
<process-list>
<process id="processde6c988" taskpriority="0" logused="276" waitresource="OBJECT: 5:1913773875:3 " waittime="100" ownerId="15379852" transactionname="user_transaction" lasttranstarted="2014-07-07T20:36:51.157" XDES="0x800d2ff0" lockMode="X" schedulerid="11" kpid="1820" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-07T20:36:51.057" lastbatchcompleted="2014-07-07T20:36:51.057" hostname="172.16.21.100" hostpid="0" loginname="aprimosa" isolationlevel="read committed (2)" xactid="15379852" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="540" sqlhandle="0x02000000f492ff2215ed2a7acf3d1dc252c7e9b9ba37c32f">
UPDATE [lead_ext_attributes] set [ExtValue11543] = @1,[ExtValue11544] = @2,[ExtValue11576] = @3,[ExtValue11571] = @4,[ExtValue11572] = @5,[ExtValue11573] = @6,[ExtValue11574] = @7,[ExtValue14400] = @8,[ExtValue11562] = @9,[ExtValue14318] = @10,[ExtValue14319] = @11,[ExtValue22600] = NULL,[ExtValue22700] = NULL,[ExtValue11551] = @12,[extvalue17209] = @13,[ExtValue17301] = NULL,[ExtValue11545] = @14,[ExtValue11542] = @15 WHERE [lead_id]=@16 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x020000007584793886adbd944dc859df7403ccf8d6ea7dac">
UPDATE lead_ext_attributes SET ExtValue11543='Closed', ExtValue11544='Hot', ExtValue11576='mrausch', ExtValue11571='Melissa', ExtValue11572='Rausch', ExtValue11573='Melissa.Rausch@ohecampus.com', ExtValue11574='+1 443-627-7655', ExtValue14400='wwate6xxxx', ExtValue11562='2009-12-03T00:00:00', ExtValue14318='Application Withdrawn', ExtValue14319='No longer interested', ExtValue22600=NULL, ExtValue22700=NULL, ExtValue11551='2009-08-31T00:58:53.880', extvalue17209='21-30', ExtValue17301=NULL, ExtValue11545='6.110360100000000e-001', ExtValue11542='815323' WHERE lead_id = 8182784 </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">
sp_executesql </frame>
<frame procname="aprimo.dbo.usp_LHEG_Onyx60_Aprimo_Integration_Incident" line="459" stmtstart="36148" stmtend="36318" sqlhandle="0x030005005b07d849a80033011aa200000100000000000000">
EXEC sp_executesql @SQLNonLead --Execute NonAudienceMember TABLE statements </frame>
<frame procname="aprimo.dbo.usp_LHEG_Onyx60_Aprimo_Integration" line="40" stmtstart="3660" stmtend="3850" sqlhandle="0x030005009680ee55badb380099a200000100000000000000">
EXEC @returnCode = [dbo].[usp_LHEG_Onyx60_Aprimo_Integration_Incident] @xmlStr = @xmlStr </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 1441693846] </inputbuf>
</process>
<process id="processde27048" taskpriority="0" logused="276" waitresource="OBJECT: 5:1913773875:0 " waittime="490" ownerId="15386819" transactionname="user_transaction" lasttranstarted="2014-07-07T20:36:53.653" XDES="0x6b70663b0" lockMode="X" schedulerid="4" kpid="7884" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-07T20:36:53.560" lastbatchcompleted="2014-07-07T20:36:53.560" hostname="172.16.21.101" hostpid="0" loginname="aprimosa" isolationlevel="read committed (2)" xactid="15386819" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="504" sqlhandle="0x02000000e1d30d0695d52e36d68130d28c25be0b66989ddf">
UPDATE [lead_ext_attributes] set [ExtValue11543] = @1,[ExtValue11544] = @2,[ExtValue11576] = @3,[ExtValue11571] = @4,[ExtValue11572] = @5,[ExtValue11573] = @6,[ExtValue11574] = NULL,[ExtValue14400] = @7,[ExtValue11562] = @8,[ExtValue14318] = @9,[ExtValue14319] = @10,[ExtValue22600] = NULL,[ExtValue22700] = NULL,[ExtValue11551] = @11,[extvalue17209] = @12,[ExtValue17301] = NULL,[ExtValue11545] = @13,[ExtValue11542] = @14 WHERE [lead_id]=@15 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000f59f6f21034dd5039265f82f4c2ff6b34dc613c6">
UPDATE lead_ext_attributes SET ExtValue11543='Closed', ExtValue11544='Hot', ExtValue11576='spodsiadlo', ExtValue11571='Sebastian', ExtValue11572='Podsiadlo', ExtValue11573='Sebastian.Podsiadlo@ohecampus.com', ExtValue11574=NULL, ExtValue14400='wwate7xxxx', ExtValue11562='2010-08-26T00:00:00', ExtValue14318='Not Qualified', ExtValue14319='Failed English Language Assessment', ExtValue22600=NULL, ExtValue22700=NULL, ExtValue11551='2009-08-31T16:46:33.220', extvalue17209='10-15', ExtValue17301=NULL, ExtValue11545='1.584076000000000e+000', ExtValue11542='815684' WHERE lead_id = 8014117 </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">
sp_executesql </frame>
<frame procname="aprimo.dbo.usp_LHEG_Onyx60_Aprimo_Integration_Incident" line="459" stmtstart="36148" stmtend="36318" sqlhandle="0x030005005b07d849a80033011aa200000100000000000000">
EXEC sp_executesql @SQLNonLead --Execute NonAudienceMember TABLE statements </frame>
<frame procname="aprimo.dbo.usp_LHEG_Onyx60_Aprimo_Integration" line="40" stmtstart="3660" stmtend="3850" sqlhandle="0x030005009680ee55badb380099a200000100000000000000">
EXEC @returnCode = [dbo].[usp_LHEG_Onyx60_Aprimo_Integration_Incident] @xmlStr = @xmlStr </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 1441693846] </inputbuf>
</process>
<process id="processde30988" taskpriority="0" logused="276" waitresource="OBJECT: 5:1913773875:0 " waittime="1879" ownerId="15379332" transactionname="user_transaction" lasttranstarted="2014-07-07T20:36:51.047" XDES="0x8000f3c0" lockMode="X" schedulerid="5" kpid="10228" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-07T20:36:50.960" lastbatchcompleted="2014-07-07T20:36:50.960" hostname="172.16.21.101" hostpid="0" loginname="aprimosa" isolationlevel="read committed (2)" xactid="15379332" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="360" sqlhandle="0x02000000fb7336179fb9808a01594417ac03ea5fa1bd2fb2">
UPDATE [lead_ext_attributes] set [ExtValue11543] = @1,[ExtValue11544] = @2,[ExtValue14318] = NULL,[ExtValue14319] = NULL,[ExtValue11542] = @3,[ExtValue11571] = @4,[ExtValue11551] = @5,[ExtValue11572] = @6,[ExtValue11574] = NULL,[ExtValue11573] = @7,[ExtValue11576] = @8,[ExtValue14400] = @9,[extvalue17209] = @10 WHERE [lead_id]=@11 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000bec358061c64da149a3987954dc7c59b2c47e7af">
UPDATE lead_ext_attributes SET ExtValue11543='Uncontacted', ExtValue11544='Hot', ExtValue14318=NULL, ExtValue14319=NULL, ExtValue11542='2425396', ExtValue11571='Miguel', ExtValue11551='2014-05-31T02:16:03.697', ExtValue11572='Nunes', ExtValue11574=NULL, ExtValue11573='Miguel.Nunes@roehampton-online.com', ExtValue11576='mnunes', ExtValue14400='4311200', extvalue17209='10-15' WHERE lead_id = 10879213 </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">
sp_executesql </frame>
<frame procname="aprimo.dbo.usp_LHEG_Onyx60_Aprimo_Integration_Incident" line="459" stmtstart="36148" stmtend="36318" sqlhandle="0x030005005b07d849a80033011aa200000100000000000000">
EXEC sp_executesql @SQLNonLead --Execute NonAudienceMember TABLE statements </frame>
<frame procname="aprimo.dbo.usp_LHEG_Onyx60_Aprimo_Integration" line="40" stmtstart="3660" stmtend="3850" sqlhandle="0x030005009680ee55badb380099a200000100000000000000">
EXEC @returnCode = [dbo].[usp_LHEG_Onyx60_Aprimo_Integration_Incident] @xmlStr = @xmlStr </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 1441693846] </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="3" objid="1913773875" subresource="FULL" dbid="5" objectname="aprimo.dbo.lead_ext_attributes" id="lockeba4280" mode="IX" associatedObjectId="1913773875">
<owner-list>
<owner id="processde27048" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="processde6c988" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1913773875" subresource="FULL" dbid="5" objectname="aprimo.dbo.lead_ext_attributes" id="lockef5e180" mode="X" associatedObjectId="1913773875">
<owner-list/>
<waiter-list>
<waiter id="processde27048" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1913773875" subresource="FULL" dbid="5" objectname="aprimo.dbo.lead_ext_attributes" id="lockef5e180" mode="X" associatedObjectId="1913773875">
<owner-list>
<owner id="processde6c988" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processde30988" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
July 17, 2014 at 8:46 am
As well as the deadlock graph, please can you post the table definitions and definitions of all the indexes on it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2014 at 8:53 am
Full table DDL, including indexes and trigger definitions
CREATE TABLE [dbo].[lead_ext_attributes]
(
[lead_id] [INT] NOT NULL
, [ExtValue11541] [NVARCHAR](100) NULL
, [ExtValue11542] [INT] NULL
, [ExtValue11543] [NVARCHAR](30) NULL
, [ExtValue11544] [NVARCHAR](10) NULL
, [ExtValue11545] [FLOAT] NULL
, [ExtValue11546] [INT] NULL
, [ExtValue11547] [FLOAT] NULL
, [ExtValue11548] [INT] NULL
, [ExtValue11549] [FLOAT] NULL
, [ExtValue11550] [INT] NULL
, [ExtValue11551] [DATETIME] NULL
, [ExtValue11552] [NVARCHAR](10) NULL
, [ExtValue11553] [NVARCHAR](255) NULL
, [ExtValue11554] [NVARCHAR](25) NULL
, [ExtValue11555] [INT] NULL
, [ExtValue11556] [DATETIME] NULL
, [ExtValue11557] [NVARCHAR](10) NULL
, [ExtValue11558] [NVARCHAR](255) NULL
, [ExtValue11559] [NVARCHAR](25) NULL
, [ExtValue11560] [NVARCHAR](3) NULL
, [ExtValue11561] [NTEXT] NULL
, [ExtValue11562] [DATETIME] NULL
, [ExtValue11563] [DATETIME] NULL
, [ExtValue11569] [DATETIME] NULL
, [ExtValue11570] [DATETIME] NULL
, [ExtValue11571] [NVARCHAR](255) NULL
, [ExtValue11572] [NVARCHAR](255) NULL
, [ExtValue11573] [NVARCHAR](255) NULL
, [ExtValue11574] [NVARCHAR](40) NULL
, [ExtValue11575] [NVARCHAR](10) NULL
, [ExtValue11576] [NVARCHAR](255) NULL
, [ExtValue11600] [INT] NULL
, [ExtValue11700] [NVARCHAR](255) NULL
, [ExtValue14318] [NVARCHAR](50) NULL
, [ExtValue14319] [NVARCHAR](50) NULL
, [ExtValue14320] [DATETIME] NULL
, [ExtValue14321] [INT] NULL
, [ExtValue14400] [NVARCHAR](50) NULL
, [ExtValue15200] [NVARCHAR](30) NULL
, [ExtValue16300] [NVARCHAR](20) NULL
, [ExtValue16737] [NVARCHAR](80) NULL
, [ExtValue16738] [NVARCHAR](75) NULL
, [ExtValue16739] [NVARCHAR](80) NULL
, [ExtValue16740] [NVARCHAR](80) NULL
, [ExtValue16741] [NVARCHAR](80) NULL
, [ExtValue16742] [NVARCHAR](80) NULL
, [ExtValue16743] [NVARCHAR](80) NULL
, [ExtValue16744] [NVARCHAR](100) NULL
, [ExtValue16802] [NVARCHAR](80) NULL
, [ExtValue16803] [NVARCHAR](80) NULL
, [ExtValue16905] [NVARCHAR](80) NULL
, [ExtValue16906] [NTEXT] NULL
, [ExtValue16900] [NVARCHAR](80) NULL
, [ExtValue16901] [NVARCHAR](80) NULL
, [ExtValue16902] [NVARCHAR](80) NULL
, [ExtValue16903] [NVARCHAR](80) NULL
, [ExtValue16904] [NVARCHAR](80) NULL
, [ExtValue17006] [NVARCHAR](255) NULL
, [ExtValue17005] [NVARCHAR](255) NULL
, [ExtValue17206] [NVARCHAR](100) NULL
, [ExtValue17207] [NVARCHAR](255) NULL
, [ExtValue17208] [NVARCHAR](80) NULL
, [ExtValue17209] [NVARCHAR](255) NULL
, [ExtValue17210] [NVARCHAR](255) NULL
, [ExtValue17300] [NVARCHAR](255) NULL
, [ExtValue17301] [NVARCHAR](255) NULL
, [ExtValue17302] [NVARCHAR](255) NULL
, [ExtValue17400] [NTEXT] NULL
, [ExtValue21600] [DATETIME] NULL
, [ExtValue22600] [NVARCHAR](50) NULL
, [ExtValue22700] [NVARCHAR](50) NULL
, CONSTRAINT [XPKlead_ext_attributes] PRIMARY KEY CLUSTERED
( [lead_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] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[lead_ext_attributes] WITH NOCHECK ADD CONSTRAINT [LEAD_EXT_ATTR_FK_LEADS] FOREIGN KEY([lead_id])
REFERENCES [dbo].[leads] ([lead_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[lead_ext_attributes] CHECK CONSTRAINT [LEAD_EXT_ATTR_FK_LEADS]
GO
CREATE NONCLUSTERED INDEX [_IDX_Auto_46_INC_1] ON [dbo].[lead_ext_attributes]
(
[ExtValue16300] ASC
)
INCLUDE ( [lead_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NCIdx_LeadExtAttrib_ExtAtrib11542] ON [dbo].[lead_ext_attributes]
(
[ExtValue11542] ASC
)
INCLUDE ( [lead_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 75) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NCIdx_LeadExtAttrib_ExtAtrib11576] ON [dbo].[lead_ext_attributes]
(
[ExtValue11576] ASC
)
INCLUDE ( [ExtValue11574],
[ExtValue11575],
[ExtValue15200]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 75) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[AGS_ti_Lead_ID] ON [dbo].[lead_ext_attributes]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF @@ROWCOUNT = 1
BEGIN
/* SINGLE ROW INSERT */
UPDATE [Lead_Ext_attributes]
SET [ExtValue11600] = inserted.[Lead_ID]
FROM inserted;
END
ELSE
BEGIN
/* BULK INSERT */
DECLARE @LeadID INT
DECLARE Cur CURSOR
FOR
SELECT lead_id
FROM inserted
OPEN cur
FETCH NEXT FROM cur INTO @LeadID
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
UPDATE [Lead_Ext_attributes]
SET [ExtValue11600] = @LeadID
WHERE lead_id = @LeadID
FETCH NEXT FROM Cur INTO @LeadID
END
CLOSE cur
DEALLOCATE cur
END
END
GO
CREATE TRIGGER [dbo].[AGS_ti_LeadEAStatusChange] ON [dbo].[lead_ext_attributes]
FOR INSERT
AS
BEGIN
DECLARE @next_ch_id INT
DECLARE @current_date DATETIME
DECLARE @insertCount INT
SET @current_date = GETUTCDATE()
INSERT INTO cont_hist_t301
(
history_record_date
, abstract
, audience_member_id
, fld_3305
, fld_3308
, fld_3310
)
SELECT @current_date
, 'Added by Lead Status Change : I'
, l.audience_member_id
, inserted.lead_id
, 'Status'
, ISNULL(inserted.ExtValue11543, '')
FROM inserted
INNER JOIN leads l
ON l.lead_id = inserted.lead_id
END
GO
CREATE TRIGGER [dbo].[AGS_tu_LeadEAStatusChange] ON [dbo].[lead_ext_attributes]
FOR UPDATE
AS
BEGIN
DECLARE @next_ch_id INT
DECLARE @current_date DATETIME
DECLARE @UpdateCount INT
SET @current_date = GETUTCDATE()
INSERT INTO cont_hist_t301
(
history_record_date
, abstract
, audience_member_id
, fld_3305
, fld_3308
, fld_3310
)
SELECT @current_date
, 'Added by Lead Status Change : U'
, l.audience_member_id
, inserted.lead_id
, 'Status'
, ISNULL(inserted.ExtValue11543, '')
FROM inserted
INNER JOIN deleted
ON inserted.lead_id = deleted.lead_id
INNER JOIN leads l
ON l.lead_id = inserted.lead_id
WHERE ISNULL(inserted.ExtValue11543, '') <> ISNULL(deleted.ExtValue11543,
'')
END
GO
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
July 17, 2014 at 9:11 am
What object is object_id 1913773875? If its not the table you have already detailed can you provide the same information for that table.
July 17, 2014 at 9:13 am
It is the table I detailed.
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply