January 27, 2015 at 8:44 pm
Hi,
I'm having a deadlock issue that has me a little stumped.
Here is the XML:
<deadlock>
<victim-list>
<victimProcess id="process83f410558" />
<victimProcess id="process85aa1c558" />
<victimProcess id="process432529498" />
</victim-list>
<process-list>
<process id="process83f410558" taskpriority="0" logused="0" waitresource="KEY: 11:72057595935064064 (4889e3c4ef44)" waittime="3700" ownerId="1345291615" transactionname="user_transaction" lasttranstarted="2015-01-28T11:28:26.380" XDES="0x8f53176a8" lockMode="RangeS-S" schedulerid="3" kpid="6744" status="suspended" spid="302" sbid="4" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-01-28T11:28:28.960" lastbatchcompleted="2015-01-28T11:28:28.957" lastattention="1900-01-01T00:00:00.957" clientapp=".Net SqlClient Data Provider" hostname="DC1PRD207" hostpid="3784" loginname="TWREXTERNAL\sap1prodappool" isolationlevel="serializable (4)" xactid="1345291615" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x020000001e28c805a11d25bb935d0e14c6923bc1587894820000000000000000000000000000000000000000">
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 uniqueidentifier)SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </inputbuf>
</process>
<process id="process85aa1c558" taskpriority="0" logused="0" waitresource="KEY: 11:72057595935064064 (4889e3c4ef44)" waittime="3019" ownerId="1345291915" transactionname="user_transaction" lasttranstarted="2015-01-28T11:28:26.740" XDES="0x58aea23a8" lockMode="RangeS-S" schedulerid="1" kpid="4452" status="suspended" spid="214" sbid="4" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-01-28T11:28:29.643" lastbatchcompleted="2015-01-28T11:28:29.637" lastattention="1900-01-01T00:00:00.637" clientapp=".Net SqlClient Data Provider" hostname="DC1PRD207" hostpid="3784" loginname="TWREXTERNAL\sap1prodappool" isolationlevel="serializable (4)" xactid="1345291915" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x020000001e28c805a11d25bb935d0e14c6923bc1587894820000000000000000000000000000000000000000">
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 uniqueidentifier)SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </inputbuf>
</process>
<process id="process432529498" taskpriority="0" logused="0" waitresource="KEY: 11:72057595935064064 (4889e3c4ef44)" waittime="223" ownerId="1345294201" transactionname="user_transaction" lasttranstarted="2015-01-28T11:28:29.813" XDES="0x8f5550d28" lockMode="RangeS-S" schedulerid="3" kpid="6364" status="suspended" spid="73" sbid="4" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-01-28T11:28:32.437" lastbatchcompleted="2015-01-28T11:28:32.430" lastattention="1900-01-01T00:00:00.430" clientapp=".Net SqlClient Data Provider" hostname="DC1PRD205" hostpid="6288" loginname="TWREXTERNAL\sap1prodappool" isolationlevel="serializable (4)" xactid="1345294201" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x020000001e28c805a11d25bb935d0e14c6923bc1587894820000000000000000000000000000000000000000">
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 uniqueidentifier)SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </inputbuf>
</process>
<process id="process7da25ecf8" taskpriority="0" logused="0" waitresource="KEY: 11:72057595935064064 (4889e3c4ef44)" waittime="4090" ownerId="1345293322" transactionname="SELECT" lasttranstarted="2015-01-28T11:28:28.530" XDES="0x384b03ca0" lockMode="RangeS-S" schedulerid="1" kpid="3084" status="suspended" spid="268" sbid="4" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-01-28T11:28:28.530" lastbatchcompleted="2015-01-28T11:28:28.523" lastattention="1900-01-01T00:00:00.523" clientapp=".Net SqlClient Data Provider" hostname="DC1PRD206" hostpid="1516" loginname="TWREXTERNAL\sap1prodappool" isolationlevel="serializable (4)" xactid="1345293322" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x020000001e28c805a11d25bb935d0e14c6923bc1587894820000000000000000000000000000000000000000">
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 uniqueidentifier)SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1] </inputbuf>
</process>
<process id="process37cf67868" taskpriority="0" logused="14572" waitresource="OBJECT: 11:559393112:0 " waittime="4002" ownerId="1345292385" transactionname="user_transaction" lasttranstarted="2015-01-28T11:28:27.827" XDES="0x2eba84538" lockMode="IX" schedulerid="1" kpid="1828" status="suspended" spid="216" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-01-28T11:28:28.727" lastbatchcompleted="2015-01-28T11:28:28.723" lastattention="1900-01-01T00:00:00.723" clientapp=".Net SqlClient Data Provider" hostname="DC1PRD205" hostpid="6288" loginname="TWREXTERNAL\sap1prodappool" isolationlevel="read committed (2)" xactid="1345292385" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="166" stmtend="652" sqlhandle="0x02000000e8226300d2f282371175ab58944f030f8a14415a0000000000000000000000000000000000000000">
update [dbo].[tblApplPoliciesCommencement]
set [PolicyCommencementTypeCode] = @0, [PreferredRiskCommencementDate] = null, [PoliciesCommencementSelectionTS] = @1, [ModifiedBy] = @2, [ModifiedTS] = @3
where ([ApplPoliciesCommencementID] = @4) </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 varchar(12),@1 datetime2(7),@2 varchar(64),@3 datetime2(7),@4 uniqueidentifier)update [dbo].[tblApplPoliciesCommencement]
set [PolicyCommencementTypeCode] = @0, [PreferredRiskCommencementDate] = null, [PoliciesCommencementSelectionTS] = @1, [ModifiedBy] = @2, [ModifiedTS] = @3
where ([ApplPoliciesCommencementID] = @4)
select [ApplPolicyCommencementRowVersion]
from [dbo].[tblApplPoliciesCommencement]
where @@ROWCOUNT > 0 and [ApplPoliciesCommencementID] = @4 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057595935064064" dbid="11" objectname="CaseMgmt.dbo.tblApplPlan" indexname="IXU1_ApplPlan" id="lock3c238c300" mode="X" associatedObjectId="72057595935064064">
<owner-list>
<owner id="process7da25ecf8" mode="RangeS-S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process83f410558" mode="RangeS-S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057595935064064" dbid="11" objectname="CaseMgmt.dbo.tblApplPlan" indexname="IXU1_ApplPlan" id="lock3c238c300" mode="X" associatedObjectId="72057595935064064">
<owner-list>
<owner id="process7da25ecf8" mode="RangeS-S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process85aa1c558" mode="RangeS-S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057595935064064" dbid="11" objectname="CaseMgmt.dbo.tblApplPlan" indexname="IXU1_ApplPlan" id="lock3c238c300" mode="X" associatedObjectId="72057595935064064">
<owner-list>
<owner id="process7da25ecf8" mode="RangeS-S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process432529498" mode="RangeS-S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057595935064064" dbid="11" objectname="CaseMgmt.dbo.tblApplPlan" indexname="IXU1_ApplPlan" id="lock3c238c300" mode="X" associatedObjectId="72057595935064064">
<owner-list>
<owner id="process37cf67868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process7da25ecf8" mode="RangeS-S" requestType="wait" />
</waiter-list>
</keylock>
<objectlock lockPartition="0" objid="559393112" subresource="FULL" dbid="11" objectname="CaseMgmt.dbo.tblApplPoliciesCommencement" id="lock65396b500" mode="S" associatedObjectId="559393112">
<owner-list>
<owner id="process85aa1c558" mode="S" />
<owner id="process432529498" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process37cf67868" mode="IX" requestType="wait" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
I'd be interested on what people think could be going on here?
Any suggestions is much appreciated.
January 28, 2015 at 2:20 am
Any reason why all but one of those connections are running in the Serializable isolation level? Is that intended? Do you need the isolation that serializable provides?
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
January 28, 2015 at 2:26 am
Hi Gail,
To be honest I'm not sure. I'd have to check with the developers regarding that as to whether there is a specific reason.
I was just a bit confused because this deadlock involves the two separate tables. I also found three victims being listed as a little bit strange.
Thank you for taking the time to look at this. I went straight to your deadlock article to help investigate this but couldn't really fathom what was going on here.
January 28, 2015 at 2:49 am
Chris-475469 (1/28/2015)
To be honest I'm not sure. I'd have to check with the developers regarding that as to whether there is a specific reason.
I'd bet you lunch that they won't have a clue.
Serialisable is a default for .Net in some cases, they probably aren't even aware what's happening
I was just a bit confused because this deadlock involves the two separate tables. I also found three victims being listed as a little bit strange.
Thank you for taking the time to look at this. I went straight to your deadlock article to help investigate this but couldn't really fathom what was going on here.
I didn't look through it in detail (coffee break's over).
Grab some paper and see if you can draw out the lock chain, it should give you more insight as to what's happening.
There's quite a few processes involved, so three victims isn't unheard of, it's a pretty complex locking chain.
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
January 28, 2015 at 2:59 am
I totally agree on your first comment!
I'll look into this again when I'm in the office tomorrow morning and suggest what's I think is happening.
Then you can tell me I'm way off 😉
January 28, 2015 at 7:11 am
That's Entity Framework generated TSQL - your developers will REALLY have no clue what's going on!! :w00t:
Serializable and it's range locking is likely at least part of the issue. GUIDs probably aren't helping either.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2015 at 7:42 am
Chances are the LinQ query :
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[tblApplPlan] AS [Extent1]
WHERE [Extent1].[SuperLinkIPPlanID] = @p__linq__0
) AS [GroupBy1]
Is only used to check existance, counting all occurences, in stead of using an ' if exists ' construct, which stops scanning after the first occurence
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 29, 2015 at 1:58 am
Spoke to the devs and they are going to look at changing the isolation level.
We appear to have eliminated the deadlocks for now by adding some additional indexes.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply