You are currently offline, waiting for your internet to reconnect

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

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: 07/09/2009 15:21:31 - Revision: 1.1

Microsoft .NET Framework 2.0

  • kbexpertiseadvanced kbtshoot KB972915
Feedback