Share via

IsolationLevel is carried over to connections in connectionpool

  • Article

When using SqlConnection and Transactions you my get a somewhat unexpected behavior.

Namely that when you pick a connection from the connection pool, the connection you get will have inherited the IsolationLevel from what was set on the connection when it went into the pool.

When doing research on this, I found that this is by design and this is described in the following KB.

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

https://support.microsoft.com/?id=972915

However, I had already written a demo that shows this, so here it is.

The scenario is as follows; two connection strings (remember that pooling is based on the connection string).

First we do a select in order to see what is in the database, this happens with the IsolationLevel set to ReadUncommited (i.e. we can do a dirty read.)

Secondly, we do an Update using a second connection string (so we do not pick it from the pool) this is to get some data that is changed but not committed.

Thirdly we do a new select using the first connection string again (so we DO pick it from the pool), this shows that the data read is the uncommitted data.

The default IsolationLevel is ReadCommited so we can see that the IsolationLevel is carried over when the connection goes into the connection pool,

thus a new connection will be able to read the uncommitted data.

So, to see this, first create a new table and insert a row:

create table ConnPoolTranDemoTable (cid int identity, ctext nvarchar(20))

insert into ConnPoolTranDemoTable values ('Our Text')

--drop table ConnPoolTranDemoTable

Then create a new console C# application.

static void Main(string[] args)

{

string cs_1 = @"Data Source=<your server>;User=UserOne;Password=UserOne;Initial Catalog=<your database>";

string cs_2 = @"Data Source=<your server>;User=UserTwo;Password=UserTwo;Initial Catalog=<your database>";

string select = "SELECT ctext FROM ConnPoolTranDemoTable WHERE cid = 1";

string update = "UPDATE ConnPoolTranDemoTable SET ctext = 'New Text' WHERE cid = 1";

using (SqlConnection con = new SqlConnection(cs_1))

{

con.Open();

SqlTransaction tran = con.BeginTransaction(IsolationLevel.ReadUncommitted);

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = select;

cmd.Transaction = tran;

string s = (string)cmd.ExecuteScalar();

Console.WriteLine("First select: {0}", s);

con.Close();

}

using (SqlConnection con = new SqlConnection(cs_2))

{

con.Open();

SqlTransaction tran = con.BeginTransaction(IsolationLevel.ReadCommitted);

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = update;

cmd.Transaction = tran;

int i = cmd.ExecuteNonQuery();

Console.WriteLine("No of updated rows: {0}", i);

// Now create a new connection using the connectionstring from the first select.

// This will cause a connection to be drawn from the connection pool.

// We can see that the IsolationLevel is the same as the previous one.

using (SqlConnection con_2 = new SqlConnection(cs_1))

{

con_2.Open();

SqlCommand cmd2 = new SqlCommand(select, con_2);

string s = (string)cmd2.ExecuteScalar();

Console.WriteLine("Second select: {0}", s);

con.Close();

}

con.Close();

}

}

Note that the connection strings differ by the user name; this is needed to keep the pools separated.

Run it and you should see that the second select is reading the uncommitted data from the update statement.

As mentioned, this is by design, and the recommendations from the KB are to:

. 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.

More info:

"SqlTransaction Class"

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

"SqlTransaction.IsolationLevel Property"

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.isolationlevel.aspx

"IsolationLevel Enumeration"

https://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Comments