Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlocks in SQL 2012 Expand / Collapse
Author
Message
Posted Sunday, March 31, 2013 9:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 27, Visits: 263
Hello guys, good day

I need your experts advice, I'm having a weird situation with a deadlock recurring issue, where there's a deadlock between two processes only that are actually blocking and waiting for the same resource, an index.

I created a trace in profiler to track them out and this is the XML output of it:



<deadlock-list>
<deadlock victim="process44c8d6928">
<process-list>
<process id="process44c8d6928" taskpriority="0" logused="0" waitresource="KEY: 5:72057595823259648 [/highlight](fa4d64ad8d28)" waittime="2972" ownerId="265573708" transactionname="__GET" lasttranstarted="2013-03-30T09:23:42.640" XDES="0x44c8fe3a8" lockMode="U" schedulerid="6" kpid="7080" status="suspended" spid="93" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-03-30T09:23:42.640" lastbatchcompleted="2013-03-30T09:23:42.640" lastattention="1900-01-01T00:00:00.640" clientapp=".Net SqlClient Data Provider" hostname="OBCYRK1VPRD02" hostpid="4192" loginname="ACS_AETNA\obcprod" isolationlevel="read committed (2)" xactid="265573708" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="OBC.dbo.mcp_GETITEM_" line="34" stmtstart="1376" stmtend="2128" sqlhandle="0x030005007c2b7c1d9776fe0087a1000001000000000000000000000000000000000000000000000000000000">
SELECT TOP 1 @ITE_NAME1 = I.ITE_NAME, @ITE_ID1 = I.ITE_ID, @ITE_DTPROCESS1=I.ITE_DTPROCESS,
@APP_NAME1=I.APP_NAME, @FLG_NAME1=I.FLG_NAME
FROM mcpITEMS I
join mcpITEMTASKS T WITH (UPDLOCK) on T.ITE_ID = I.ITE_ID AND T.TAS_ID = @TAS_ID1 AND T.ITS_STATUS = 0
WHERE I.APP_NAME = @APP_NAME1 AND I.FLG_NAME = @FLG_NAME1
ORDER BY I.ITE_APRIORITY </frame>
<frame procname="OBC.dbo.mcp_GETET_" line="85" stmtstart="5452" stmtend="5702" sqlhandle="0x0300050060bc4c213945c6007ba1000001000000000000000000000000000000000000000000000000000000">
EXEC @RES=mcp_GETITEM_ @TAS_ID, @ITE_ID OUT, @APP_NAME OUT, @FLG_NAME OUT,@ITE_NAME OUT, @ITE_DTPROCESS OUT,@ERROR OUT </frame>
<frame procname="OBC.dbo.mcp_GET" line="28" stmtstart="1374" stmtend="1780" sqlhandle="0x0300050028deed286176a6007aa1000001000000000000000000000000000000000000000000000000000000">
EXEC @RES=mcp_GETET_ @ITT_NAME, @TAS_ID, @USE_LOGIN, @ITS_MACHINE, @ITS_INSTANCE,
@ITE_NAME OUT,@APP_NAME OUT, @FLG_NAME OUT, @ITE_DTPROCESS OUT, @ERROR OUT </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 686677544] </inputbuf>
</process>
<process id="process44c8e3498" taskpriority="0" logused="0" waitresource="KEY: 5:72057595823259648 (7b2aa6cb912d)" waittime="2972" ownerId="265573667" transactionname="__GET" lasttranstarted="2013-03-30T09:23:42.480" XDES="0x4e4542eb8" lockMode="U" schedulerid="5" kpid="4000" status="suspended" spid="128" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-03-30T09:23:42.480" lastbatchcompleted="2013-03-30T09:23:09.720" lastattention="1900-01-01T00:00:00.720" clientapp=".Net SqlClient Data Provider" hostname="OBCYRK1VPRD03" hostpid="352" loginname="ACS_AETNA\obcprod" isolationlevel="read committed (2)" xactid="265573667" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="OBC.dbo.mcp_GETITEM_" line="43" stmtstart="2268" stmtend="2896" sqlhandle="0x030005007c2b7c1d9776fe0087a1000001000000000000000000000000000000000000000000000000000000">
SELECT TOP 1 @ITE_NAME1 = I.ITE_NAME, @ITE_ID1 = I.ITE_ID, @ITE_DTPROCESS1=I.ITE_DTPROCESS,
@APP_NAME1=I.APP_NAME, @FLG_NAME1=I.FLG_NAME
FROM mcpITEMS I
join mcpITEMTASKS T WITH (UPDLOCK) ON T.ITE_ID = I.ITE_ID AND T.TAS_ID = @TAS_ID1 AND T.ITS_STATUS = 0
ORDER BY I.ITE_APRIORITY </frame>
<frame procname="OBC.dbo.mcp_GETET_" line="85" stmtstart="5452" stmtend="5702" sqlhandle="0x0300050060bc4c213945c6007ba1000001000000000000000000000000000000000000000000000000000000">
EXEC @RES=mcp_GETITEM_ @TAS_ID, @ITE_ID OUT, @APP_NAME OUT, @FLG_NAME OUT,@ITE_NAME OUT, @ITE_DTPROCESS OUT,@ERROR OUT </frame>
<frame procname="OBC.dbo.mcp_GET" line="28" stmtstart="1374" stmtend="1780" sqlhandle="0x0300050028deed286176a6007aa1000001000000000000000000000000000000000000000000000000000000">
EXEC @RES=mcp_GETET_ @ITT_NAME, @TAS_ID, @USE_LOGIN, @ITS_MACHINE, @ITS_INSTANCE,
@ITE_NAME OUT,@APP_NAME OUT, @FLG_NAME OUT, @ITE_DTPROCESS OUT, @ERROR OUT </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 686677544] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057595823259648" dbid="5" objectname="OBC.dbo.mcpITEMTASKS" indexname="2" id="lock49f996980" mode="U" associatedObjectId="72057595823259648">
<owner-list>
<owner id="process44c8e3498" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process44c8d6928" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057595823259648" dbid="5" objectname="OBC.dbo.mcpITEMTASKS" indexname="2" id="lock4e2f30480" mode="U" associatedObjectId="72057595823259648">
<owner-list>
<owner id="process44c8d6928" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process44c8e3498" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>

Both processes execute the same stored procedure, which executes (depending on the parameters) similar queries that read data from the table mcpItemTasks, as you may see the queries are very similar but one has where clause for parameters.

Where I'm kind of lost is that both have the lock mode on "U" because of the updlock hint, but also own it and thus they create a deadlock when they request it... but not sure why they request if they already own it...

The indexes are set to allow page and row locks (which is default right?) and the table to lock escalation as "TABLE" (but from sys.tables you can read "lock_escalation" = 0 and "lock_escalation_desc"= TABLE, which I believe is default too)

All the help you can give me to help understand this deadlock will be very appreciated.
Post #1437350
Posted Monday, April 1, 2013 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 6,748, Visits: 8,546
please provide the query plan(s).

My guess is the index is being scanned, so the updlock may indeed block it all.


p.s. are you sure this is a SQL2012 instance ? The deadlock xml isn't formatted in the latest SQL2008/SQL2012 format.
If it is an RTM version, you may want to apply SP1 ( and maybe even the most recent CU. )


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1437375
Posted Monday, April 1, 2013 8:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 27, Visits: 263
Thanks ALZ

Attached is the exec plan, as you said there is in fact an index scan, but on mcpitems, not on mcpitemtasks where the updlock is and the deadlocks occur, anyway, could this be it?

And yes, this is SQL 2012 with SP1, but the database where this query runs is in compatibility 90, probably this affects the deadlock format?

Thanks,


  Post Attachments 
execplan.png (3 views, 23.77 KB)
Post #1437465
Posted Monday, April 1, 2013 8:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 4,474, Visits: 6,408
1) a picture of the query plan is NOT the same thing as the ACTUAL query plan. Please post that. Also, since it is failing probably need to include the Estimated query plan too.

2) Can you script out the tables involved with all their indexes, and also provide the entire code involved?

3) In the mean time, check to make sure that all of your datatypes involved in joins and where clauses are EXACTLY the same datatypes as the columns they are touching.



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1437473
Posted Monday, April 1, 2013 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 27, Visits: 263
That was the picture of the actual execution plan, but I'm attaching now one from the new actual and estimate execution plan (as I created the recommended index from the mcpItems table)

Attached is the .sql file with the script of the tables, indexes and the SP where the deadlock occurs.

Thanks


  Post Attachments 
Scripts and code.txt (2 views, 7.83 KB)
Actual exec plan.sqlplan (3 views, 30.28 KB)
estimate exec plan.sqlplan (2 views, 29.87 KB)
Post #1437502
Posted Tuesday, April 2, 2013 12:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 6,748, Visits: 8,546
Since your query doesn't return any columns of table mcpITEMTASKS, I would certainly remove the update lock !

hence ... all your deadlock problems for this query case will vanish !


The lock is on objectname="OBC.dbo.mcpITEMTASKS" indexname="2" which is indeed the object you only use for filtering purposes and do not return any data from.


btw estimates for mcpitems are way off ( 63884 vs 450258 ) . it may come up with a better provisioned plan if you actually use the sproc paramters in your query predicates ( parameter sniffing ).


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1437712
Posted Tuesday, April 2, 2013 8:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 27, Visits: 263
This SP is inside another SP which then call another SP that updates mcpITEMTASKS, because of that I believe they use the UPDLOCK to update the rows later, have to confirm that though.

We added the dummy variables to the parameters because this SP was causing high CPU contention, the dummy variables fixed that performance issue we had.
Post #1437913
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse