CODEWITHSUNDEEP
sql-serverdeadlock
Nguyen Duc Tri
Nguyen Duc Tri

Reputation: 3

SQLException : Deadlock error when processing 2 different tables

I have the deadlock error:

Transaction (Process ID 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Image deadlock (https://i.sstatic.net/rDyxP.png)

Sp 91 is store Katzkin.dbo.ItemsAirBags_DX_IdItem:

delete ItemsAirBags
from ItemsAirBags  
where IdItem = @IdItem

Sp291 is store Katzkin.dbo.Items_TX_Description:

select *
from items  
where description = @description or left(description,5) = @description

Full deadlock.xml that I downloaded from redgate:

 <deadlock>
  <victim-list>
    <victimProcess id="process366a0a5848" />
  </victim-list>
  <process-list>
    <process id="process366a0a5848" taskpriority="0" logused="6424" waitresource="KEY: 6:72057609466478592 (56dfc8bc1bd3)" waittime="134" ownerId="2620704196" transactionname="user_transaction" lasttranstarted="2021-01-25T10:48:53.610" XDES="0x4462dd4490" lockMode="U" schedulerid="11" kpid="14292" status="suspended" spid="91" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-01-25T10:48:53.690" lastbatchcompleted="2021-01-25T10:48:53.687" lastattention="2021-01-25T08:32:04.567" clientapp=".Net SqlClient Data Provider" hostname="SQLSERVER" hostpid="17172" loginname="KatCa" isolationlevel="read committed (2)" xactid="2620704196" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="Katzkin.dbo.ItemsAirBags_DX_IdItem" line="2" stmtstart="136" stmtend="258" sqlhandle="0x030006006b1f922228e7730050ab000001000000000000000000000000000000000000000000000000000000">
delete ItemsAirBags from ItemsAirBags  
where IdItem = @IdIte    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 6 Object Id = 580001643]   </inputbuf>
    </process>
    <process id="process3dc4191468" taskpriority="0" logused="34720" waitresource="KEY: 6:72057609931325440 (b015ce3d2676)" waittime="62" ownerId="2620704065" transactionname="user_transaction" lasttranstarted="2021-01-25T10:48:53.500" XDES="0x3d45478490" lockMode="S" schedulerid="6" kpid="1500" status="suspended" spid="291" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-01-25T10:48:53.760" lastbatchcompleted="2021-01-25T10:48:53.760" lastattention="2021-01-25T08:30:50.683" clientapp=".Net SqlClient Data Provider" hostname="SQLSERVER" hostpid="30552" loginname="KatCa" isolationlevel="read committed (2)" xactid="2620704065" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="Katzkin.dbo.Items_TX_Description" line="4" stmtstart="172" stmtend="348" sqlhandle="0x030006008513045381f88c002da3000001000000000000000000000000000000000000000000000000000000">
Select * from items  
where description=@description or left(description,5)=@descriptio    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 6 Object Id = 1392776069]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057609466478592" dbid="6" objectname="Katzkin.dbo.ItemsAirBags" indexname="PK_ItemsAirBags" id="lock3ca39db480" mode="X" associatedObjectId="72057609466478592">
      <owner-list>
        <owner id="process3dc4191468" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process366a0a5848" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057609931325440" dbid="6" objectname="Katzkin.dbo.Items" indexname="PK_Items" id="lock3f52da2100" mode="X" associatedObjectId="72057609931325440">
      <owner-list>
        <owner id="process366a0a5848" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process3dc4191468" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

We are on two different tables but caused a deadlock and I don't understand the reason and how to fix it. Any ideas on how these can be resolved?

Upvotes: 0

Views: 607

Answers (1)

Dale K
Dale K

Reputation: 27283

As the deadlock graph shows, you have 2 stored procedures, both of which are trying to update/access the same rows, but for some reason one is updating/accessing the ItemsAirBags table first and the Items table second and the other stored procedure in reverse order. This is what is causing the deadlock. If you make both stored procedure update/access both tables in the same order you'll remove the deadlock.

When you are dealing with a parent child situation its sometimes necessary to take an update lock on the child rows you are planning to update before you update the parent, and then the children.

You want to ensure you always update/access your tables in the same order because that way the first stored procedure will block the second stored procedure (which is what you want) rather than deadlock.

Note: there must be more happening within those stored procedure than you code shows, because as it stands there would be no deadlock. Somehow process process3dc4191468 has an exclusive lock on ItemsAirBags which is not part of the SQL shown.

Upvotes: 1

Related Questions