How to prevent deadlock in SQL Server stored procedure?

I’m calling a stored procedure which performs either an INSERT or an UPDATE dependent on the procedure key being present in a table.

So far the procedure was working as expected. Until our user base started to grow in size. Today I got the following error which was resolved by restarting the Application Pool running the service:

  • Executing addlinkedServer within a Stored Procedure Transaction Error
  • VBScript / ADODB Syntax Issue with adArray?
  • How to transform a MSSQL CTE query to MySQL?
  • How to resolve VS2013 Error SQL71501: Procedure X has an unresolved reference to Assembly Y?
  • How to get the week Starting and Ending date using Week number in SQL Server?
  • Are you able to pass the 'TOP' number as a parameter to a stored procedure?
  • InsertDDM_UserDashboard error: RequestError: Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    How can you prevent deadlock in a SQL Server stored procedure?

    I reviewed the this link which suggests it may have been an issue with a SELECT AND UPDATE running concurrently causing the deadlock. But my procedure separates the statements with an IF..ELSE condition so both couldn’t run concurrently.

    Stored procedure:

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[InsertDDM_UserDashboard]
       @p_email VARCHAR(255),
       @p_dashboardPreferences VARCHAR(4000),
       @p_userDefaultDashboard VARCHAR(500)
    AS 
    
    IF (NOT EXISTS(SELECT * FROM [dbo].[DDM_UserProfile] WHERE Email = @p_email)) 
    BEGIN    
    
        INSERT INTO [dbo].[DDM_UserProfile]
               ([Email]
               ,[DashboardPreferences]
               ,DefaultDashboard
               )
         VALUES
               (@p_email
               ,@p_dashboardPreferences
               ,@p_userDefaultDashboard
               )
    
    END ELSE BEGIN
    
            UPDATE [dbo].[DDM_UserProfile]
            SET [DashboardPreferences]=@p_dashboardPreferences
            WHERE [Email]=@p_email
    
            UPDATE [dbo].[DDM_UserProfile]
            SET DefaultDashboard=@p_userDefaultDashboard
            WHERE [Email]=@p_email
    
    END
    

    2 Solutions collect form web for “How to prevent deadlock in SQL Server stored procedure?”

    Would need to see the table and index DDL and full deadlock graph to be sure, but you probably just need to lock the target row on the initial read. EG

    ALTER PROCEDURE [dbo].[InsertDDM_UserDashboard]
       @p_email VARCHAR(255),
       @p_dashboardPreferences VARCHAR(4000),
       @p_userDefaultDashboard VARCHAR(500)
    
    
    AS 
    begin
    begin transaction
    
    IF (NOT EXISTS(SELECT * FROM [dbo].[DDM_UserProfile] with (updlock, holdlock) WHERE Email = @p_email)) 
    BEGIN     
    INSERT INTO [dbo].[DDM_UserProfile]
            ([Email]
            ,[DashboardPreferences]
            ,DefaultDashboard
            )
        VALUES
            (@p_email
            ,@p_dashboardPreferences
            ,@p_userDefaultDashboard
            )
    
    END
    
    ELSE 
    BEGIN 
        UPDATE [dbo].[DDM_UserProfile]
        SET [DashboardPreferences]=@p_dashboardPreferences,
            DefaultDashboard=@p_userDefaultDashboard
        WHERE [Email]=@p_email
    
    END
    
    commit transaction
    end
    

    You could use the Sam Saffron upsert approach like so:

    create procedure dbo.ddm_UserProfile_Dashboard_upsert (
        @p_email varchar(255)
      , @p_dashboardPreferences varchar(4000)
      , @p_userDefaultDashboard varchar(500)
    ) as 
    begin
      set nocount, xact_abort on;
      begin tran;
        update up
          set DashboardPreferences=@p_dashboardPreferences
            , DefaultDashboard    =@p_userDefaultDashboard
          from  dbo.ddm_UserProfile up with (serializable) 
          where up.Email = @p_email;
        if @@rowcount = 0
        begin;
          insert into dbo.ddm_UserProfile (Email, DashboardPreferences, DefaultDashboard)
          values (@p_email, @p_dashboardPreferences, @p_userDefaultDashboard);
        end;
      commit tran;
    end;
    go
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.