TransactionScope and Connection Pooling

Question

Hi,

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

Answer 1

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.