How to prevent deadlock in SQL Server stored procedure?

Question

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:

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

Solution 1

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

Solution 2

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