如何解决WhatsApp API状态通知处理的UPDATE竞态条件问题？

核心调整：重构表结构 #

首先要改变原有的单状态存储设计——单一的status和timestamp字段会导致状态覆盖，无法保留全量状态信息。改成按状态拆分时间字段，确保一条message_id记录能存储所有状态的时间：

CREATE TABLE WhatsAppMessageStatus (
    message_id VARCHAR(255) PRIMARY KEY, -- 用WhatsApp的message_id作为主键，强制唯一约束
    sent_at DATETIME2 NULL,       -- 消息发送完成时间
    delivered_at DATETIME2 NULL,  -- 消息送达接收方时间
    read_at DATETIME2 NULL,       -- 消息被接收方读取时间
    last_updated_at DATETIME2 DEFAULT GETUTCDATE() -- 记录最后更新时间
);

原子UPSERT存储过程（SQL Server） #

方案1：使用MERGE语句（推荐） #

MERGE是SQL Server原生的原子UPSERT操作，能在单个语句内完成「匹配更新、不匹配插入」，从根源避免竞态条件：

CREATE PROCEDURE UpdateWhatsAppMessageStatus
    @message_id VARCHAR(255),
    @status VARCHAR(20), -- 传入状态：'sent'/'delivered'/'read'
    @status_timestamp DATETIME2
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO WhatsAppMessageStatus AS target
    USING (
        SELECT 
            @message_id AS message_id,
            @status AS status,
            @status_timestamp AS status_timestamp
    ) AS source
    ON target.message_id = source.message_id
    -- 匹配到记录时，仅更新对应状态的时间字段，保留其他状态数据
    WHEN MATCHED THEN
        UPDATE SET
            sent_at = CASE WHEN source.status = 'sent' THEN source.status_timestamp ELSE target.sent_at END,
            delivered_at = CASE WHEN source.status = 'delivered' THEN source.status_timestamp ELSE target.delivered_at END,
            read_at = CASE WHEN source.status = 'read' THEN source.status_timestamp ELSE target.read_at END,
            last_updated_at = GETUTCDATE()
    -- 未匹配到记录时，插入新记录并填充当前状态的时间
    WHEN NOT MATCHED THEN
        INSERT (message_id, sent_at, delivered_at, read_at)
        VALUES (
            source.message_id,
            CASE WHEN source.status = 'sent' THEN source.status_timestamp ELSE NULL END,
            CASE WHEN source.status = 'delivered' THEN source.status_timestamp ELSE NULL END,
            CASE WHEN source.status = 'read' THEN source.status_timestamp ELSE NULL END
        );
END;

方案2：带范围锁的UPDATE+INSERT #

如果更习惯传统的先更后插写法，必须结合UPDLOCK和HOLDLOCK锁锁定索引范围，防止并发请求插入重复记录：

CREATE PROCEDURE UpdateWhatsAppMessageStatus
    @message_id VARCHAR(255),
    @status VARCHAR(20),
    @status_timestamp DATETIME2
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    -- 用UPDLOCK+HOLDLOCK锁定message_id对应的索引范围，即使记录不存在，其他事务也无法插入相同message_id的记录
    UPDATE WhatsAppMessageStatus WITH (UPDLOCK, HOLDLOCK)
    SET
        sent_at = CASE WHEN @status = 'sent' THEN @status_timestamp ELSE sent_at END,
        delivered_at = CASE WHEN @status = 'delivered' THEN @status_timestamp ELSE delivered_at END,
        read_at = CASE WHEN @status = 'read' THEN @status_timestamp ELSE read_at END,
        last_updated_at = GETUTCDATE()
    WHERE message_id = @message_id;

    -- 若未找到匹配记录，执行插入
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO WhatsAppMessageStatus (message_id, sent_at, delivered_at, read_at)
        VALUES (
            @message_id,
            CASE WHEN @status = 'sent' THEN @status_timestamp ELSE NULL END,
            CASE WHEN @status = 'delivered' THEN @status_timestamp ELSE NULL END,
            CASE WHEN @status = 'read' THEN @status_timestamp ELSE NULL END
        );
    END;

    COMMIT TRANSACTION;
END;

关键说明 #

1. 原有方案失效原因
   原设计的单状态字段会导致状态覆盖，且先更后插的逻辑在并发时，两个事务都会检测到@@ROWCOUNT=0，进而执行重复插入。单独使用UPDLOCK无法锁定不存在的记录范围，SERIALIZABLE隔离级别也需要配合范围锁才能阻止并发插入。
2. 原子操作的必要性
   MERGE或带范围锁的UPDATE+INSERT都是原子性操作，能确保同一message_id的并发请求不会产生重复记录，所有状态都会被正确更新到同一条记录中。

内容的提问来源于stack exchange，提问作者Joe