Sql Server SET TRANSACTION ISOLATION LEVEL READ COMMITTED does not appear to work with pyodbc – PHP Versions

Short Summary
I am running multiple sql queries (each committed separately) within one session via pyodbc. In a few queries we call SET TRANSACTION ISOLATION LEVEL SNAPSHOT;, begin a transaction, do some work, commit the transaction and then call SET TRANSACTION ISOLATION LEVEL READ COMMITTED; But even though we have set the transaction isolation level back to READ COMMITTED we get the error

pyodbc.ProgrammingError: (‘42000’, ‘[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation. (3964) (SQLExecDirectW)’)

I don’t understand why we’re getting this error when we’re no longer within snapshot isolation.

Full Details

I am migrating a large legacy SQL process from PHP to Python. Briefly, a PHP job calls a series of SQL statements in order (all within a single session) to populate several dozen large tables. This includes many intermediary steps with temp tables. (We are in the process of decoupling ourselves from this legacy process but for now we are stuck with it.)

I’m moving that legacy process into Python for maintainability reasons, using pyodbc. While this has been largely painless I am finding a strange difference in behaviors from PHP to Python around TRANSACTION ISOLATION LEVEL.

Early in the process we switch to ISOLATION LEVEL SNAPSHOT:

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRANSACTION;
  DECLARE @current_refresh_id BIGINT = :current_refresh_id;
  DECLARE @CurRowID INT = 1;
  DECLARE @TotalCount INT = (SELECT COUNT(*) FROM #product_data);

  WHILE (1 = 1)
  BEGIN
    -- a complex insert into a table tblSomeTableOne using joins, etc, done in batches
  END
 COMMIT TRANSACTION;
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    

We then call many other SQL statements with no issue. I’ve added a query before each of them to verify that we’re using ReadCommited transaction level after the above SQL statement (taken from this answer):

SELECT CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID;

The query shows that the transaction level is in fact ReadCommitted.

However, later in the code I run this DDL on a temp table that has already been created:

      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
      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;

This fails with the following exception:

pyodbc.ProgrammingError: (‘42000’, ‘[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation. (3964) (SQLExecDirectW)’)

This confuses me because if I check the isolation level immediately prior to this error, I get ReadCommitted, not Snapshot.

For context pyodbc is running with autocommit=True, all our SQL statements are executed as part of a single session. These SQL statements work fine in PHP and they work in python/pyodbc as well in limited test cases, but they fail when running our "full" legacy process in python/pyodbc. (The only difference between test cases and the full process is the amount of data, the SQL is identical.)

Apologies for not including a fully reproducible example but the actual legacy process is massive and proprietary.

Update One
I added a query to check the transaction state to see if autocommit has somehow been disabled, causing us to be stuck in the SNAPSHOT transaction.

IF @@TRANCOUNT = 0 SELECT 'No current transaction, autocommit mode (default)'

ELSE IF @@OPTIONS & 2 = 0 SELECT ‘Implicit transactions is off, explicit transaction is currently running’
ELSE SELECT ‘Implicit transactions is on, implicit or explicit transaction is currently running’

When I run my queries over a limited dataset (~16000 records) All my queries run with autocommit mode. But when I run my queries over the full dataset (~3 million records), one of the queries that uses a SNAPSHOT isolation transaction changes subsequent queries to Implicit transactions is off, explicit transaction is currently running. So They’re still stuck in that query’s snapshot transaction even though that transaction is told to commit.

Here is a modified version of that query:

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
--dramatically simplified from the real query
      ;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;
     SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

What I don’t understand is why the above query will successfully commit the transaction and set the isolation level back to READ COMMITTED when running on a smaller set (16k records) but it doesn’t actually commit the transaction on larger sets (~3M records).

(We do the OPTION RECOMPILE because the actual query in this loop performs very poorly without recompile, because the number of records etc can change dramatically between executions.)