SQL Server带日期重置的特殊ID生成方案并发问题咨询

需求回顾 #

需要生成格式为YYYYMMDD-XXXX的特殊业务ID，其中每日的计数器从0001开始递增，插入数据时自动生成该ID。

现有尝试与问题 #

1. 最初尝试用ROW_NUMBER()结合表中的CreatedDate列计算ID：

   SELECT 
       CONVERT(VARCHAR(8), CreatedDate, 112) + '-' + RIGHT('0000' + CONVERT(VARCHAR(4), ROW_NUMBER() OVER(PARTITION BY CAST(CreatedDate AS DATE) ORDER BY Id)), 4) AS SpecialId
   FROM MyBaseTable
   

   但该方案在并发插入场景下会出现重复ID——多个会话同时计算行号时，会基于相同的数据集生成重复序号。

2. 改用存储过程GetSpecialId，通过IndexingTable维护每日计数器，但出现异常：

   • 存储过程返回的ID正常递增，但IndexingTable中无对应日期的记录插入；
   • Visual Studio调试器挂起，仅能通过重启SQL Server服务恢复。

并发遗漏点分析 #

1. 锁机制缺失 #

如果存储过程中未对IndexingTable的每日记录添加排他性锁，并发调用时会出现竞态条件：多个会话同时读取到相同的计数器值，后续的更新/插入操作可能被覆盖或回滚，导致计数器在内存中递增但未持久化到表中。

2. 事务原子性不足 #

若存储过程中计数器的计算与IndexingTable的写操作未包裹在同一事务内，可能出现“计数器已计算但写操作失败回滚”的情况，表现为返回值递增但表中无记录。

3. 阻塞/死锁 #

调试器挂起的核心原因是阻塞或死锁：比如某个会话长期持有IndexingTable的锁未释放，导致调试会话的请求被无限阻塞；或者存储过程中的锁逻辑不当，触发死锁导致会话挂起。

4. 表结构设计缺陷 #

如果IndexingTable未将RecordDate设为唯一主键或聚集索引，并发插入同日期记录时会触发主键冲突，导致插入失败但计数器逻辑仍在执行，出现返回值与表记录不一致的情况。

优化方案 #

方案1：修复存储过程的锁与事务逻辑 #

确保对IndexingTable的操作原子化，添加合适的锁避免并发冲突：

CREATE PROCEDURE GetSpecialId
    @SpecialId VARCHAR(20) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 遇到错误时自动回滚事务

    DECLARE @Today DATE = CAST(GETDATE() AS DATE);
    DECLARE @Counter INT;

    BEGIN TRANSACTION;
        -- 使用UPDLOCK+HOLDLOCK锁定今日记录，避免并发读取与修改
        SELECT @Counter = Counter
        FROM IndexingTable WITH (UPDLOCK, HOLDLOCK)
        WHERE RecordDate = @Today;

        IF @Counter IS NULL
        BEGIN
            SET @Counter = 1;
            INSERT INTO IndexingTable (RecordDate, Counter)
            VALUES (@Today, @Counter);
        END
        ELSE
        BEGIN
            SET @Counter += 1;
            UPDATE IndexingTable
            SET Counter = @Counter
            WHERE RecordDate = @Today;
        END

        SET @SpecialId = CONVERT(VARCHAR(8), @Today, 112) + '-' + RIGHT('0000' + CONVERT(VARCHAR(4), @Counter), 4);
    COMMIT TRANSACTION;
END

• UPDLOCK：为记录添加更新锁，确保同一时间只有一个会话能修改该记录；
• HOLDLOCK：将锁持有到事务结束，防止其他会话在事务期间读取旧的计数器值；
• SET XACT_ABORT ON：确保遇到错误时事务自动回滚，避免脏数据。

方案2：使用序列（Sequence）实现每日重置 #

利用SQL Server的序列特性，结合每日作业重置序列值：

1. 创建序列：

CREATE SEQUENCE DailyCounterSeq
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE; -- 若需超过9999可改为CYCLE

2. 创建SQL Server代理作业，每日凌晨执行序列重置：

ALTER SEQUENCE DailyCounterSeq
    RESTART WITH 1;

3. 生成ID的逻辑：

DECLARE @Today VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112);
DECLARE @Counter INT = NEXT VALUE FOR DailyCounterSeq;
DECLARE @SpecialId VARCHAR(20) = @Today + '-' + RIGHT('0000' + CONVERT(VARCHAR(4), @Counter), 4);

-- 插入MyBaseTable的逻辑
INSERT INTO MyBaseTable (SpecialId, CreatedDate, ...)
VALUES (@SpecialId, GETDATE(), ...);

序列本身是线程安全的，无需额外锁逻辑，但需确保每日重置作业的可靠性。

方案3：分区视图+IDENTITY（适用于大数据量场景） #

将MyBaseTable按日期分区，每个分区表的IDENTITY列从1开始，通过分区视图统一访问：

1. 创建每日分区表（示例为20240825）：

CREATE TABLE MyBaseTable_20240825 (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    SpecialId AS CONVERT(VARCHAR(8), '20240825') + '-' + RIGHT('0000' + CONVERT(VARCHAR(4), Id), 4) PERSISTED,
    CreatedDate DATETIME DEFAULT GETDATE(),
    -- 其他字段
);

2. 创建分区视图：

CREATE VIEW MyBaseTable
AS
SELECT * FROM MyBaseTable_20240825
UNION ALL
SELECT * FROM MyBaseTable_20240826
-- 其他分区表

该方案复杂度较高，需提前创建分区表或通过动态SQL自动创建，适合数据量极大且需按日期归档的场景。

异常原因总结 #

• 返回值递增但无记录：大概率是存储过程事务未提交（如未捕获错误导致隐式回滚），但计数器变量已在内存中递增；
• 调试器挂起：因锁机制不当导致阻塞或死锁，重启服务后锁被强制释放才恢复。

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