Deadlock troubleshooting

• Feivel

  SSCertifiable

  Points: 7524

  More actions

  March 6, 2019 at 10:02 am

  #374008

  My boss is asking me about how I would end up resolving this issue that is causing deadlocks and most likely to prevent it in future. I am under the impression that it is a one off case of simply how the data was accessed but it looks like he is kinda stretching to get a resolution for this. Any help or suggestions would be appreciated. I will also post the .xml file if needed.

  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 6, 2019 at 12:25 pm

  #2023708

  First thing to do is look at the code run by both processes to see what may have caused the deadlock.

  The next thing you could do is implement try/catch blocks and trap for the deadlock error.  If you code a while forever loop correctly you could retry the code that is killed due to a deadlock.
  The following snippet could be used for doing this (written for use in SQL Server 2008 R2):
  
declare @LoopCode bit = 1,
   @ErrorNumber int,
   @ErrorLine int,
   @ErrorMessage nvarchar(4000),
   @ErrorProcedure nvarchar(128),
   @ErrorSeverity int,
   @ErrorState int;

  while 1 = 1
begin
  begin transaction;
  begin try;
  -- <-- code goes here -->
  commit;
  break;
  end try
  begin catch
   rollback;
   select
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorLine = ERROR_LINE(),
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorProcedure = ERROR_PROCEDURE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();
   if @ErrorNumber = 1205 -- Trap deadlock
   begin
    WAITFOR DELAY '00:00:00.05' -- Wait for 50 ms
    continue;
   end
   else begin
    -- All other errors
    raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
   end
  end catch
end


• Feivel

  SSCertifiable

  Points: 7524

  More actions

  March 6, 2019 at 12:31 pm

  #2023714

  Lynn Pettis - Wednesday, March 6, 2019 12:25 PM

  First thing to do is look at the code run by both processes to see what may have caused the deadlock.

  The next thing you could do is implement try/catch blocks and trap for the deadlock error.  If you code a while forever loop correctly you could retry the code that is killed due to a deadlock.
  The following snippet could be used for doing this (written for use in SQL Server 2008 R2):
  
declare @LoopCode bit = 1,
   @ErrorNumber int,
   @ErrorLine int,
   @ErrorMessage nvarchar(4000),
   @ErrorProcedure nvarchar(128),
   @ErrorSeverity int,
   @ErrorState int;

  while 1 = 1
begin
  begin transaction;
  begin try;
  -- <-- code goes here -->
  commit;
  break;
  end try
  begin catch
   rollback;
   select
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorLine = ERROR_LINE(),
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorProcedure = ERROR_PROCEDURE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();
   if @ErrorNumber = 1205 -- Trap deadlock
   begin
    WAITFOR DELAY '00:00:00.05' -- Wait for 50 ms
    continue;
   end
   else begin
    -- All other errors
    raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
   end
  end catch
end


  I'll review the code and see what it was doing.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 6, 2019 at 12:37 pm

  #2023717

  Feivel - Wednesday, March 6, 2019 10:02 AM

  My boss is asking me about how I would end up resolving this issue that is causing deadlocks and most likely to prevent it in future. I am under the impression that it is a one off case of simply how the data was accessed but it looks like he is kinda stretching to get a resolution for this. Any help or suggestions would be appreciated. I will also post the .xml file if needed.

  

  This is an older article but still has a lot of good tips and steps to take in troubleshooting deadlocks. It should give you quite a few ideas in addition to the order of access:
  Deadlock Troubleshooting, Part 1

  Sue

• Feivel

  SSCertifiable

  Points: 7524

  More actions

  March 6, 2019 at 12:49 pm

  #2023718

  Sue_H - Wednesday, March 6, 2019 12:37 PM

  Feivel - Wednesday, March 6, 2019 10:02 AM

  My boss is asking me about how I would end up resolving this issue that is causing deadlocks and most likely to prevent it in future. I am under the impression that it is a one off case of simply how the data was accessed but it looks like he is kinda stretching to get a resolution for this. Any help or suggestions would be appreciated. I will also post the .xml file if needed.

  

  This is an older article but still has a lot of good tips and steps to take in troubleshooting deadlocks. It should give you quite a few ideas in addition to the order of access:
  Deadlock Troubleshooting, Part 1

  Sue

  Thank you