SSMS中查询TABLE1未加锁记录的可行替代方案咨询

问题根因说明 #

你遇到的READPAST返回全表记录的核心原因是：默认配置下SQL Server启用了读提交快照隔离(RCSI)，只读查询默认走版本存储读取行的历史版本，不会主动请求共享锁，因此不会和会话1持有的排他行锁产生冲突，READPAST自然不会触发跳过逻辑。

可用实现方案 #

方案1：强制查询请求更新锁配合READPAST #

对会话2的查询增加UPDLOCK提示，强制查询对所有行请求更新锁，更新锁和排他锁(X)冲突，此时READPAST就会跳过无法获取锁的行，仅返回未锁定的记录：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM TABLE1 WITH (UPDLOCK, READPAST);

该方案性能开销低，无需查询系统视图，适合大多数业务场景使用。

方案2：通过系统动态视图直接过滤锁定行 #

通过SQL Server内置的事务锁管理视图，先查询出TABLE1上当前所有持有的排他行锁对应的资源标识，再通过行内置的锁资源哈希字段%%lockres%%过滤排除锁定行：

SELECT * FROM TABLE1
WHERE %%lockres%% NOT IN (
    SELECT 
        l.resource_description
    FROM sys.dm_tran_locks l
    JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
    JOIN sys.tables t ON p.object_id = t.object_id
    WHERE 
        t.name = 'TABLE1'
        AND l.resource_type = 'KEY'
        AND l.request_mode = 'X'
)

该方案可以直观识别被锁定的具体行，适合需要明确锁定明细的排查场景，注意%%lockres%%是SQL Server内部字段，仅适用于有聚集索引的表或堆表。

方案3：关闭会话级快照隔离 #

如果不想修改查询提示，也可以在会话2中关闭快照隔离，让只读查询默认请求共享锁，此时原有的READPAST逻辑即可正常生效：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET ALLOW_SNAPSHOT_ISOLATION OFF;
SELECT * FROM TABLE1 WITH (READPAST);

该方案会改变会话的隔离行为，可能影响其他业务逻辑，不建议随意使用。

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