如何在READ_COMMITTED_SNAPSHOT模式下实现显式表读锁定？

我不确定如何在READ_COMMITTED_SNAPSHOT（简称RCS）模式下显式锁定表来阻止读取。

根据我的认知：
当READ_COMMITTED_SNAPSHOT为OFF时：

BEGIN TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 可以锁定表阻止读取（符合预期）
select LastOrderNumber from IncrementalNumbers WITH (TABLOCKX);

-- 执行后端其他操作

-- 可以锁定表阻止读取（符合预期）
update Orders set Reference = 'ORD-001';
-- 表已被锁定，无需担心并发问题
update IncrementalNumbers set LastOrderNumber = 1;

COMMIT

当READ_COMMITTED_SNAPSHOT为ON时：

BEGIN TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 即使指定了WITH (TABLOCKX)，也无法锁定表阻止读取（不符合预期）
select LastOrderNumber from IncrementalNumbers WITH (TABLOCKX);

-- 执行后端其他操作

-- 无法锁定表阻止读取（符合RCS特性）
update Orders set Reference = 'ORD-001';
-- 本应被上面的SELECT锁定，但实际未生效
update IncrementalNumbers set LastOrderNumber = 1;

COMMIT

问题：如何在启用READ_COMMITTED_SNAPSHOT模式的同时，在特定场景下锁定表以阻止读取？比如避免增量数值（如[LastOrderNumber]）被读取后覆盖，这是否可行？

要在RCS启用的情况下实现锁表阻止读取，核心是强制使用锁机制而非快照读取，以下是几种可行方案：

1. 使用READCOMMITTEDLOCK查询提示 #

当RCS开启时，READ COMMITTED隔离级别默认采用快照读取，此时可在查询中显式指定READCOMMITTEDLOCK提示，强制切换为传统锁机制，配合TABLOCKX即可实现锁表阻止读取：

BEGIN TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 强制使用锁而非快照，同时添加排他表锁
select LastOrderNumber from IncrementalNumbers WITH (TABLOCKX, READCOMMITTEDLOCK);

-- 执行后端其他操作

update Orders set Reference = 'ORD-001';
update IncrementalNumbers set LastOrderNumber = 1;

COMMIT

2. 提升事务隔离级别 #

将事务隔离级别提升至REPEATABLE READ或SERIALIZABLE，这两个级别在RCS开启时不会使用快照读取，默认采用锁机制，配合TABLOCKX可实现锁表：

BEGIN TRAN

-- 提升隔离级别到REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

select LastOrderNumber from IncrementalNumbers WITH (TABLOCKX);

-- 执行后端其他操作

update Orders set Reference = 'ORD-001';
update IncrementalNumbers set LastOrderNumber = 1;

COMMIT

3. 合并读写操作（推荐） #

如果你的场景是读取增量值后立即更新，可将读取和更新合并为一条语句，通过OUTPUT子句获取旧值，这样既能自动获取排他锁，又能避免先读后写的竞态窗口：

BEGIN TRAN

DECLARE @LastNum INT;

-- 通过UPDATE获取排他锁，同时读取旧值并更新
UPDATE IncrementalNumbers
SET @LastNum = LastOrderNumber, LastOrderNumber = 1
WHERE [你的主键条件];

-- 使用读取到的增量值执行后续操作
update Orders set Reference = 'ORD-' + RIGHT('000' + CAST(@LastNum AS VARCHAR(3)), 3);

COMMIT

关于增量数值场景的可行性 #

完全可行，上述方法都能有效避免并发读取LastOrderNumber后被覆盖的问题。其中第三种合并读写的方案效率最高，因为它缩短了锁的持有时间，消除了中间的竞争窗口。

内容的提问来源于stack exchange，提问作者ADM-IT