How does SqlConnection manages IsolationLevel?

Question

This MSDN article states that:

An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.

The SqlConnection class has no member that may hold the isolation level. So how does a connection know what isolation level to run in???

The reason I'm asking this is because of the following scenario:

1. I opened a transaction using TransactionScope in Serializable mode, say "T1".
2. Opened a connection for T1.
3. T1 is finished/disposed, connection goes back to connection pool.
4. Called another query on same connection (after getting it from connection pool) and this query runs in serializable mode!!!

Problem:

1. How does the pooled connection still know what isolation level was associated to it???
2. How to revert it back to some other transaction level???

Answer 1

Isolation levels are implemented in the underlying DBMS, say SqlServer. Setting the isolation level most probably sets up SQL commands which set the isolation level for the connection.

The DBMS keeps the isolation level as long as the connection stays open. Because the connections is put into the pool, it stays open and keeps the settings made before.

When messing around with isolation levels, you should either reset the isolation level at the end of any transaction, or, even better, set it when a new connection is requested.

Answer 2

SqlConnection.BeginTransaction accepts an IsolationLevel argument and this is how one controls the isolation level of SqlClient connections. Another option is to use the generic System.Transactions and specify the isolation level in the TransactionOptions.IsolationLevel passed to the TransactionScope constructor. Both in the SqlClient and System.Transactions programming model the isolation level has to be explicitly specified for each transaction. If is not specified, the default will be used (Read Committed for SqlClient, Serializable for System.Transactions).

Pooled connections are not blindly reused. They have hidden internal members to track the current state like current transaction, pending results etc and the framework can clean up a connection returned to the pool. Just because state is not exposed in the programming model, it doesn't mean is not there (this applies to any library class, any class designer can hide member under the internal umbrella).

And finally any connection re-used from the pool it invokes sp_reset_connection which is a server procedure that cleans up the state of the session on the server side.