如何编写SQL安全获取并更新可用文档参考编号？

你的核心需求是原子性地获取并标记可用参考编号，避免并发冲突。先直接给结论：仅用BEGIN/COMMIT事务块包裹普通的SELECT+UPDATE语句是不可行的，因为默认事务隔离级别无法阻止多个事务同时读取到同一可用编号，最终导致重复分配。

推荐方案1：原子化UPDATE+OUTPUT（最简洁可靠） #

利用SQL的UPDATE操作本身的原子性，直接在更新时返回被选中的编号，全程由数据库保证锁机制，彻底避免并发冲突：

BEGIN TRANSACTION;

DECLARE @AssignedRefN SMALLINT;

-- 一次性完成"选中可用编号+标记为已占用"的原子操作
UPDATE TOP(1) RefNum
SET avail = 1
OUTPUT INSERTED.refN INTO @AssignedRefN
WHERE avail = 0;

-- 后续可以使用@AssignedRefN进行业务逻辑处理
-- 比如插入到业务表：INSERT INTO Documents(refN, ...) VALUES(@AssignedRefN, ...)

COMMIT TRANSACTION;

推荐方案2：显式行锁+SELECT+UPDATE #

如果需要先读取编号再做其他判断，可通过UPDLOCK和ROWLOCK提示锁定选中的行，阻止其他事务读取：

BEGIN TRANSACTION;

DECLARE @AssignedRefN SMALLINT;

-- 锁定目标行，避免其他事务同时读取
SELECT TOP(1) @AssignedRefN = refN
FROM RefNum WITH (UPDLOCK, ROWLOCK)
WHERE avail = 0;

-- 标记为已占用
UPDATE RefNum
SET avail = 1
WHERE refN = @AssignedRefN;

COMMIT TRANSACTION;

为什么单纯BEGIN/COMMIT不行？ #

假设你写了这样的代码：

BEGIN TRANSACTION;
SELECT TOP(1) @refN = refN FROM RefNum WHERE avail=0;
UPDATE RefNum SET avail=1 WHERE refN=@refN;
COMMIT;

在默认的READ COMMITTED隔离级别下，两个并发事务可能都在SELECT阶段读到同一个avail=0的编号，之后各自执行UPDATE，最终导致同一个编号被分配两次。事务块只是保证了SELECT和UPDATE的原子性，但无法阻止并发读取未锁定的行。

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