HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Encounter deadlock even when there is no relation between tables

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
deadlocksqlsql-serverstackoverflowlockingdba
tablesdeadlockbetweenwhenencountereventhererelation

Problem

I've been studying about deadlock and it's different causes .
I found an example regarding this issue but I could not understand the cause of the deadlock exactly because two DIFFERENT tables are involved and what I have read so far was when only one table is involved !!

First I run This Query in Connection 1 :

Begin Tran

 Update  Purchasing.PurchaseOrderHeader
 Set Freight = Freight * 0.9
 Where PurchaseOrderID = 1255;


Again I run this query in connection 2 :

update Purchasing.PurchaseOrderDetail
Set OrderQty = 4
Where ProductID = 448 and
      PurchaseOrderID = 1255


and back in connection 1 I run this query (which leads to the deadlock):

Begin Transaction

             Update Purchasing.PurchaseOrderDetail
             Set OrderQty = 2
             where ProductID = 448 and
                   PurchaseOrderID = 1255


As you can see , there are two tables involved in the transaction , So what exactly causes the deadlock??
Thanks in advance

Solution

I could not understand the cause of the deadlock exactly because two
DIFFERENT tables are involved and what I have read so far was when
only one table is involved !!

Deadlocks occur when 2 or more sessions are waiting on each other. It doesn't matter if the deadlocked resources are rows in the same table, different tables, entire objects, etc.

I ran your scripts to reproduce the deadlock, extracted the xml_deadlock_report column from the system_health XE trace, and saved the deadlock report XML to a file with an .xdl extension. Below is the graphical view from SSMS.

As you can see, the SPID 58 victim was waiting on a Purchasing.PurchaseOrderDetail update keylock, blocked by the SPID 53 exclusive keylock on the same key. SPID 53 was waiting on an update keylock on Purchasing.PurchaseOrderHeader, blocked by the exclusive lock SPID 58 held on the same key.

Below is the raw xml_deadlock_report XML.


  
    
  
  
    
      
          unknown    
          unknown    
      
                     Update Purchasing.PurchaseOrderDetail               Set OrderQty = 2               where ProductID = 448 and                     PurchaseOrderID = 1255   
    
    
      
          UPDATE [Purchasing].[PurchaseOrderHeader]              SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =                   (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])                      FROM [Purchasing].[PurchaseOrderDetail]                      WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]                           = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])              WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]                   IN (SELECT inserted.[PurchaseOrderID] FROM inserted    
          unknown    
          unknown    
      
        Begin Tran    update Purchasing.PurchaseOrderDetail  Set OrderQty = 4  Where ProductID = 448 and        PurchaseOrderID = 1255


The sample AdventureWorks database includes an UPDATE trigger on Purchasing.PurchaseOrderDetail table that contributed to this deadlock. The trigger inserts into Production.TransactionHistory and also updates both Purchasing.PurchaseOrderHeader and Purchasing.PurchaseOrderDetail whenever ProductID, OrderQty, or UnitPrice columns are updated.

In summary, the sequence leading to this deadlock was:

1) connection 1 updated a Purchasing.PurchaseOrderHeader but did not commit

2) connection 2 updated the Purchasing.PurchaseDetail table and the fired trigger was blocked when trying to update Purchasing.PurchaseOrderHeader

3) connection 1 tried to update Purchasing.PurchaseDetail but was blocked by connection

4) SQL Server chose connection 1 as the deadlock victim, allowing connection 2 to autocommit

Code Snippets

<deadlock>
  <victim-list>
    <victimProcess id="process1a668c9eca8"/>
  </victim-list>
  <process-list>
    <process id="process1a668c9eca8" taskpriority="0" logused="400" waitresource="KEY: 6:72057594048348160 (4ab5f0d47ad5)" waittime="1316" ownerId="2500187" transactionname="user_transaction" lasttranstarted="2018-07-24T06:02:13.303" XDES="0x1a85b00c490" lockMode="U" schedulerid="3" kpid="2932" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-24T06:02:31.437" lastbatchcompleted="2018-07-24T06:02:31.433" lastattention="2018-07-24T06:00:22.400" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLSERVER" hostpid="15904" loginname="DOMAINNAME\USERNAME" isolationlevel="read committed (2)" xactid="2500187" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="64" stmtend="278" sqlhandle="0x02000000d0c7f31a30fb1ad425c34357fe8ef6326793e7aa0000000000000000000000000000000000000000">  unknown    </frame>
        <frame procname="adhoc" line="1" stmtstart="26" stmtend="326" sqlhandle="0x02000000b66e4532b6686980baf466a9597258f2464dd4f50000000000000000000000000000000000000000">  unknown    </frame>
      </executionStack>
      <inputbuf>               Update Purchasing.PurchaseOrderDetail               Set OrderQty = 2               where ProductID = 448 and                     PurchaseOrderID = 1255   </inputbuf>
    </process>
    <process id="process1a66883d848" taskpriority="0" logused="9484" waitresource="KEY: 6:72057594048413696 (4bc08edebc6b)" waittime="16176" ownerId="2500193" transactionname="user_transaction" lasttranstarted="2018-07-24T06:02:16.577" XDES="0x1a85e004490" lockMode="U" schedulerid="5" kpid="20416" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-24T06:02:16.577" lastbatchcompleted="2018-07-24T06:02:16.570" lastattention="2018-07-24T06:01:28.820" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLSERVER" hostpid="15904" loginname="DOMAINNAME\USERNAME" isolationlevel="read committed (2)" xactid="2500193" currentdb="6" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390200">
      <executionStack>
        <frame procname="AdventureWorks2014.Purchasing.uPurchaseOrderDetail" line="39" stmtstart="2732" stmtend="3830" sqlhandle="0x0300060004cc856a0ee00a016ba3000000000000000000000000000000000000000000000000000000000000">  UPDATE [Purchasing].[PurchaseOrderHeader]              SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =                   (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])                      FROM [Purchasing].[PurchaseOrderDetail]                      WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]                           = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])              WHERE [Purchasing].[PurchaseOrderHeade

Context

StackExchange Database Administrators Q#212899, answer score: 5

Revisions (0)

No revisions yet.