SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlocks in SQL 2012


Deadlocks in SQL 2012

Author
Message
dsbolanos
dsbolanos
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 332
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.
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12852 Visits: 8930
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
dsbolanos
dsbolanos
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 332
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,
Attachments
execplan.png (13 views, 23.00 KB)
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13285 Visits: 8566
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
dsbolanos
dsbolanos
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 332
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
Attachments
Scripts and code.txt (16 views, 7.00 KB)
Actual exec plan.sqlplan (8 views, 30.00 KB)
estimate exec plan.sqlplan (8 views, 29.00 KB)
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12852 Visits: 8930
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
dsbolanos
dsbolanos
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 332
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search