Transaction (Process ID) was deadlocked on lock - deadlock errors on table update

Question

I have a stored procedure which updates a few tables like this:

CREATE PROCEDURE SP_UpdateSomeTables
  @bundleId bigint, 
  @wfStage tinyint
AS
  SET XACT_ABORT ON
  
  SET NOCOUNT ON        
  
  BEGIN TRANSACTION

    UPDATE 
      table1
    SET 
      [LastUpdated] = GETDATE(),
      [WorkflowStage] = @wfStage
    WHERE 
      [Id] = @bundleId
    
    UPDATE 
        table2
    SET
        [LastModified] = GETDATE(),
        [WorkflowStage] = @wfStage
    WHERE
        [ResultBundleId] = @bundleId AND
        [IsCancelled] = 0      
    
  COMMIT TRANSACTION

GO

I'm getting the following error:

Transaction (Process ID 69) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

There are other SELECT queries running parallel on the same set of tables.

How can I avoid the deadlock?

FYI, the sp_ prefix is reserved, by Microsoft, for Special Procedures. It should not be used for User Stored Procedures names. Using the prefix comes at a performance cost and could result in your Procedure simply not working after an update or upgrade. This is also warned in the documentation. — Larnu
There are many causes and resolutions to deadlocks - table scans or lock escalation, order of operations across multiple transactions etc - a lot more info is required - table schemas, indexes on the tables involved, the execution plan, the deadlock graph, the isolation level used etc — Stu
@Larnu thanks for the heads up. I wasn't aware of this. Unfortunately, this is a brownfield project and the db objects cannot be renamed... — masroore
The first step in resolving the deadlock is inspecting the deadlock graph to determine on what resources the deadlock is occurring. And when asking for assistance you need to provide the deadlock graph to us. — Dale K
Deadlocks can often be lessened with query and index tuning. Check the execution plans for the queries involved in the deadlock to ensure only the rows needed are touched. — Dan Guzman

Lenroy Yeung Lenroy Yeung · Accepted Answer · 2021-09-16T13:33:13

I think you have a view that query table1 and table2 that is running in the background. Add commit transaction after the first update then call another begin transaction after the first commit.

CREATE PROCEDURE SP_UpdateSomeTables
  @bundleId bigint, 
  @wfStage tinyint
AS
  SET XACT_ABORT ON

  SET NOCOUNT ON        

  BEGIN TRANSACTION

    UPDATE 
      table1
    SET 
      [LastUpdated] = GETDATE(),
      [WorkflowStage] = @wfStage
    WHERE 
      [Id] = @bundleId
  COMMIT TRANSACTION --add this

  BEGIN TRANSACTION -- add this
    UPDATE 
        table2
    SET
        [LastModified] = GETDATE(),
        [WorkflowStage] = @wfStage
    WHERE
        [ResultBundleId] = @bundleId AND
        [IsCancelled] = 0      

  COMMIT TRANSACTION

GO

Transaction (Process ID) was deadlocked on lock - deadlock errors on table update

1 Answers