Sql-server – Deadlock — Way to make this better

deadlocksql serversql-server-2016update

I have this query that deadlocks every once in a while. It is doing a self update to a table. ( update, from ) Is there any better way to handle the concurrency?
Here is the plan:
https://www.brentozar.com/pastetheplan/?id=r19CZMC8-

Deadlock:

UPDATE j SET StatusId=@StatusId — SELECT *
FROM jobmanager.JobRun j with(rowlock) WHERE PrintBatchId=@PrintBatchI

Proc [Database Id = 11 Object Id = 191196327]

Best Answer

  1. I would try to understand why your query leads to a deadlock. It would be useful to look at deadlock graph info. Try enabling trace flags (1204, 1222) to get more details from the SQL Server log.

  2. Since you are using the row level locking and get deadlocks I suspect that PrintBatchId might have a large number of records associated. So SQL Server has to escalate the lock to page/table level. And as a result, you get a deadlock with an update on different PrintBatchId. (BTW. Do you have an index on PrintBatchId? W/o proper index row level locking won't work). Try to make query filter smaller.

  3. If you can't make your filter more precise I would suggest to catch deadlocks and retry update attempt. Here is a sample code:

    DECLARE @Retries INT = 5
    , @ErrorMessage nvarchar(max)
    , @ErrorLine INT    
    , @ErrorSeverity INT
    , @ErrorNumber INT 

WHILE (@Retries > 0)
BEGIN             
    BEGIN TRY
        --UPDATE Command.... 
        SELECT @retries = 0
    END TRY
    BEGIN CATCH
        SELECT 
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorLine    = ERROR_LINE()
            , @ErrorSeverity= ERROR_SEVERITY()
            , @ErrorNumber  = ERROR_NUMBER();

        IF (@ErrorNumber = 1205 AND @Retries > 0) -- If Deadlock, then retry 
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock. Retrying... ';
        ELSE
        IF (@ErrorNumber = 1205 AND @Retries <= 0) -- If Deadlock, and max number of retries reached. Fail.
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock after max number of retries ... ' + CHAR(13) + ' Original Error: Msg. ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;
        ELSE
            SELECT @ErrorMessage = 'Execution Failed: Msg ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;

        SELECT @Retries-=1;     

    END CATCH;
END
Related Question