sql - SQL Server 上的死锁问题，更新更新

Question

首先，我讨厌死锁，因为我永远无法弄清楚为什么，我在我们的数据库上遇到了这个死锁，我似乎无法弄清楚如何修复它，基本上它说这是一个钥匙锁。

环境：Azure Hyper-Scale 2-Vcore

我们有 2 个同时运行的合并语句，似乎会导致死锁。

合并语句1：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
MERGE [Q_Net_Ml_PlayerLifecycleDuration] t USING @Data s ON t.MerchantID = s.MerchantID and t.ExternalCustomerID = s.ExternalCustomerID 
WHEN MATCHED THEN

    UPDATE SET TimeID = s.TimeID, StageID = s.StageID, Duration = s.Duration, PredictionStageID = s.PredictionStageID, PredictionDuration = s.PredictionDuration, DaysSinceSignin = s.DaysSinceSignin, DaysSinceDeposit = s.DaysSinceDeposit, DaysSinceWager = s.DaysSinceWager, DaysSincePayout = s.DaysSincePayout, DaysSinceBonus = s.DaysSinceBonus, DaysSinceWithdrawal = s.DaysSinceWithdrawal, DateCreated = GETUTCDATE()

WHEN NOT MATCHED THEN

    insert (MerchantID, ExternalCustomerID, TimeID, StageID, Duration, PredictionStageID, PredictionDuration, DaysSinceSignin, DaysSinceDeposit, DaysSinceWager, DaysSincePayout, DaysSinceBonus, DaysSinceWithdrawal, DateCreated)
    values (s.MerchantID, s.ExternalCustomerID, s.TimeID, s.StageID, s.Duration, s.PredictionStageID, s.PredictionDuration, s.DaysSinceSignin, s.DaysSinceDeposit , s.DaysSinceWager, s.DaysSincePayout, s.DaysSinceBonus, s.DaysSinceWithdrawal, GETUTCDATE())
OPTION (MAXDOP 1,IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

合并语句 2：

MERGE INTO dbo.Q_Net_Ml_PlayerLifecycleDuration AS ExistingEvent  
USING (SELECT MerchantID, ExternalCustomerID, StageID, TimeID, ActivityTimeID  
 FROM OPENJSON(@Events)  
 WITH ([MerchantID] int, [TimeID] int, [ExternalCustomerID] varchar(50), [StageID] int, [ActivityTimeID] int)) AS NewEvent  
 ON (ExistingEvent.MerchantID = NewEvent.MerchantID  
 AND ExistingEvent.ExternalCustomerID = NewEvent.ExternalCustomerID)  
WHEN MATCHED THEN  
  
 UPDATE SET  
 StageID = NewEvent.StageID,  
 DateCreated = @Now,  
 Duration = @TimeIDNow - NewEvent.ActivityTimeID,  
 TimeID = NewEvent.TimeID,  
 PredictionStageID = 0,  
 PredictionDuration = 0  
  
WHEN NOT MATCHED THEN  
  
 INSERT (TimeID, ExternalCustomerID, MerchantID, StageID, DateCreated, Duration, PredictionStageID, PredictionDuration)  
 VALUES(NewEvent.TimeID,  
  NewEvent.ExternalCustomerID,  
  NewEvent.MerchantID,  
  NewEvent.StageID,  
  @Now,  
  @TimeIDNow - NewEvent.ActivityTimeID,  
  0,  
  0)OPTION (MAXDOP 1,IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX); 

Q_Net_Ml_PlayerLifecycleDuration 在 MID 上进行分区

当它运行时，它似乎有以下死锁问题：

这似乎是一个使用 mode = U 的更新问题，但我似乎在谷歌上找不到任何 mode = U 的东西

我尝试了一些提示，但主要只是猜测是否会有所帮助，有时会导致更多问题。

表详细信息：

CREATE TABLE [dbo].[Q_Net_Ml_PlayerLifecycleDuration](
    [RecordID] [bigint] IDENTITY(1,1) NOT NULL,
    [MerchantID] [int] NOT NULL,
    [ExternalCustomerID] [varchar](50) NULL,
    [TimeID] [int] NOT NULL,
    [StageID] [int] NOT NULL,
    [Duration] [int] NOT NULL,
    [PredictionStageID] [int] NOT NULL,
    [PredictionDuration] [int] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [DaysSinceSignin] [int] NULL,
    [DaysSinceDeposit] [int] NULL,
    [DaysSinceWager] [int] NULL,
    [DaysSincePayout] [int] NULL,
    [DaysSinceBonus] [int] NULL,
    [DaysSinceWithdrawal] [int] NULL
)

表大小：2045675 行 981664 KB 数据 1402832 KB 索引大小

按 MerchantID 分区，例如 1、2、3、17、100,200,250 等。表上的索引：

CREATE CLUSTERED INDEX [CI_Q_Net_Ml_PlayerLifecycleDuration] ON [dbo].[Q_Net_Ml_PlayerLifecycleDuration]
(
    [ExternalCustomerID] ASC,
    [MerchantID] ASC,
    [StageID] ASC
)

lock_escalation：自动

然后是其余字段的非聚集列存储索引，但在合并语句中禁用。

我还注意到还有其他针对此表运行的脚本并注意到 lck_M_IX 和 LCK_M_U

Blitz 表报告显示：行锁等待：51；总时长：4分钟；平均持续时间：5 秒；页面锁等待：503；总时长：24 秒；平均持续时间：0 秒；锁升级尝试：32,797；实际升级：543；

标签： sqlsql-serverazuredeadlockdatabase-deadlocks