How to avoid deadlock while updating a table multi

问题:

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.

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?

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>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

PS: Here is the deadlock graph and here is description extracted from profiler

回答1:

I have solved this problem by creating a temporary table, inserting required data and updating that temp table or inserting new data into that temp table. Eventually insert/update the actual table with the values in temp table on the basis of primary key (index). This resolved my deadlock issue. PS: Please comment or add new answer if anyone gets something better!!