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:

  • Can you create nested WITH clauses for Common Table Expressions?
  • Why does changing valid SQL Server pipeline references in old projects cause the build to fail?
  • What bcp format file that I'm gonna used to insert 59 columns?
  • Number of weeks and partial weeks between two days calculated wrong
  • ClassNotFoundException - com.microsoft.jdbc.sqlserver.SQLServerDriver
  • How do I copy SQL Server 2012 database to localdb instance?
  • 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.