title: How to avoid deadlock while updating a table multiple times in a stored procedure?
body: <p>I have a stored procedure in which I am updating a table multiple times, but this is causing deadlock in my case when multiple sessions run this procedure.</p>
  
  <p>I have tried to use Begin Trans and Commit statements but not working for me. I have read that creating index can solve my problem but how is it going to help and on which column should I create (which type of) index?</p>
  
  <pre><code>UPDATE lessonPlanSkill SET 
    PreviousProficiency = lessonPlanSkill.CurrentProficiency ,
    PreviousAccuracy = lessonPlanSkill.CurrentAccuracy  ,
    CoveragePercentage = Calculations.CoveragePercentage,
    AccuracyPercentage = Calculations.AccuracyPercentage,
    CurrentAccuracy = Calculations.AccuracyPercentage,
    TotalQuestions = Calculations.AttemptedQuestions,
    CorrectQuestions = Calculations.CorrectAttempts,
    CurrentAchievementScore = Calculations.CurrentAchievedScore,
    TotalAchievementScore = Calculations.TotalAchievementScore,
    TimeSpentInMin=TimeSpentInMin+@ModuleAttemptHistoryTimeSpent,
    AchievementPercentage = CASE WHEN (Calculations.TotalAchievementScore&gt;0) 
    THEN (Calculations.CurrentAchievedScore/Calculations.TotalAchievementScore)*100 
    END
  FROM #tblLearnerProficiency Calculations 
  INNER JOIN dbo.UserLessonPlanSkill lessonPlanSkill ON lessonPlanSkill.UserId = @pUserId AND lessonPlanSkill.LessonPlanSharedTrackingId = @LessonPlanSharedTrackingId AND lessonPlanSkill.SkillId = Calculations.SkillId AND lessonPlanSkill.IsDeleted = 0
  
  -- Insert statement to insert some data in UserLessonPlanSkill table
  -- Again update statement which is most likely causing deadlock with above update statement
  UPDATE lessonPlanSkill SET 
    CurrentProficiency = ISNULL(logic.ProficiencyLevel,1),
    ModifiedOn = GETUTCDATE(),
    IsSkillStuck = CASE WHEN (logic.ProficiencyLevel = @PROFICIENT_STATUS) THEN 0 ELSE IsSkillStuck END  
  FROM #tblLearnerProficiency Calculations 
  INNER JOIN dbo.UserLessonPlanSkill lessonPlanSkill ON 
  lessonPlanSkill.UserId = @pUserId AND 
  lessonPlanSkill.LessonPlanSharedTrackingId = @LessonPlanSharedTrackingId 
  AND lessonPlanSkill.SkillId = Calculations.SkillId AND 
  lessonPlanSkill.IsDeleted = 0
  INNER JOIN dbo.JudgementLogic logic WITH(NOLOCK) ON logic.FormulaId = 
  Calculations.FormulaId AND logic.IsDeleted = 0
  WHERE 
  Calculations.AccuracyPercentage BETWEEN AccuracyMinPercentage AND 
  AccuracyMaxPercentage  AND
  Calculations.CoveragePercentage BETWEEN CoverageMinPercentage AND 
  CoverageMaxPercentage 
  
  -- After some queries, another update statement comes at the end of this stored procedure updating same table.
  
  UPDATE CurrentResult SET 
    MedianPercentage = ((CoveragePercentage/MaxQuestionCount)*AccuracyPercentage)
  FROM dbo.UserLessonPlanSkill CurrentResult  
  INNER JOIN #tblFilteredSkills skills ON skills.SkillId = 
  CurrentResult.SkillId 
  AND CurrentResult.LessonPlanSharedTrackingId = @LessonPlanSharedTrackingId
  AND CurrentResult.UserId = @pUserId 
  AND CurrentResult.IsDeleted = 0
  </code></pre>
  
  <p>PS: Here is the <a href="https://drive.google.com/file/d/1i5BxLT7huDC1MkUbu9uZQ_PGKOfcpvGB/view" rel="nofollow noreferrer">deadlock graph</a> and here is <a href="https://drive.google.com/file/d/1ldt2jDmkKAfRjdLv0IQVodFXoJNFyQRT/view" rel="nofollow noreferrer">description</a> extracted from profiler</p>