0

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]

  • That query by itself can't cause deadlock so you need to figure out what more is involved in it. – Daniel Björk Aug 1 '17 at 18:07
0
  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
    
  • Alexander, yesterday I updated the post with the deadlock plan. Maybe it isn't there? Either way I will update the proc with your retry logic and push through our dev cycle. – Shellz Aug 2 '17 at 16:01
  • Alexander can you see the deadlock plan? – Shellz Aug 7 '17 at 21:11
  • DECLARE @IsError bit SELECT @IsError=IsError FROM jobmanager.Status WHERE StatusID=@StatusId --Set Error state on batch: UPDATE b SET StatusId=@StatusId,ErrorDttm=case when @IsError=1 then getdate() end -- SELECT * FROM jobmanager.PrintBatch b with(rowlock) WHERE PrintBatchId=@PrintBatchId UPDATE j SET StatusId=@StatusId -- SELECT * FROM jobmanager.JobRun j with(rowlock) WHERE PrintBatchId=@PrintBatchId – Shellz Aug 7 '17 at 21:13
  • I see this: "Deadlock: UPDATE j SET StatusId=@StatusId -- SELECT * FROM jobmanager.JobRun j with(rowlock) WHERE PrintBatchId=@PrintBatchI Proc [Database Id = 11 Object Id = 191196327]" on your main message. But this information is not sufficient. – Alexander Sharovarov Aug 7 '17 at 21:22
  • On the other hand I took another look at the execution plan. I see that you the second query does the Clustered Index Scan. What is the structure of the jobmanagerJobRun.PK_JobRun index? Why does it scan it? It is most likely affecting locking. – Alexander Sharovarov Aug 7 '17 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.