Snapshot isolation transaction failed in database 'tempdb'

Question

Upgraded from SQL Server 2008 R2 to SQL Server 2017. In SS 2008 R2 the application would occasionally get this error:

“Snapshot isolation transaction failed in database 'tempdb' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.”

In SS 2017 the error happens much more often. The transaction is being started in .NET specifically setting the Isolation Level to snapshot and it runs a single stored procedure within the transaction using ExecuteNonQuery(); so the only thing going on from the .NET side is the procedure call. I'm just flummoxed as to why the same code is producing many more errors in SS 2017 vs. SS 2008 R2.

There are some temp tables created in the procedure using SELECT INTO #Table. I have recommended that this be changed to explicitly create the temp tables and then insert into them.

I also don't believe that snapshot isolation is necessary in this case and was supposedly added to help avoid deadlocks. I have recommended using READ COMMITTED SNAPSHOT as I think that will solve the problem, but it requires more testing and changing the .NET code to remove the explicit use of snapshot isolation.

The procedure can be run 100's of times per minute.

I'm open to suggestions. I can't share the exact code but could post a simple mock up potentially.

How do I stop the error?

Example .NET code:

using (SqlConnection conn = Timeco.ConnectionMgr.GetConnection()) 
{ 
conn.Open(); 
{ 
    using (SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Snapshot)) 
    { 
        using (SqlCommand cmd = new SqlCommand("proc_name", conn, transaction)) 
        { 
            cmd.CommandTimeout = 120; // avoid timeouts on save 
            cmd.CommandType = CommandType.StoredProcedure; 

            SqlParameter param1 = cmd.Parameters.AddWithValue("@param_name", param_value]); 
            param1.SqlDbType = SqlDbType.Structured; 

            // adds a few more parameters
            cmd.Parameters.Add("@param_anem", SqlDbType.UniqueIdentifier).Value = param_value; 

            cmd.ExecuteNonQuery(); 
        } 

        transaction.Commit(); 
    }
}
}

Answer 1

I've managed to create reliable replication steps for this error.

I have a stored procedure that:

• runs a SELECT statement to output some data from a bunch of real tables
• waits for 1 second using WAITFOR DELAY '00:00:01'
• creates a temporary table
• drops the temporary table

My C# test harness starts a transaction with Snapshot isolation, calls my stored procedure, then commits the transaction. Running this concurrently in 100 threads causes this error to be thrown on every run.

Just removing the dropping of the temporary table removed the error. Changing the DROP to TRUNCATE DID NOT remove the error.

However, this article by Paul White states:

Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS.

which makes these observations very peculiar.