Procedure procA updates the same table as procB (table PLS), this is info from deadlock file -
procA relevant code:
UPDATE dbo.PLS
SET LModifiedDate = @dt,
LDate = @dt,
LReasonID = ISNULL(@LReasonID, 323)
WHERE PID = @PID
AND LDate IS NULL
procB relevant code:
UPDATE TOP(@BatchSize) pl
SET LModifiedDate = @dt,
LDate = DATEADD(MINUTE, ISNULL(CAST(par.ParVal AS INT),@Fallback), pac.LastAccessDate),
LReasonID = 333
OUTPUT INSERTED.PID, INSERTED.LDate, INSERTED.ISID, INSERTED.LID, pac.CID
INTO @PTL (PlayerID,LDate,LoginSessionID,LID,CID)
FROM dbo.PAct AS pac WITH (NOLOCK)
INNER JOIN dbo.PLS AS pl WITH (READPAST) ON pl.PlayerID = pac.PlayerID
LEFT JOIN dbo.Pars AS par WITH (NOLOCK) ON par.ParName = 'STO' AND par.CID = pac.CID
WHERE pac.LastAccessDate <= DATEADD(MINUTE, -ISNULL(CAST(par.ParVal AS INT),@Fallback), @dt)
AND pac.LastAccessDate > @LastAccessFromDateTime
AND pl.LinDate < DATEADD(MINUTE, ISNULL(CAST(par.ParVal AS INT),@Fallback), pac.LastAccessDate)
AND pl.LDate IS NULL
OPTION (RECOMPILE)
Dead lock graph shows that one key lock was on primary key PK_PLS_LID and second key lock on non clusterd index column IX_PLS_LDate_LinDate
(Request Mode U/Owner mode X/Request Mode X/Owner mode U)