SQL长事务重建表时允许并发查询读取旧数据的配置问询

你现在写的「事务内直接DROP+重建表」的逻辑，不管改什么事务隔离级别、加不加表提示，都达不到你要的效果。

核心原因：DROP TABLE操作会直接给表加Sch-M（架构修改）锁，锁持有期间任何访问该表的请求都会被阻塞，不管你用什么隔离级别、加不加NOLOCK都绕不开这个锁；而且DROP执行完原表旧数据就已经被标记回收，根本没有旧版本可以给其他查询读。

1. 必须改重建逻辑，别在长事务里碰原表 #

不要在长事务里删原表，换成「影子表+短事务元数据切换」的模式，全程对读查询无影响：

• 不在事务里，先建一张和目标表结构完全一致的影子表，比如命名为IReallyWantToQueryThisTable_Staging
• 所有耗时的全量数据加载操作，全部往这张影子表里插，全程不碰正式表，其他查询读正式表完全不受阻塞，加载跑多久都没关系
• 等影子表数据全部加载完成、校验没问题之后，开一个极短的事务，只做表名切换的元数据操作：

  BEGIN TRAN
    -- 把旧正式表改名成备份表
    EXEC sp_rename 'IReallyWantToQueryThisTable', 'IReallyWantToQueryThisTable_Old';
    -- 把影子表改成正式表名
    EXEC sp_rename 'IReallyWantToQueryThisTable_Staging', 'IReallyWantToQueryThisTable';
  COMMIT TRAN
  

  这个切换是纯元数据操作，毫秒级就能完成，阻塞时间可以忽略
• 事务提交后，确认业务正常，再删掉旧的备份表就行

2. 需要开的配置 #

只需要给数据库开启*读提交快照隔离（RCSI）*即可：

ALTER DATABASE 你的业务库名 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

开了这个配置之后，默认的READ COMMITTED隔离级别下，所有普通SELECT语句自动基于行版本读取数据：

• 不会被写操作的排他锁阻塞，不需要等写事务提交就能返回结果
• 永远读取语句启动时已经提交的最新版本数据，不会读到未提交的脏数据，完全匹配你的需求
• 不需要改任何业务查询的代码，不用加任何表提示

3. 关于WITH (NOLOCK)的问题 #

不要加NOLOCK：

• NOLOCK对应的是读未提交隔离级别，会出现脏读、重复读、数据缺失，甚至查询过程中因为对象被修改直接报错的问题，一致性完全没有保障
• 开了RCSI之后，不需要加任何提示就能实现无阻塞一致性读，NOLOCK没有任何价值
• 就算你硬加NOLOCK，碰到Sch-M锁（比如你原逻辑里的DROP操作）照样会被阻塞，解决不了根本问题

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