How does SqlConnection manage IsolationLevel?

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 h开发者_运维问答as 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???

Resolution:

The reason why pooled connections are returning the serializable isolation level is because of the following reason:

1. You have one connection pool (let's say CP1)
2. CP1 may have 50 connections.
3. You pick one connection C1 from CP1 and execute it with Serializable. This connection has its isolation level set now. Whatever you do, this will not be reset (unless this connection is used to execute a code in a different isolation level).
4. After executing the query C1(Serializable) goes back to CP1.
5. If steps 1-4 are executed again then the connection used may be some other connection than C1, let's say C2 or C3. So, that will also have its isolation level set to Serializable.
6. So, slowly, Serialzable is set to multiple connections in CP1.
7. When you execute a query where no explicit isolation level setting is being done, the connection picked from CP1 will decide the isolation level. For e.g. if such a query requests for a connection and CP1 uses C1(Serializable) to execute this query then this query will execute in Serializable mode even though you didn't explicitly set it.

Hope that clears a few doubts. :)

---

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.

---

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.

---

It does not return the isolation level to the original value. An example using entities required an empty transaction to reset the level (although it apparentently does not need to be committed (no .Complete() needed).

An attempt to change the iso level using a SP on the DB server does not work. Output:

Before: ReadCommitted
During: Serializable
After: Serializable
After Reset by SP Attempt: Serializable
During Reset by XACT: ReadCommitted
After Reset by XACT: ReadCommitted

// using Dbg = System.Diagnostics.Debug;
XactIso.iso isoEntity = new XactIso.iso();
using (isoEntity)
{
    Dbg.WriteLine("Before: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());

    var xactOpts = new TransactionOptions();
    xactOpts.IsolationLevel = System.Transactions.IsolationLevel.Serializable;

    using (TransactionScope xact = new TransactionScope(TransactionScopeOption.Required, xactOpts))
    {
        Dbg.WriteLine("During: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
        xact.Complete();
    }

    Dbg.WriteLine("After: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());

    isoEntity.usp_SetXactIsoLevel("ReadCommitted");

    Dbg.WriteLine("After Reset by SP Attempt: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
    // failed

    var xactOpts2 = new TransactionOptions();
    xactOpts2.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
    using (TransactionScope xact2 = new TransactionScope(TransactionScopeOption.Required, xactOpts2))
        Dbg.WriteLine("During Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
    // works w/o commit

    Dbg.WriteLine("After Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
}

where from link

proc [Common].[usp_GetXactIsoLevel]
as
begin          
    select         
        case transaction_isolation_level 
            WHEN 0 THEN 'Unspecified' 
            WHEN 1 THEN 'ReadUncommitted' 
            WHEN 2 THEN 'ReadCommitted' 
            WHEN 3 THEN 'RepeatableRead' 
            WHEN 4 THEN 'Serializable' 
            WHEN 5 THEN 'Snapshot' 
        end as lvl
     from sys.dm_exec_sessions 
    where session_id = @@SPID;
end

and (didn't work):

proc [Common].[usp_SetXactIsoLevel]
    @pNewLevel    varchar(30)
as
begin

    if @pNewLevel = 'ReadUncommitted'
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    else if @pNewLevel = 'ReadCommitted'
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    else if @pNewLevel = 'RepeatableRead'
        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    else if @pNewLevel = 'Serializable'
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    else if @pNewLevel = 'Snapshot'
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    else
        raiserror('Unrecognized Transaction Isolation Level', 16, 1);         
end        

---

In SQL Server 2014 the isolation level for pooled connection is reset when connection is returned to pool. 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 2017-04-22

Unfortunately this was later "unfixed" in SQL Server 2014 CU6 and SQL Server 2014 SP1 CU1 since it introduced a bug:

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