4

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(); 
    }
}
}
1
  • Ideally the errors would go away completely but a reduction back to the frequency seen on 2008R2. As I think about it more the increased frequency could be due to improved performance allowing more executions of this procedure in the same time frame so they are colliding more frequently. Commented Aug 30, 2018 at 13:30

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.