SQL Server链式存储记录：高并发下避免重复前置ID的最优方案

针对你遇到的高流量场景下链式记录重复前置ID的问题，以下是几个经过验证的可行方案：

方案一：序列化隔离级别+原子事务操作 #

核心思路是通过数据库的事务隔离机制，强制保证每次获取并写入链式记录的操作是排他且原子的，从根源上避免并发冲突。

1. 创建存储过程封装完整的链式写入逻辑，使用SERIALIZABLE隔离级别（最高隔离级别，防止幻读和并发修改），同时配合UPDLOCK和HOLDLOCK锁提示锁定最后一条记录：

CREATE PROCEDURE InsertChainRecord
    @XmlData XML
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;
    BEGIN TRY
        -- 锁定并获取最后一条记录的哈希值
        DECLARE @PrevHash VARCHAR(64);
        SELECT TOP 1 @PrevHash = XmlHash 
        FROM ChainTargetTable 
        ORDER BY RecordId DESC
        WITH (UPDLOCK, HOLDLOCK);

        -- 计算当前XML的哈希（这里用SHA2_256示例，可替换为你的哈希算法）
        DECLARE @CurrentHash VARCHAR(64);
        SET @CurrentHash = CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', CONVERT(VARBINARY(MAX), @XmlData)), 2);

        -- 插入当前记录，关联前置哈希
        INSERT INTO ChainTargetTable (XmlData, XmlHash, PrevXmlHash)
        VALUES (@XmlData, @CurrentHash, @PrevHash);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- 抛出异常让调用方处理，或根据需求重试
    END CATCH
END

2. 所有写入请求都通过这个存储过程执行，高流量下会自动排队处理，确保每条记录的前置哈希都是最新的有效值。

方案二：序列+异步有序处理 #

如果前置处理（XML哈希计算等）耗时较长，可将并发写入和链式处理解耦，先快速写入中间表，再通过后台任务按顺序生成链式记录：

1. 创建自增序列用于标记处理顺序：

CREATE SEQUENCE ChainSequence AS BIGINT START WITH 1 INCREMENT BY 1;

2. 创建中间表存储待处理数据：

CREATE TABLE ChainStagingTable (
    SequenceId BIGINT DEFAULT NEXT VALUE FOR ChainSequence PRIMARY KEY,
    XmlData XML NOT NULL,
    IsProcessed BIT DEFAULT 0
);

3. 高流量写入时直接插入中间表（无需等待哈希计算，速度快）：

INSERT INTO ChainStagingTable (XmlData) VALUES (@XmlData);

4. 编写后台任务（SQL Agent作业或外部服务），按SequenceId顺序处理：

DECLARE @LastProcessedSeq BIGINT = 0;
WHILE 1=1
BEGIN
    BEGIN TRANSACTION;
    -- 获取下一条未处理的记录
    DECLARE @CurrentSeq BIGINT, @CurrentXml XML;
    SELECT TOP 1 @CurrentSeq = SequenceId, @CurrentXml = XmlData
    FROM ChainStagingTable
    WHERE SequenceId > @LastProcessedSeq AND IsProcessed = 0
    ORDER BY SequenceId ASC
    WITH (UPDLOCK, READPAST); -- 跳过已锁定的记录，避免阻塞

    IF @CurrentSeq IS NULL
    BEGIN
        COMMIT TRANSACTION;
        WAITFOR DELAY '00:00:01'; -- 无数据时休眠1秒
        CONTINUE;
    END

    -- 获取前一条链式记录的哈希
    DECLARE @PrevHash VARCHAR(64);
    SELECT TOP 1 @PrevHash = XmlHash 
    FROM ChainTargetTable 
    ORDER BY RecordId DESC;

    -- 计算当前哈希并插入目标表
    DECLARE @CurrentHash VARCHAR(64);
    SET @CurrentHash = CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', CONVERT(VARBINARY(MAX), @CurrentXml)), 2);
    INSERT INTO ChainTargetTable (XmlData, XmlHash, PrevXmlHash)
    VALUES (@CurrentXml, @CurrentHash, @PrevHash);

    -- 标记中间表记录为已处理
    UPDATE ChainStagingTable SET IsProcessed = 1 WHERE SequenceId = @CurrentSeq;
    SET @LastProcessedSeq = @CurrentSeq;

    COMMIT TRANSACTION;
END

这个方案将高并发写入的压力转移到中间表，后台任务串行处理保证链式结构的正确性，同时避免了前置处理耗时影响写入性能。

方案三：乐观锁+重试机制 #

如果不想用高隔离级别影响并发性能，可采用乐观锁配合重试逻辑，适合冲突频率较低的高流量场景：

1. 在目标表添加版本号列：

ALTER TABLE ChainTargetTable ADD VersionRow BIGINT DEFAULT 0;

2. 编写带重试逻辑的写入逻辑：

DECLARE @XmlData XML = '<your-xml-data>';
DECLARE @RetryCount INT = 3; -- 设置重试次数
DECLARE @Success BIT = 0;

WHILE @RetryCount > 0 AND @Success = 0
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @PrevHash VARCHAR(64), @LastVersion BIGINT;
        -- 获取最后一条记录的哈希和版本号
        SELECT TOP 1 @PrevHash = XmlHash, @LastVersion = VersionRow
        FROM ChainTargetTable 
        ORDER BY RecordId DESC;

        -- 计算当前哈希
        DECLARE @CurrentHash VARCHAR(64);
        SET @CurrentHash = CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', CONVERT(VARBINARY(MAX), @XmlData)), 2);

        -- 插入时检查版本号是否未变化（乐观锁）
        INSERT INTO ChainTargetTable (XmlData, XmlHash, PrevXmlHash, VersionRow)
        VALUES (@XmlData, @CurrentHash, @PrevHash, @LastVersion + 1);

        COMMIT TRANSACTION;
        SET @Success = 1;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @RetryCount = @RetryCount - 1;
        IF @RetryCount = 0
        BEGIN
            THROW; -- 重试失败抛出异常
        END
        WAITFOR DELAY '00:00:00.1'; -- 短暂休眠后重试
    END CATCH
END

当并发冲突发生时，事务会回滚并自动重试，直到成功或达到重试次数上限。

关于你之前临时表方案失败的可能原因 #

你之前的临时表+后台任务方案失效，大概率是因为没有保证后台任务的严格顺序读取：比如临时表没有按写入时间或唯一递增标识排序，导致后台任务乱序处理，多条记录同时获取到同一个前置记录的哈希。只要给临时表添加类似序列的递增标识，后台任务按标识顺序处理，就能解决这个问题（本质和方案二类似）。

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