Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 972915 - Last Review: July 9, 2009 - Revision: 1.1
The transaction isolation level is not reset when you reuse a connection from the connection pool
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.
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.
This behavior is by design.
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();
}
}
}
APPLIES TO
- Microsoft .NET Framework 2.0
| kbexpertiseadvanced kbtshoot KB972915 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate