3

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(); 
    }
}
}
  • 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. – Jack Corbett Aug 30 '18 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.