Post reply

Concurrent Update to Same Record - Deadlock

  • September 28, 2018 at 3:11 pm

    #408568

    One of our client is updating the same record and same column concurrently which is causing blocking/deadlock some times.. how it can be resolved.?. Client is not ready to give up concurrent updates..

  • September 28, 2018 at 4:01 pm

    #2007416

    jrobertraj - Friday, September 28, 2018 3:11 PM

    One of our client is updating the same record and same column concurrently which is causing blocking/deadlock some times.. how it can be resolved.?. Client is not ready to give up concurrent updates..

    The following is a snippet from a procedure I wrote that needs to trap for deadlocks:

    while 1 = 1 -- Loop to permit deadlock handling
    begin
      begin transaction;
      begin try;

      < your code goes here>
      
      commit transaction;

      WAITFOR DELAY ''00:00:00.05'' -- Wait for 50 ms
      break;

      end try
      begin catch
      rollback transaction;
      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
      end
      else begin
       -- All other errors
       raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState) with nowait;
      end
      end catch
    end -- End while loop for deadlock processing

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply