TransactionScope and Connection Pooling

I'm trying to get a handle on whether we have a problem in our application with database connections using incorrect IsolationLevels. Our application is a .Net 3.5 database app using SQL Server 2005.

I've discovered that the IsolationLevel of connections are not reset when they are returned to the connection pool (see here) and was also really surprised to read in this blog post that each new TransactionScope created gets its own connection pool assigned to it.

Our database updates (via our business objects) take place within a TransactionScope (a new one is created for each business object graph update). But our fetches do not use an explicit transaction. So what I'm wondering is could we ever get into the situation where our fetch operations (which should be using the default IsolationLevel - Read Committed) would reuse a connection from the pool which has been used for an update, and inherit the update IsolationLevel (RepeatableRead)? Or would our updates be guaranteed to use a different connection pool seeing as they are wrapped in a TransactionScope?

开发者_开发问答Thanks in advance,

Graham

---

That is worrying!

Bill Vaughan's article you linked to states that '...each TransactionScope gets its own pool', but the code in the support article you linked to, would suggest that is not true, since second run of NoTxScope() gets the connection from the pool which used the elevated Isolation level.

You can 'force' the issue by [I'm drawing on the code from the first link]:

static void ForceReadCommitedScope()
{
    TransactionOptions op = new TransactionOptions();
    op.IsolationLevel = IsolationLevel.ReadCommitted;
    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();
    }
}

or by adding "..;Pooling=False" to your connection string.

---

In SQL Server 2014 the isolation level for pooled connection is reset when connection is returned to the pool. In earlier versions it is not.

See this forum post:

"in SQL 2014, for client drivers with TDS version 7.3 or higher, SQL server will reset transaction isolation level to default (read committed) for pooled connections. for clients with TDS version lower than 7.3 they will have the old behavior when running against SQL 2014."

Update

This again changed back to previous behavior in SQL 2014 CU6 and SQL 2014 SP1 CU1 with this fix:

FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

"Assume that you use the TransactionScope class in SQL Server client-side source code, and you do not explicitly open the SQL Server connection in a transaction. When the SQL Server connection is released, the transaction isolation level is reset incorrectly."