SQL2008显式事务中执行表重命名的并发锁影响问题咨询

场景1：换表窗口期（DROP/RENAME的毫秒级窗口）内新查询的行为 #

• 换表事务执行DDL（DROP/RENAME）时，会对目标表（dbo.MyTable）加Sch-M（架构修改）锁，该锁与所有查询需要的Sch-S（架构稳定）锁不兼容。
• 默认情况下，SQL Server的锁超时为-1（无限等待），新查询会进入锁等待队列：由于换表事务仅执行轻量DDL，耗时仅毫秒级，事务提交后Sch-M锁立即释放，新查询会正常获取Sch-S锁并访问新的MyTable，不会直接报错。
• 仅当查询连接显式设置了小于换表事务耗时的*LOCK_TIMEOUT*参数时，才会触发锁超时错误。

场景2：换表事务启动时已有SELECT正在运行的行为 #

• 正在运行的SELECT会提前对MyTable加Sch-S（架构稳定）锁，持有周期为整个查询执行周期（数千条数据的查询仅需微秒/毫秒级）。
• 换表事务执行DROP TABLE MyTable时需要获取Sch-M锁，会被现有Sch-S锁单向阻塞（SELECT不需要换表事务持有的任何锁，无循环依赖）。
• 通常不会回滚：阻塞仅持续到SELECT执行完成（毫秒级），之后换表事务可正常获取锁并执行。仅当触发锁超时、发生极罕见的死锁（如登台表被意外访问）或手动杀进程时，事务才会回滚。

• 登台表名硬编码冲突：原代码固定使用Temp_MyTable_Staging，若上一次任务在SELECT INTO后、事务执行前崩溃，下一次执行会因表已存在报错。
• 直接DROP的风险：事务回滚时虽能恢复旧表，但无临时过渡表作为回滚校验，排查问题难度大。

核心思路：用唯一时间戳命名登台表/旧表，原子切换后再清理旧表，避免硬编码冲突。

-- 1. 生成带时间戳的唯一表名（避免冲突）
DECLARE 
    @StagingName NVARCHAR(128) = N'dbo.Temp_MyTable_' + REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 120), '-', ''), ' ', '_'), ':', ''),
    @OldName NVARCHAR(128) = N'dbo.Old_MyTable_' + REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 120), '-', ''), ' ', '_'), ':', ''),
    @ExecSQL NVARCHAR(MAX);

-- 2. 抽取数据到唯一登台表（自动提交，失败不影响正式表）
SET @ExecSQL = N'SELECT fieldlist INTO ' + @StagingName + N' FROM FOOBAR;';
EXEC sp_executesql @ExecSQL;

-- 3. 原子切换（事务保证元数据操作原子性）
BEGIN TRANSACTION
BEGIN TRY
    -- 旧表重命名为临时旧表（而非直接DROP，保留回滚余地）
    IF OBJECT_ID(N'dbo.MyTable', N'U') IS NOT NULL
        EXEC sp_rename N'dbo.MyTable', @OldName;

    -- 新登台表重命名为正式表
    EXEC sp_rename @StagingName, N'MyTable';

    COMMIT TRANSACTION;

    -- 4. 提交后清理旧表（不阻塞查询）
    SET @ExecSQL = N'DROP TABLE ' + @OldName + N';';
    EXEC sp_executesql @ExecSQL;
END TRY
BEGIN CATCH
    -- 回滚事务，恢复旧表
    ROLLBACK TRANSACTION;
    -- 清理失败的新登台表
    SET @ExecSQL = N'IF OBJECT_ID(''' + @StagingName + N''', N''U'') IS NOT NULL DROP TABLE ' + @StagingName + N';';
    EXEC sp_executesql @ExecSQL;
    -- SQL Server 2008用RAISERROR抛出错误，替代2012+的THROW
    RAISERROR(N'表切换失败，已回滚', 16, 1);
END CATCH

• 由于数据集仅数千条，SELECT的Sch-S锁持有时间极短，换表事务的阻塞延迟可忽略，无需调整锁超时参数。
• 若需进一步消除阻塞，可开启快照隔离级别（SQL Server 2008支持），让查询读取旧表的快照版本，不持有Sch-S锁阻塞DDL。

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