确保批量插入数据的唯一批次ID：并发场景下的疑问与优化建议

我通过VBA实现了多用户从各自工作站独立向SQL Server单表并发批量插入数据的流程，Excel生成的SQL脚本仅包含一条INSERT INTO语句，示例如下：

INSERT INTO TABLE (col1, col2)
VALUES (a,b), (c,d), (e,f);

我为该表编写了如下INSTEAD OF INSERT触发器，用于为每个插入批次生成唯一的batchID和供下游使用的extraID。想请教：在多用户并发操作时，当前锁机制下batchID是否可能出现重复？如果存在问题，能否给出优化建议？

触发器代码：

CREATE TRIGGER [dbo].[example] 
ON [dbo].[tblExample]
INSTEAD OF INSERT
AS 
BEGIN 
    BEGIN TRANSACTION;

    DECLARE @batchID AS int
    DECLARE @extraID AS int

    SELECT @batchID = COALESCE(MAX(BatchID), 0) + 1  
    FROM tblExample WITH (TABLOCKX, HOLDLOCK)
    
    SELECT @extraID = COALESCE(MAX(nodeID), 0) + 1 
    FROM tblExample WITH (TABLOCKX, HOLDLOCK)

    INSERT INTO [tblExample] WITH (TABLOCK)
        SELECT @batchID, @extraID, col1, col2 
        FROM inserted 
    
    COMMIT TRANSACTION;
END

1. 当前实现下batchID是否会重复？ #

不会重复。你在查询MAX(BatchID)时使用了TABLOCKX（排他表锁）和HOLDLOCK（将锁持有到事务结束），这会强制所有并发事务排队执行：当第一个事务持有表的排他锁时，其他事务必须等待该事务提交后才能获取锁并计算新的batchID，因此不会出现多个事务同时拿到相同MAX(BatchID)的情况。

但这种方案存在明显的性能问题：排他表锁会完全阻塞表上的所有读写操作，在高并发场景下会导致严重的排队等待，系统吞吐量大幅下降。

2. 优化建议 #

（1）使用SQL Server序列（SEQUENCE）替代MAX()+1 #

序列是SQL Server 2012及以上版本支持的对象，专门用于生成连续/非连续的唯一标识，天生支持并发，不需要手动加锁：

-- 创建batchID序列
CREATE SEQUENCE dbo.BatchID_Seq
    AS INT
    START WITH 1
    INCREMENT BY 1;

-- 创建extraID序列
CREATE SEQUENCE dbo.ExtraID_Seq
    AS INT
    START WITH 1
    INCREMENT BY 1;

-- 修改触发器
CREATE TRIGGER [dbo].[example] 
ON [dbo].[tblExample]
INSTEAD OF INSERT
AS 
BEGIN 
    DECLARE @batchID AS int
    DECLARE @extraID AS int

    -- 获取序列值
    SET @batchID = NEXT VALUE FOR dbo.BatchID_Seq;
    SET @extraID = NEXT VALUE FOR dbo.ExtraID_Seq;

    INSERT INTO [tblExample]
        SELECT @batchID, @extraID, col1, col2 
        FROM inserted 
END

优势：无需手动管理锁，并发性能大幅提升，序列值生成是原子操作，天然保证唯一。

（2）使用单独的控制表存储当前最大ID #

创建一张专门的控制表来存储batchID和extraID的当前最大值，然后用UPDLOCK+HOLDLOCK替代表级排他锁，缩小锁范围：

-- 创建控制表
CREATE TABLE dbo.ID_Control (
    ID_Type VARCHAR(20) PRIMARY KEY,
    Current_Value INT NOT NULL DEFAULT 0
);

-- 初始化数据
INSERT INTO dbo.ID_Control (ID_Type, Current_Value)
VALUES ('BatchID', 0), ('ExtraID', 0);

-- 修改触发器
CREATE TRIGGER [dbo].[example] 
ON [dbo].[tblExample]
INSTEAD OF INSERT
AS 
BEGIN 
    DECLARE @batchID AS int
    DECLARE @extraID AS int

    -- 获取并更新BatchID
    UPDATE dbo.ID_Control WITH (UPDLOCK, HOLDLOCK)
    SET Current_Value = Current_Value + 1,
        @batchID = Current_Value + 1
    WHERE ID_Type = 'BatchID';

    -- 获取并更新ExtraID
    UPDATE dbo.ID_Control WITH (UPDLOCK, HOLDLOCK)
    SET Current_Value = Current_Value + 1,
        @extraID = Current_Value + 1
    WHERE ID_Type = 'ExtraID';

    INSERT INTO [tblExample]
        SELECT @batchID, @extraID, col1, col2 
        FROM inserted 
END

优势：锁的范围缩小到控制表的单行记录，而非整个业务表，并发性能比原方案提升很多。

（3）触发器简化优化 #

原触发器中的BEGIN TRANSACTION可以去掉，因为触发器本身会在隐式事务中执行（除非显式指定SET IMPLICIT_TRANSACTIONS ON），显式事务会增加不必要的开销。

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