事务中替换目标表数据且不阻塞INFORMATION_SCHEMA.TABLES查询

问题背景 #

数据仓库通过CRON定时任务从ERP同步临时表，需要高效完成源表到目标表的数据更新，核心要求：

• 不阻塞目标表、源表的正常查询
• 不影响对源/目标库INFORMATION_SCHEMA视图的访问
  当前采用的流程存在明显问题：事务内重建目标表会产生数据库级锁，导致1-2分钟内无法查询INFORMATION_SCHEMA.TABLES。考虑拆分多事务在BEGIN TRY...END TRY中执行，仅在DDL操作时短时间持有锁，但不确定能否保留全局回滚的容错能力。

一、列数可变表的最优更新方案 #

核心思路是拆分长事务为多个短事务，将DDL操作与数据加载、切换分离，最小化锁持有时间：

1. 结构校验与变更（短事务）
   • 单独开启事务，对比源表与目标表的列结构（新增列、数据类型调整等）
   • 若有结构差异，仅在这个短事务内执行目标表的ALTER TABLE操作，完成后立即提交
   • 此步骤锁持有时间极短，基本不会阻塞INFORMATION_SCHEMA查询
2. 数据加载（无锁长操作）
   • 直接创建/复用与目标表结构一致的加载表，批量导入源表数据
   • 此阶段仅操作加载表，完全不影响目标表和系统视图的正常访问
3. 原子数据切换（短事务）
   • 开启短事务，执行ALTER TABLE data_load_table SWITCH TO target_table（需确保两表分区配置匹配）
   • 提交事务后，目标表已无缝切换为新数据
4. 失败补偿机制
   • 在BEGIN TRY中按顺序执行上述步骤，若某一步失败：
     • 若DDL事务已提交，需根据提前记录的变更日志，手动回滚列修改（比如删除新增列）
     • 若数据加载失败，直接清空或删除加载表即可
     • 若切换失败，回滚该短事务，目标表仍保留原数据

二、列数固定表的通用最优方法 #

针对结构稳定的表，采用加载表+原子切换的无锁方案，全程避免长事务：

1. 提前创建与目标表结构完全一致的加载表（可复用，每次更新前清空数据）
2. 批量导入源表数据到加载表（无需事务，不阻塞任何查询）
3. 开启短事务，执行ALTER TABLE ... SWITCH完成数据切换，提交事务
4. 若加载或切换失败，直接丢弃加载表数据，目标表不受任何影响

多事务的回滚能力说明 #

拆分多个事务后，无法实现全局自动回滚，但可以通过以下方式实现容错：

• 提前将所有待执行的变更（比如DDL语句、数据加载状态）记录到日志表
• 在BEGIN CATCH块中，根据日志记录的已完成步骤，执行反向补偿操作：
  • 若已执行目标表列新增，就删除对应新增列
  • 若已完成数据加载，就清空加载表或删除加载表
  • 若切换未完成，无需额外操作，目标表仍为原数据

参考代码结构 #

BEGIN TRY
    -- 步骤1：短事务处理结构变更（仅列数可变表需要）
    BEGIN TRANSACTION schema_transaction;
    -- 此处添加源表与目标表结构对比逻辑，执行ALTER TABLE添加/修改列
    COMMIT TRANSACTION schema_transaction;

    -- 步骤2：数据加载（无事务，不阻塞查询）
    TRUNCATE TABLE data_load_table; -- 复用已创建的加载表
    INSERT INTO data_load_table SELECT * FROM source_table;

    -- 步骤3：短事务执行数据切换
    BEGIN TRANSACTION switch_transaction;
    ALTER TABLE data_load_table SWITCH TO target_table;
    COMMIT TRANSACTION switch_transaction;
END TRY
BEGIN CATCH
    -- 回滚未提交的事务
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    -- 执行补偿操作：根据日志回滚结构变更、清理加载表
    DECLARE @errorMsg NVARCHAR(4000) = ERROR_MESSAGE();
    RAISERROR('更新失败：%s', 16, 1, @errorMsg);
    -- 此处可添加日志记录与具体补偿逻辑
END CATCH;

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