如何基于指定列生成本地ID？SQL分组自增计数器安全实现

核心需求 #

插入Product表时，依据StoreId分组自动生成唯一的StoreProductId：同一StoreId下从1开始递增，不同StoreId重新从1计数，且必须避免并发插入时出现重复值。

安全实现方案：AFTER INSERT触发器 + 事务锁 #

计算列无法实现该需求，因为它不能依赖表中其他行的聚合值（如COUNT）；普通触发器若不做并发控制，高并发场景下会出现StoreProductId重复。以下是可靠的实现步骤：

1. 确认表结构 #

原表结构满足需求，Id为全局自增主键，StoreProductId用于分组内计数：

CREATE TABLE Product (
    Id int IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    StoreId int, 
    StoreProductId int, 
    ProductName varchar(255)
)

2. 创建并发安全的触发器 #

关键是在更新StoreProductId时，对当前StoreId的行添加**UPDLOCK + HOLDLOCK**锁，防止多事务同时读取相同的计数结果：

CREATE TRIGGER trg_Product_AssignStoreProductId
ON Product
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 为每个插入的行分配对应StoreId的自增计数
    UPDATE p
    SET p.StoreProductId = (
        SELECT COUNT(*)
        FROM Product WITH (UPDLOCK, HOLDLOCK)
        WHERE StoreId = i.StoreId
    )
    FROM Product p
    INNER JOIN inserted i ON p.Id = i.Id;
END

3. 验证插入效果 #

执行插入语句后，会自动生成符合要求的StoreProductId：

-- 插入StoreId=1的第一个产品
INSERT INTO Product(StoreId, ProductName) VALUES(1, 'MyProduct');
-- 结果：Id=1, StoreId=1, StoreProductId=1, ProductName='MyProduct'

-- 插入StoreId=1的第二个产品
INSERT INTO Product(StoreId, ProductName) VALUES(1, 'MyProduct2');
-- 结果：Id=2, StoreId=1, StoreProductId=2, ProductName='MyProduct2'

-- 插入StoreId=2的第一个产品
INSERT INTO Product(StoreId, ProductName) VALUES(2, 'MyProduct3');
-- 结果：Id=3, StoreId=2, StoreProductId=1, ProductName='MyProduct3'

4. 并发安全说明 #

• UPDLOCK：获取更新锁，避免其他事务对同一StoreId的行进行修改或加排他锁
• HOLDLOCK：将锁保持到事务结束，防止其他事务在当前事务完成前读取相同的计数结果
• 该方式可确保高并发场景下，同一StoreId的StoreProductId不会重复

替代方案：序列+自定义函数（仅SQL Server 2012+适用） #

若你的SQL Server版本支持序列，可为每个StoreId创建独立序列，但此方式灵活性较差，需提前为新增StoreId手动创建序列：

-- 为StoreId=1创建序列
CREATE SEQUENCE seq_Store1_ProductId
START WITH 1
INCREMENT BY 1;

-- 插入时调用序列赋值
INSERT INTO Product(StoreId, StoreProductId, ProductName)
VALUES(1, NEXT VALUE FOR seq_Store1_ProductId, 'MyProduct');

注意事项 #

• 禁止直接用MAX(StoreProductId)+1计算，高并发下必然出现重复，必须配合锁机制
• 批量插入时，触发器会自动为每一行分配正确的StoreProductId
• 若需修正历史数据的StoreProductId，可执行类似触发器内的更新语句，按StoreId重新计数

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