March 31, 2013 at 9:32 pm
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.
April 1, 2013 at 2:06 am
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
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
April 1, 2013 at 8:36 am
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,
April 1, 2013 at 8:57 am
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 on googles mail service
April 1, 2013 at 9:35 am
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
April 2, 2013 at 12:06 am
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
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
April 2, 2013 at 8:13 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply