如何为单条SQL Server查询启用opportunistic locking？

单SELECT语句的启用方法 #

无需修改服务器/数据库级默认配置，两种方式实现单查询级乐观快照读取：

• 临时会话隔离级别设置
  先将当前会话的事务隔离级别切换为SNAPSHOT，执行目标查询后可按需恢复原级别：

  -- 切换到快照隔离级别
  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  -- 你的全表扫描查询
  SELECT * FROM YourTable WHERE [无索引过滤条件];
  
  -- 可选：恢复默认的READ COMMITTED级别
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

  该设置对当前会话内后续查询生效，直到你重新修改隔离级别。

• 单查询表提示
  直接在SELECT语句中添加WITH (SNAPSHOT)表提示，仅对这条查询生效：

  SELECT * FROM YourTable WITH (SNAPSHOT) WHERE [无索引过滤条件];
  

  注意：需先一次性启用数据库的快照隔离支持（不修改默认隔离级别）：

  ALTER DATABASE YourDBName SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  此操作仅开启快照隔离的支持能力，不会改变数据库默认的事务行为。

SELECT与UPDATE的启用差异 #

你只需要在SELECT中应用乐观锁逻辑即可——UPDATE操作必须获取排他锁来保证数据一致性，乐观锁机制不适用于写操作，因此无需对UPDATE做任何额外设置。

死锁解决原理 #

你的死锁源于：全表扫描的SELECT会在扫描过程中对大量行加共享锁，主键UPDATE的排他锁与这些共享锁相互阻塞。启用乐观快照读取后，SELECT会读取数据的版本快照，不会添加任何共享锁，彻底避免与UPDATE的锁冲突，从而解决这类死锁。

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