Resolving deadlock when multiple stored procedures are reading and updating the same table in SQL Server 2014 database

Question

I have two stored procedures. First SP runs when records are received from server to save it in relationship table. Second SP runs after every fixed interval of time through a scheduler from .NET server.

First Stored Procedure Code:

BEGIN TRY
INSERT INTO RELATIONSHIP SELECT DATA FROM ANOTHER TABLE
SELECT INTO TEMPTABLE FROM RELATIONSHIP
UPDATE RELATIONSHIP SET...
END TRY

Second Stored Procedure Code:

BEGIN TRY
BEGIN TRANSACTION
SELECT INTO TEMPTABLE FROM RELATIONSHIP
UPDATE RELATIONSHIP SET ISPROCESSED = 1 WHERE ...
COMMIT TRANSACTION
END TRY

When the second stored procedure runs, it runs into a deadlock. Also, The first stored procedure is not written within a transaction.

What would be the best practice here to resolve the deadlock considering the code logic and flow cannot be changed? I have written code related to relationship table only and skipped code related to other table, as deadlock is occurring on this table. Let me know if you need more information, I can post the deadlock logs too from health monitor.

    <deadlock>
 <victim-list>
  <victimProcess id="process207c2cd3c28" />
 </victim-list>
 <process-list>
  <process id="process207c2cd3c28" taskpriority="0" logused="0" waitresource="PAGE: 5:1:53898 " waittime="774" ownerId="10078305" transactionname="UPDATE" lasttranstarted="2021-07-27T15:43:22.120" XDES="0x20d350857d0" lockMode="U" schedulerid="10" kpid="9288" status="suspended" spid="74" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2021-07-27T15:43:21.030" lastbatchcompleted="2021-07-27T15:43:21.030" lastattention="1900-01-01T00:00:00.030" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="6900" isolationlevel="read committed (2)" xactid="10078305" currentdb="5" currentdbname="" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="ProcessMessage" line="153" stmtstart="11462" stmtend="11928" sqlhandle="0x03000500abb08931c097be0072ad000001000000000000000000000000000000000000000000000000000000">
UPDATE lar
        SET 
            -- some code
        FROM Relationship lar INNER JOIN --few joins   </frame>
    <frame procname="" line="133" stmtstart="10056" stmtend="10112" sqlhandle="0x03000500caf178474f6b59006ead000001000000000000000000000000000000000000000000000000000000">
    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 5 Object Id = 1199108554]   </inputbuf>
  </process>
  <process id="process20958d27848" taskpriority="0" logused="2060" waitresource="PAGE: 5:1:53794 " waittime="523" ownerId="9400887" transactionname="ProcessDiscrepancies" lasttranstarted="2021-07-27T13:24:45.573" XDES="0x20d2cb80420" lockMode="IU" schedulerid="2" kpid="7576" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-07-27T13:24:45.570" lastbatchcompleted="2021-07-27T13:24:45.573" lastattention="1900-01-01T00:00:00.573" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="6900" loginname="" isolationlevel="read committed (2)" xactid="9400887" currentdb="5" currentdbname="" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="ProcessDiscrepancies" line="140" stmtstart="10254" stmtend="11002" sqlhandle="0x030005003968a12f1d68be0072ad000001000000000000000000000000000000000000000000000000000000">
UPDATE lar

            SET 

                IsProcessed = 1,

            FROM Relationship lar   

            INNER JOIN --few joins  </frame>
    <frame procname="adhoc" line="1" stmtend="60" sqlhandle="0x010005007715562a80688d2c0d02000000000000000000000000000000000000000000000000000000000000">
EXEC ProcessDiscrepancie    </frame>
   </executionStack>
   <inputbuf>
EXEC ProcessDiscrepancies   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="53898" dbid="5" subresource="FULL" objectname="Relationship" id="lock1ffa075f280" mode="IU" associatedObjectId="72057597189357568">
   <owner-list>
    <owner id="process20958d27848" mode="IU" />
   </owner-list>
   <waiter-list>
    <waiter id="process207c2cd3c28" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="53794" dbid="5" subresource="FULL" objectname="Relationship" id="lock204c8f59680" mode="U" associatedObjectId="72057597189357568">
   <owner-list>
    <owner id="process207c2cd3c28" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process20958d27848" mode="IU" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

You need to inspect the deadlock graph and find out what resources and locking. Only then can you try and prevent them. Although its not always possible to prevent deadlocks, so a retry mechanism is often also required. — Dale K, Jul 28 '21 at 06:39
@DaleK Is it not possible that when first SP runs, it locks the table and second SP waits for the lock to be lifted. Then when first SP finishes it work, lock is released and then second SP places the lock and starts its work? — Suryanshu Singh, Jul 28 '21 at 06:47
Are you suggesting that is the problem? Or the solution? The deadlock graph is required to provide any meaningful advice. — Dale K, Jul 28 '21 at 06:48
@VenkataramanR The update statement in both the stored proc are conflicting — Suryanshu Singh, Jul 28 '21 at 06:49
@DaleK Whatever I have suggested above seems like a solution to me, but I am not much expert in SQL so I am not able to implement the above solution. Also, i am adding deadlock graph now. — Suryanshu Singh, Jul 28 '21 at 06:52
It might well be a solution, but it could be bad for performance. Thats why you should (ideally) solve the real problem. — Dale K, Jul 28 '21 at 06:54
What isolation level are the statements running at? The deadlock is on `U` locks — Martin Smith, Jul 28 '21 at 07:00
I can't see from your deadlock graph which actual object are involved... the XML is probably more illustrative. — Dale K, Jul 28 '21 at 07:05
@MartinSmith The isolation level checked through dbcc useroptions shows as "read committed snapshot" — Suryanshu Singh, Jul 28 '21 at 07:08
@SuryanshuSingh the *actual* SQL statements and tables matter. You can't fix such problems with query and transaction hints. If indexes are missing the server will have to scan and *lock* far more rows than it needs. If you update rows based on a `bit` flag, the server will have to scan and lock all rows. *Avoiding* multiple SELECT and INSERTs in the same transaction helps. You may not need the temporary table.`SELECT INTO` creates an **unindexed** table which means the next UPDATE will suffer. You can get rid of `SELECT INTO` if you use `OUTPUT` to insert into an existing indexed temp table — Panagiotis Kanavos, Jul 28 '21 at 07:32
Please [edit] your question and add the full queries, along with the table *and index* definitions. Please also share the query plans via https://brentozar.com/pastetheplan. The shape of the plans make a huge difference to what gets locked, and therefore whether a deadlock will occur — Charlieface, Jul 28 '21 at 10:06

score 0 · Answer 1 · answered Jul 28 '21 at 06:58

Few suggestions for fixing the deadlock scenario. You have to try and see, if they help in fixing the issues.

Always handle the transactions in similar way. Have a pattern to follow for transactions. Below is the template, I am following. Reference SO Post

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION;
    THROW;
    --before SQL Server 2012 use 
    --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Make transactions as small as possible. Why are you loading the temptable inside the transaction. Try to load it outside the transaction to make the transaction smaller.
Define proper indexes to make the queries to run faster, it will make the transactions smaller
Why are you INSERT and UPDATE Relationship table one by one. Why can't you to take care of your UPDATE as part of your INSERT. It will totally avoid the UPDATE statement.

Resolving deadlock when multiple stored procedures are reading and updating the same table in SQL Server 2014 database

1 Answers1