DeadLock issue on primary key index

Question

I am reviewing my ASP.NET application and found that the deadlock issue occurs quite often (twice and more in a week). I am seeking for help to found out why deadlock happen and how to solve it.
Background: a warehouse application to deal with user requests.

Request_Table: stores request item and will be remove if processed.

Requestor_Division*	Requestor_Section*	Item_Division*	Item_Code*	Req_Qty	Status
Division A	Section 1	Division Z	Item A	1	Requested
Division A	Section 1	Division Z	Item B	1	Requested
Division A	Section 1	Division Z	Item C	1	Requested
Division A	Section 1	Division Z	Item D	1	Requested
Division B	Section 2	Division Z	Item A	1	Requested
Division B	Section 2	Division Z	Item B	1	Requested
Division B	Section 2	Division Z	Item C	1	Requested

(*) is the primary key column.

Note: one division may have one or many sections.

And here is the deadlock graph:

deadlock graph

<deadlock-list>
 <deadlock victim="process26ff73804e8">
  <process-list>
   <process id="process26ff73804e8" taskpriority="0" logused="0" waitresource="KEY: 14:72057594130399232 (93f4f9fceb1e)" waittime="4900" ownerId="7888090087" transactionname="SELECT" lasttranstarted="2023-08-14T11:07:33.263" XDES="0x2741db0f270" lockMode="RangeS-S" schedulerid="6" kpid="10376" status="suspended" spid="872" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2023-08-14T11:07:33.260" lastbatchcompleted="2023-08-14T11:07:33.257" lastattention="1900-01-01T00:00:00.257" clientapp="LAMSM4, XXXX" hostname="VM-XXXSVR123" hostpid="6048" loginname="XXX_DBUSER" isolationlevel="serializable (4)" xactid="7888090087" currentdb="14" currentdbname="XXXXX" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="XXXXX.dbo.usp_XXXXX_GET_XXXXX_Request_Order_List" line="22" stmtstart="920" stmtend="4948" sqlhandle="0x03000e00745a5a75ce99f8004ab0000001000000000000000000000000000000000000000000000000000000">
SELECT
    Item_Division = XXXX_Request_Table.Item_Division,
    Requestor_Division = XXXX_Request_Table.Requestor_Division,
    Requestor_Div_Name = Division.Div_EngName,
    Requestor_Section = XXXX_Request_Table.Requestor_Section,
    Requestor_Sec_Name = Section.Section_Name,
    Status = XXXX_Request_Table.Status,
    Status_Desc =   CASE XXXX_Request_Table.Status 
                        WHEN 0 THEN &apos;Cancelled&apos;
                        WHEN 1 THEN &apos;Pending&apos; 
                        WHEN 2 THEN &apos;Requested&apos; 
                        WHEN 3 THEN &apos;Acknowledged&apos; 
                        WHEN 4 THEN &apos;Processing&apos; 
                        WHEN 5 THEN &apos;Completed&apos; 
                        WHEN 6 THEN &apos;Back Order&apos; 
                    END,
    Is_Urgent = ISNULL(XXXX_Request_Table.Is_Urgent,0),
    Requested_DT = MIN(XXXX_Request_Table.Requested_DT),
    Processing_DT = MIN(XXXX_Request_Table.Processing_DT),
    Print_Slip_DT = MIN(XXXX_Request_Table.Print_Slip_DT),
    Picking_No = XXXX_Request_Table.Picking_No,
    No_Of_Item = COUNT(XXXX_Request_Table.Item_Code)
FROM
    XXXX_Request_Table
JOIN @tbl_Status a ON XXXX_Request_Table.Status = a.Status
JOIN Division ON XXXX_Request_Table.Request_Division = Division     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 14 Object Id = 1968855668]    </inputbuf>
   </process>
   <process id="process270db23b848" taskpriority="0" logused="210268" waitresource="KEY: 14:72057594130399232 (2dd0ed242191)" waittime="2471" ownerId="7888086305" transactionname="user_transaction" lasttranstarted="2023-08-14T11:07:32.370" XDES="0x273b7864bd0" lockMode="X" schedulerid="6" kpid="7668" status="suspended" spid="1599" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-08-14T11:07:35.687" lastbatchcompleted="2023-08-14T11:07:35.693" lastattention="1900-01-01T00:00:00.693" clientapp="FUNGPY, XXXXX" hostname="VM-XXXSVR123" hostpid="6048" loginname="XXX_DBUSER" isolationlevel="serializable (4)" xactid="7888086305" currentdb="14" currentdbname="XXXX" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="XXXXX.dbo.usp_XXXXX_UPD_XXXXX_Request" line="281" stmtstart="19768" stmtend="20494" sqlhandle="0x03000e00ad7e4e76f2a0f8004ab0000001000000000000000000000000000000000000000000000000000000">
UPDATE XXXX_Request_Table 
        SET
            Delivered_Qty = ISNULL(Delivered_Qty, 0) + ISNULL(@Delivered_Qty, 0),
            WS_Name = @WS_Name,
            User_Code = @User_Code,
            LastUpdate = GETDATE()
        WHERE   
            Requestor_Division = @Requestor_Division
        AND Requestor_Section = @Requestor_Section
        AND Item_Division =@Item_Division
        AND Item_Code = @Item_Cod     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 14 Object Id = 1984855725]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594130399232" dbid="14" objectname="XXXXX.dbo.XXXX_Request" indexname="PK_XXXXX_Request" id="lock273a1137f00" mode="X" associatedObjectId="72057594130399232">
    <owner-list>
     <owner id="process270db23b848" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process26ff73804e8" mode="RangeS-S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594130399232" dbid="14" objectname="XXXXX.dbo.XXXX_Request" indexname="PK_XXXX_Request" id="lock275582dab80" mode="RangeS-S" associatedObjectId="72057594130399232">
    <owner-list>
     <owner id="process26ff73804e8" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process270db23b848" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

After studying the log, here are my findings:

Deadlock victim process (getting request item list):

SELECT * 
FROM request_table 
WHERE Requestor_Division = 'Division B' 
  AND Requestor_Section = 'Section 2' 
  AND Item_Division = 'Division Z'

Another process(update request item status for processing):

BEGIN TRAN
for loop item in Requestor Division A and Section 1:

    UPDATE status = 'Acknowledged' 
    FROM request_table 
    WHERE Requestor_Division = 'Division A' 
      AND Requestor_Section = 'Section 1' 
      AND Item_Division = 'Division Z'  
      AND Item_Code = 'xxxx'

COMMIT TRAN

I am wondering two if processes are reading and updating different records and why it would be a deadlock.

You should supply the deadlock graph as XML not an image. But anyway are they both running at serializable isolation level? Hence range locks? If so was that deliberate or just because it is a default? Also have you considered using RCSI for the `SELECT` so it takes no key locks at all? — Martin Smith, Aug 14 '23 at 06:56
Are you certain that is the correct functional primary key for the table? i.e. can a given `Item_Code` be in two different `Item_Divisions`? If not, then `Item_Divisions` should not be part of your PK. Putting the PK on a diet can help with reducing I/O, therefore wait times, therefore contention, therefore deadlocks. — Nick.Mc, Aug 14 '23 at 06:59
Please show all indexes on the table if any, and please share the query plans for both queries using https://brentozar.com/pastetheplan. Please show the graph as XML not as an image. What is the `UPDATE` query doing exactly that it needs to be a loop? Generally loops should be avoided in SQL. — Charlieface, Aug 14 '23 at 07:15
@MartinSmith Just using database default setting and I didn't set any isolation level in my application. — Kenneth.F, Aug 14 '23 at 07:20
@Charlieface There are two item divisions, and can be added depends on business. And this table have been used many years and not easy to change the structure. There are only one index in the table. In Business case, user can select item from the requested list and update the corresponding status (acknowledged, processing, completed, back-order...) — Kenneth.F, Aug 14 '23 at 07:30
@Dai Actually, the sql is wrapped in a stored procedure and called in ASP.Net business layer — Kenneth.F, Aug 14 '23 at 07:32
Like I said, we need all that info, otherwise the question is not really answerable. Deadlocks are not easy to diagnose, don't make it more difficult than it needs to be. — Charlieface, Aug 14 '23 at 08:33
So, yeah, both trans are running at serializable. Don't do that unless you have a good reason for needing it. It is highly deadlock prone - I presume you are using a `TransactionScope` and getting [the default](https://stackoverflow.com/q/11292763/73226)? And again consider using RCSI so readers don't take key locks — Martin Smith, Aug 15 '23 at 02:41
@Charlieface Thanks for your comment. Added the deadlock xml. for more info, there are two update stored procedures 1. Update Request for updating the columns other than status 2. Update Request status for updating the status and related log column — Kenneth.F, Aug 15 '23 at 02:48
@MartinSmith We use TransactionScope for all connection require update action (UPD stored procedure). I have read some article, it require modify database setting to support Read Committed Snapshot Isolation. Does any other simple way to achieve? — Kenneth.F, Aug 15 '23 at 03:02
Just using normal ReadComitted will be significantly less deadlock prone than serializable. Blocking and Deadlocks is how it gives the serializable semantics. Though TBH RCSI is not at all complex to enable anyway. You need to consider the isolation level that you actually need here. Likely not serializable if you are only using that by accident as it is the default. — Martin Smith, Aug 15 '23 at 03:26
@MartinSmith Thanks for your comment. I am using entity framework 6 and use transactionscope for all update statement(update stored procedure) and don't know why it will use serializable as default. Anyway, If I change the select statement to use read committed isolation level (which declare transaction option in transaction scope creation). Will it improve the deadlock issue? Changing RCSI in database level, it should talk to the DBA why it should be changed and why other application do not require and etc.... — Kenneth.F, Aug 15 '23 at 04:57
"Will it improve the deadlock issue?" - reader/writer deadlocks are still possible at RC locking isolation level but should be less likely than at serializable as (1) The Key locks taken out will just be on the key rather than a key + adjacent range (2) they are released once the row is read rather than being held until the end of the transaction. So it shouldn't be holding a lock on `PK_XXXX_Request` whilst waiting for one as here — Martin Smith, Aug 15 '23 at 06:53
Please can you share the query plans also, and show what else the `UPDATE` is doing in th same transaction. — Charlieface, Aug 15 '23 at 10:01
I got some interesting finding. Before calling TransactionScope, all the connection will use isolation level ReadCommitted. Once calling TransactionScope, all coming connection will use the isolation level serializable, no matter calling TransactionScope or not. Maybe it is issue using connection pool. if the connection in the pool is disposed. It will use isolation level ReadCommitted again. Now, I am trying to add using TransactionScope with option isolation level ReadCommitted in select stored procedure. And see whether the issue is improved. Thanks again for all you guys. — Kenneth.F, Aug 16 '23 at 03:00
Finally got the problem and can be repeated in MSSQL Management Studio. Both transaction are set isolation level serializable. Transaction A has begin tran and select the status is suitable before update. Transaction B has no begin tran and only has select statement. Transaction B will be deadlock if the running between transaction A. There are two solutions for this issue. (1) Remove all select statement in Transaction A. (2) change transaction B to isolation level read committed. Then the transaction will wait the Transaction A commit. — Kenneth.F, Aug 17 '23 at 06:23

DeadLock issue on primary key index

0 Answers0