如何以线程安全方式插入带通用标识的SQL Server数据库记录？

1. 这段代码确实线程不安全 #

你的判断完全正确，当前代码存在并发冲突风险。当多个用户同时执行这段SQL时，很容易出现以下场景：

• 会话A执行select max(Reference) from PickReference拿到最大值N，计算出N+1；
• 同一时间会话B执行同样的查询，也拿到N，算出同一个N+1；
• 两个会话最终都会用N+1作为通用标识插入数据，导致PickReference表中出现重复的Reference值，破坏数据唯一性。
  哪怕事务很短，只要并发量上来，这种冲突几乎一定会发生。

2. 解决方法 #

方法一：改用IDENTITY列（推荐） #

把PickReference表的Reference字段改成IDENTITY类型，让SQL Server自动生成唯一标识，彻底避免手动计算的并发问题：

1. 先修改表结构（仅需执行一次）：

ALTER TABLE PickReference 
ALTER COLUMN Reference INT IDENTITY(1,1) PRIMARY KEY;

2. 调整插入逻辑，先插入一条记录获取标识，再批量插入关联数据：

SET XACT_ABORT ON
BEGIN TRAN

DECLARE @MultiOrderReference AS INT
-- 插入第一条实际记录，获取自动生成的标识
INSERT INTO PickReference (Barcode, Amount) VALUES ('0068518000', 1)
SET @MultiOrderReference = SCOPE_IDENTITY()
-- 插入剩余关联记录
INSERT INTO PickReference (Reference, Barcode, Amount) 
VALUES (@MultiOrderReference, '0068548000', 4),
       (@MultiOrderReference, '0068550000', 8)

SELECT @MultiOrderReference AS Reference
COMMIT TRAN

这种方式由数据库全权管理标识生成，完全不会有并发重复的问题。

方法二：使用SEQUENCE对象（适合自定义标识规则） #

如果需要更灵活的标识规则（比如指定起始值、步长），可以创建SEQUENCE对象：

1. 创建SEQUENCE（仅需执行一次）：

CREATE SEQUENCE PickReferenceSeq 
START WITH 1
INCREMENT BY 1
AS INT;

2. 修改插入逻辑，通过序列获取唯一值：

SET XACT_ABORT ON
BEGIN TRAN

DECLARE @MultiOrderReference AS INT
-- 获取序列的下一个唯一值
SET @MultiOrderReference = NEXT VALUE FOR PickReferenceSeq
-- 批量插入所有关联记录
INSERT INTO PickReference (Reference, Barcode, Amount) 
VALUES (@MultiOrderReference, '0068518000', 1),
       (@MultiOrderReference, '0068548000', 4),
       (@MultiOrderReference, '0068550000', 8)

SELECT @MultiOrderReference AS Reference
COMMIT TRAN

SEQUENCE是全局唯一的，每次调用NEXT VALUE FOR都会返回不重复的值，并发场景下绝对安全。

方法三：加锁提示（不推荐，性能差） #

如果暂时不想改表结构，可以在查询max值时加锁，强制串行化访问：

SET XACT_ABORT ON
BEGIN TRAN

DECLARE @MultiOrderReference AS INT
-- 加UPDLOCK和HOLDLOCK，确保同一时间只有一个会话能读取max值
SET @MultiOrderReference = (SELECT MAX(Reference) FROM PickReference WITH (UPDLOCK, HOLDLOCK)) + 1
INSERT INTO PickReference (Reference, Barcode, Amount) 
VALUES (@MultiOrderReference, '0068518000', 1),
       (@MultiOrderReference, '0068548000', 4),
       (@MultiOrderReference, '0068550000', 8)

SELECT @MultiOrderReference AS Reference
COMMIT TRAN

这种方式会对表加锁，大幅降低并发性能，只适合并发量极低的场景。

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