title: How to prevent deadlock in SQL Server stored procedure?
body: <p>I'm calling a stored procedure which performs either an <code>INSERT</code> or an <code>UPDATE</code> dependent on the procedure key being present in a table.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>How can you prevent deadlock in a SQL Server stored procedure?</p>
  
  <p>I reviewed the <a href="https://stackoverflow.com/a/22116819/1829251">this link</a> which suggests it may have been an issue with a <code>SELECT</code> AND <code>UPDATE</code> running concurrently causing the deadlock. But my procedure separates the statements with an <code>IF..ELSE</code> condition so both couldn't run concurrently.</p>
  
  <p>Stored procedure:</p>
  
  <pre><code>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
  </code></pre>