基于其他字段的自增列触发器并发重复值问题咨询

问题原因 #

你的触发器逻辑存在并发漏洞：当多个事务同时插入相同templateName的记录时，它们会在各自的事务中执行SELECT MAX(position)，此时由于事务未提交，彼此无法看到对方的position更新，因此会读取到同一个最大值，最终都将position设置为max+1，导致重复值出现。

默认的READ COMMITTED事务隔离级别只能保证读取已提交的数据，但无法解决这种"读取-修改-写入"（Read-Modify-Write）的并发冲突。即使提升到REPEATABLE READ或更高的隔离级别，也只能保证单个事务内的读取一致性，无法阻止多个独立事务读取到相同的初始最大值。SERIALIZABLE隔离级别虽然能避免，但会强制事务串行执行，严重降低并发性能，不适合高并发场景。

解决方案 #

推荐使用独立计数器表+原子更新的方式，确保获取下一个position的操作是原子性的，避免并发冲突：

1. 先创建计数器表，存储每个templateName的当前最大position：

CREATE TABLE TemplatePositionCounter (
    templateName VARCHAR(100) PRIMARY KEY,
    currentMaxPosition SMALLINT DEFAULT 0
)

2. 修改触发器逻辑，通过原子更新计数器来获取唯一的position值：

CREATE TRIGGER [dbo].[setPosition]
ON [dbo].[myTable]
AFTER INSERT
AS 
BEGIN
    DECLARE @newPosition SMALLINT
    DECLARE @templateName VARCHAR(100)
    DECLARE @id INT

    SELECT @id = id, @templateName = templateName FROM inserted

    -- 原子更新计数器，同时获取下一个position值
    UPDATE TemplatePositionCounter
    SET currentMaxPosition = currentMaxPosition + 1,
        @newPosition = currentMaxPosition + 1
    WHERE templateName = @templateName

    -- 处理新的templateName，初始化计数器并设置position为1
    IF @@ROWCOUNT = 0
    BEGIN
        SET @newPosition = 1
        INSERT INTO TemplatePositionCounter (templateName, currentMaxPosition)
        VALUES (@templateName, 1)
    END

    UPDATE [myTable]
    SET position = @newPosition WHERE id = @id  
END

额外保障 #

为了彻底防止重复值，建议给myTable添加唯一约束：

ALTER TABLE [dbo].[myTable]
ADD CONSTRAINT UQ_myTable_TemplateName_Position UNIQUE (templateName, position)

这样即使出现极端异常情况，数据库也会直接报错，避免脏数据入库。

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