PyODBC设置READ COMMITTED后仍触发快照隔离DDL错误

问题核心 #

在大数据量场景下，SNAPSHOT隔离级别事务提交后，SQL Server会话的事务上下文可能因行版本清理延迟或隐式事务残留，仍被标记为快照隔离状态——即使显式切换回READ COMMITTED，后续临时表DDL操作仍会触发3964错误，因为元数据操作不允许在快照隔离事务上下文内执行。

解决方案 #

1. 强制清理会话事务上下文 #

修改SNAPSHOT事务的SQL，在提交后添加事务残留检查，并执行空事务刷新会话状态：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
DECLARE @my_param BIGINT = :my_param;
DECLARE @CurRowID INT = 1;
DECLARE @TotalCount INT = (SELECT COUNT(*) FROM #product_data);
WHILE (1 = 1)
BEGIN
      ;WITH some_data AS (
      SELECT t1.A, t1.B FROM #temp_table_1 t1
      WHERE t1.RowNum BETWEEN @CurRowID AND @CurRowID + :batch_size - 1
    )
    INSERT INTO tblMyTable (A, B, Param)
    SELECT some_data.A, 
    some_data.B,
    @my_param
    FROM some_data
    
    OPTION(RECOMPILE,MAXDOP 8)
    SET @CurRowID += :batch_size;
    IF @CurRowID > @TotalCount BREAK;
    WAITFOR DELAY :wait_time;
END
COMMIT TRANSACTION;
-- 强制关闭所有残留事务
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
-- 切换隔离级别后执行空事务，刷新会话状态
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
COMMIT TRANSACTION;

2. 拆分数据库连接隔离上下文 #

将SNAPSHOT批量插入操作与DDL操作放在两个独立的pyodbc连接中执行，彻底隔离会话的事务状态：

import pyodbc

# 连接1：执行SNAPSHOT事务
conn_snapshot = pyodbc.connect(connection_string)
cursor_snapshot = conn_snapshot.cursor()
cursor_snapshot.execute(snapshot_sql, params)
conn_snapshot.commit()
cursor_snapshot.close()
conn_snapshot.close()

# 连接2：执行DDL操作
conn_ddl = pyodbc.connect(connection_string)
cursor_ddl = conn_ddl.cursor()
cursor_ddl.execute(ddl_sql)
conn_ddl.commit()
cursor_ddl.close()
conn_ddl.close()

3. 优化DDL执行逻辑 #

移除DDL SQL中重复的隔离级别设置（已在会话中完成切换），确保DDL事务独立且干净：

BEGIN TRANSACTION;
ALTER TABLE #already_populated_temp_table ADD RowNum INT IDENTITY;
CREATE UNIQUE INDEX ix_psi_RowNum ON #already_populated_temp_table (RowNum);
ALTER INDEX ALL ON #already_populated_temp_table REBUILD;
COMMIT TRANSACTION;

关键原因说明 #

大数据量的SNAPSHOT事务会生成大量行版本，事务提交后SQL Server需要时间清理这些版本，此时会话的事务上下文可能仍保留快照隔离的标记。显式执行空事务或拆分连接，可强制刷新会话状态，避免DDL操作误判为快照隔离事务内的操作。

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