The transaction isolation level is not reset when you reuse a connection from the connection pool

Article translations Article translations
Article ID: 972915 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

When you use the System.Data.SqlClient.SqlConnection class, and you reuse a connection from the connection pool, commands are run with a previously specified transaction isolation level. The level is not reset as expected.

WORKAROUND

To work around this problem, use any of the following methods:
  • Specify the transaction isolation level explicitly.
  • Use the TransactionOption property if you use the TransactionScope class.
  • Specify the IsolationLevel enum value if you use the SqlTransaction class.
  • Execute the "SET TRANSACTION ISOLATION LEVEL" statement by using the ExecuteNonQuery method if you do not open an explicit transaction.

STATUS

This behavior is by design.

MORE INFORMATION

Sample code to reproduce the problem

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Transactions;

namespace TxIsolationLevelTest
{
    class Program
    {
        // 0 = Unspecified
        // 1 = ReadUncomitted
        // 2 = ReadCommitted
        // 3 = Repeatable
        // 4 = Serializable
        // 5 = Snapshot

        static void Main(string[] args)
        {
            NoTxScope();
            TxScope(IsolationLevel.Serializable);
            NoTxScope();
            Console.ReadLine();
        }

        static void TxScope(IsolationLevel isolationLevel)
        {
            TransactionOptions op = new TransactionOptions();
            op.IsolationLevel = isolationLevel;
            using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, op))
            {
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True;");
                SqlCommand com = new SqlCommand("select transaction_isolation_level from sys.dm_exec_sessions where (session_id = @@SPID)", con);
                con.Open();
                short level = (short)com.ExecuteScalar();
                Console.WriteLine("transaction_isolation_level : " + level.ToString());
                con.Close();
                tx.Complete();
            }
        }

        static void NoTxScope()
        {
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True;");
            SqlCommand com = new SqlCommand("select transaction_isolation_level from sys.dm_exec_sessions where (session_id = @@SPID)", con);
            con.Open();
            short level = (short)com.ExecuteScalar();
            Console.WriteLine("transaction_isolation_level : " + level.ToString());
            con.Close();
        }
    }
}

Properties

Article ID: 972915 - Last Review: July 9, 2009 - Revision: 1.1
APPLIES TO
  • Microsoft .NET Framework 2.0
Keywords: 
kbexpertiseadvanced kbtshoot KB972915

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com