Deadlock Question

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  December 17, 2010 at 7:29 am

  #228975

  Hi all

  I am currently experiencing a deadlock foe which I have trapped the graph. How can I get the variables that were being executed at the time so that I can try to investigate the issue?

  Thanks

• WayneS

  SSC Guru

  Points: 95461

  More actions

  December 17, 2010 at 8:00 am

  #1264074

  If you post your deadlock graph, we can probably help you figure it out.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  December 17, 2010 at 8:04 am

  #1264079

  Ok cool - please see below 🙂

  <deadlock-list>

  <deadlock victim="processee711c18">

  <process-list>

  <process id="processff0ef8" taskpriority="0" logused="1580" waitresource="KEY: 38:72057594054508544 (1404d11120e3)" waittime="3171" ownerId="5176117985" transactionname="user_transaction" lasttranstarted="2010-12-17T09:07:01.937" XDES="0xeaf66370" lockMode="X" schedulerid="4" kpid="8808" status="suspended" spid="337" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-12-17T09:07:01.937" lastbatchcompleted="2010-12-17T09:07:01.937" clientapp=".Net SqlClient Data Provider" hostname="WEB" hostpid="3880" loginname="ANON" isolationlevel="read committed (2)" xactid="5176117985" currentdb="38" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

  <executionStack>

  <frame procname="DB.dbo.proc_UpdateListItemWorkflowInstanceData" line="166" stmtstart="14466" stmtend="14848" sqlhandle="0x030026001465fe6776359e00bb9b00000100000000000000">

  UPDATE

  dbo.WorkflowAssociation

  SET

  InstanceCount = InstanceCount - 1

  WHERE

  SiteId = @SiteId AND

  Id = @TemplateId </frame>

  </executionStack>

  <inputbuf>

  Proc [Database Id = 38 Object Id = 1744725268] </inputbuf>

  </process>

  <process id="processee711c18" taskpriority="0" logused="0" waitresource="KEY: 38:72057594070695936 (e30741680542)" waittime="3171" ownerId="5176117970" transactionname="user_transaction" lasttranstarted="2010-12-17T09:07:01.937" XDES="0x1ea1677e0" lockMode="S" schedulerid="2" kpid="6092" status="suspended" spid="621" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2010-12-17T09:07:01.937" lastbatchcompleted="2010-12-17T09:07:01.937" clientapp="Internet Information Services" hostname="WEB" hostpid="3880" loginname="ANON" isolationlevel="read committed (2)" xactid="5176117970" currentdb="38" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

  <executionStack>

  <frame procname="DB.dbo.proc_AddWorkflow" line="71" stmtstart="4790" stmtend="5836" sqlhandle="0x03002600f7af396368359e00bb9b00000100000000000000">

  IF (@Configuration & (128 | 1024 | 512)) = 0 AND

  ((@Configuration & 256) <> 0 OR

  NOT EXISTS (SELECT

  *

  FROM

  dbo.Workflow

  WHERE

  SiteId = @SiteId AND

  ListId = @ListId AND

  ItemId = @ItemId AND

  TemplateId = @WorkflowTemplateId AND

  (InternalState & (4 | 8)) = 0)) </frame>

  </executionStack>

  <inputbuf>

  Proc [Database Id = 38 Object Id = 1664724983] </inputbuf>

  </process>

  </process-list>

  <resource-list>

  <keylock hobtid="72057594070695936" dbid="38" objectname="DB.dbo.Workflow" indexname="Workflow_PK" id="lock188b46e00" mode="X" associatedObjectId="72057594070695936">

  <owner-list>

  <owner id="processff0ef8" mode="X"/>

  </owner-list>

  <waiter-list>

  <waiter id="processee711c18" mode="S" requestType="wait"/>

  </waiter-list>

  </keylock>

  <keylock hobtid="72057594054508544" dbid="38" objectname="DB.dbo.WorkflowAssociation" indexname="WorkflowAssociation_Id" id="lock2fe042100" mode="U" associatedObjectId="72057594054508544">

  <owner-list>

  <owner id="processee711c18" mode="U"/>

  </owner-list>

  <waiter-list>

  <waiter id="processff0ef8" mode="X" requestType="wait"/>

  </waiter-list>

  </keylock>

  </resource-list>

  </deadlock>

  </deadlock-list>

• WayneS

  SSC Guru

  Points: 95461

  More actions

  December 17, 2010 at 9:03 am

  #1264116

  The actual code being run is:

  (Deadlock victim)

  From procedure: DB.dbo.proc_AddWorkflow

  IF (@Configuration & (128 | 1024 | 512)) = 0 AND

  ((@Configuration & 256) <> 0 OR

  NOT EXISTS (SELECT

  *

  FROM

  dbo.Workflow

  WHERE

  SiteId = @SiteId AND

  ListId = @ListId AND

  ItemId = @ItemId AND

  TemplateId = @WorkflowTemplateId AND

  (InternalState & (4 | 8)) = 0))

  (Deadlock survivor)

  From procedure : DB.dbo.proc_UpdateListItemWorkflowInstanceData

  UPDATE

  dbo.WorkflowAssociation

  SET

  InstanceCount = InstanceCount - 1

  WHERE

  SiteId = @SiteId AND

  Id = @TemplateId

  However, if I read your question correctly, you're looking for the values of the variables for these queries. This information isn't available in the deadlock graph, and unless you had a trace running, I'm not sure how you would get this information. Even then, you would only be able to see what the parameters passed to the procedure are - if these variables are different than those, then you would have to walk through the procedures to figure out what the query variables are.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2