SQLException : Deadlock error when processing 2 different tables

Question

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.stack.imgur.com/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?

@Arvo: The relationship between them is only through a foreign key. — Nguyen Duc Tri
One of the contributors to the deadlock is the SELECT query in need of tuning, The shared lock suggests a full scan due to applying the LEFT function to the column. If you have an index on description, refactoring the query as SELECT * FROM items WHERE description LIKE LEFT(@description, 5); might limit the number of rows touched and an OPTION *RECOMPILE) query hint may help as well. Separately, a best practice is to avoid SELECT * and specify an explict column list instead. — Dan Guzman

Dale K Dale K · Accepted Answer · 2021-02-26T10:25:08

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.

SQLException : Deadlock error when processing 2 different tables

1 Answers