SQLServerCentral.com / SQL Server 2012 / SQL 2012 - General / Deadlocks in SQL 2012 / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 15:39:26 GMT20RE: Deadlocks in SQL 2012http://www.sqlservercentral.com/Forums/Topic1437350-2799-1.aspxThis 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.Tue, 02 Apr 2013 08:13:29 GMTdsbolanosRE: Deadlocks in SQL 2012http://www.sqlservercentral.com/Forums/Topic1437350-2799-1.aspx[b]Since your query doesn't return any columns of table mcpITEMTASKS, I would certainly remove the update lock ![/b]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 ).Tue, 02 Apr 2013 00:06:12 GMTALZDBARE: Deadlocks in SQL 2012http://www.sqlservercentral.com/Forums/Topic1437350-2799-1.aspxThat 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.ThanksMon, 01 Apr 2013 09:35:50 GMTdsbolanosRE: Deadlocks in SQL 2012http://www.sqlservercentral.com/Forums/Topic1437350-2799-1.aspx1) 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.Mon, 01 Apr 2013 08:57:09 GMTTheSQLGuruRE: Deadlocks in SQL 2012http://www.sqlservercentral.com/Forums/Topic1437350-2799-1.aspxThanks ALZAttached 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,Mon, 01 Apr 2013 08:36:56 GMTdsbolanosRE: Deadlocks in SQL 2012http://www.sqlservercentral.com/Forums/Topic1437350-2799-1.aspxplease 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. )Mon, 01 Apr 2013 02:06:30 GMTALZDBADeadlocks in SQL 2012http://www.sqlservercentral.com/Forums/Topic1437350-2799-1.aspxHello 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:&lt;deadlock-list&gt; &lt;deadlock victim="process44c8d6928"&gt; &lt;process-list&gt; &lt;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"&gt; &lt;executionStack&gt; &lt;frame procname="OBC.dbo.mcp_GETITEM_" line="34" stmtstart="1376" stmtend="2128" sqlhandle="0x030005007c2b7c1d9776fe0087a1000001000000000000000000000000000000000000000000000000000000"&gt;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 &lt;/frame&gt; &lt;frame procname="OBC.dbo.mcp_GETET_" line="85" stmtstart="5452" stmtend="5702" sqlhandle="0x0300050060bc4c213945c6007ba1000001000000000000000000000000000000000000000000000000000000"&gt;EXEC @RES=mcp_GETITEM_ @TAS_ID, @ITE_ID OUT, @APP_NAME OUT, @FLG_NAME OUT,@ITE_NAME OUT, @ITE_DTPROCESS OUT,@ERROR OUT &lt;/frame&gt; &lt;frame procname="OBC.dbo.mcp_GET" line="28" stmtstart="1374" stmtend="1780" sqlhandle="0x0300050028deed286176a6007aa1000001000000000000000000000000000000000000000000000000000000"&gt;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 &lt;/frame&gt; &lt;/executionStack&gt; &lt;inputbuf&gt;Proc [Database Id = 5 Object Id = 686677544] &lt;/inputbuf&gt; &lt;/process&gt; &lt;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"&gt; &lt;executionStack&gt; &lt;frame procname="OBC.dbo.mcp_GETITEM_" line="43" stmtstart="2268" stmtend="2896" sqlhandle="0x030005007c2b7c1d9776fe0087a1000001000000000000000000000000000000000000000000000000000000"&gt;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 &lt;/frame&gt; &lt;frame procname="OBC.dbo.mcp_GETET_" line="85" stmtstart="5452" stmtend="5702" sqlhandle="0x0300050060bc4c213945c6007ba1000001000000000000000000000000000000000000000000000000000000"&gt;EXEC @RES=mcp_GETITEM_ @TAS_ID, @ITE_ID OUT, @APP_NAME OUT, @FLG_NAME OUT,@ITE_NAME OUT, @ITE_DTPROCESS OUT,@ERROR OUT &lt;/frame&gt; &lt;frame procname="OBC.dbo.mcp_GET" line="28" stmtstart="1374" stmtend="1780" sqlhandle="0x0300050028deed286176a6007aa1000001000000000000000000000000000000000000000000000000000000"&gt;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 &lt;/frame&gt; &lt;/executionStack&gt; &lt;inputbuf&gt;Proc [Database Id = 5 Object Id = 686677544] &lt;/inputbuf&gt; &lt;/process&gt; &lt;/process-list&gt; &lt;resource-list&gt; &lt;keylock hobtid="72057595823259648" dbid="5" objectname="OBC.dbo.mcpITEMTASKS" indexname="2" id="lock49f996980" mode="U" associatedObjectId="72057595823259648"&gt; &lt;owner-list&gt; &lt;owner id="process44c8e3498" mode="U"/&gt; &lt;/owner-list&gt; &lt;waiter-list&gt; &lt;waiter id="process44c8d6928" mode="U" requestType="wait"/&gt; &lt;/waiter-list&gt; &lt;/keylock&gt; &lt;keylock hobtid="72057595823259648" dbid="5" objectname="OBC.dbo.mcpITEMTASKS" indexname="2" id="lock4e2f30480" mode="U" associatedObjectId="72057595823259648"&gt; &lt;owner-list&gt; &lt;owner id="process44c8d6928" mode="U"/&gt; &lt;/owner-list&gt; &lt;waiter-list&gt; &lt;waiter id="process44c8e3498" mode="U" requestType="wait"/&gt; &lt;/waiter-list&gt; &lt;/keylock&gt; &lt;/resource-list&gt; &lt;/deadlock&gt;&lt;/deadlock-list&gt;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.Sun, 31 Mar 2013 21:32:42 GMTdsbolanos