Deadlock issue

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 😉

  • 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

  • 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

  • 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