Monday, April 1, 2013

[SQL 2012] Deadlocks in SQL 2012

[SQL 2012] Deadlocks in SQL 2012

Deadlocks in SQL 2012

Posted: 31 Mar 2013 02:32 PM PDT

Hello guys, good dayI 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.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog