"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" not taking? Or am I looking in the wrong way?

We have a problem with some database code that apparently executes with the wrong isolation level. In this particular part of the code, it is supposed to execute with "READ UNCOMMITTED" to minimize locks. Inconsistent data is OK at this point.

However, the code actually reads with READ COMMITTED, and we can't figure out why.

Here's what we did:

1. Open the connection
2. Execute on this connection "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
3. Hit a breakpoint
4. Execute the SQL

On the breakpoint, we issue this command to the database:

select s.session_id, s.transaction_isolation_level, st.text from sys.dm_exec_sessions s
inner join sys.sysprocesses sp on (sp.spid = s.session_id) 
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) st

This SQL reports 4 pooled connections right now, one of which is our connection that we can step beyond the breakpoint to execute our SQL with, that has this state:

53  2   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

ie. session 53 has isolation level 2 (READ COMMITTED), and the last SQL that was executed on this session was that "SET TRANSACTION ..." command.

How can this be?

We verified with SQL Profiler that this connection did not live before our .NET code opened it, so it was not reused from the connection pool.

Yet, with a fresh connection, and the only and first SQL executed on it explicitly told it to use READ UNCOMMITTED, how can the connection still be READ COMMITTED?

What should we look at here?

The connection string (with bits redacted) is like this:

SERVER=hostname;DATABASE=dbname;Integrated Security=false;USER ID=sa;PASSWORD=****;Application Name=appname;Type System Version=SQL Server 2000;Workstation ID=hostname;

The connections are normal SqlConnection connections, opened in the normal way.

Unfortunately we're unable to reproduce the problem if we write normal code opening a SqlConnection, so there has to be something with the application state, but since SqlProfiler and Sql Server both tells us that yes, the SQL was executed, but no, I don't care.

What can impact this?

The exact same code also opens other connections, that is, the code is executed many times and opens many connections, so more than one connection ends up in the pool, yet only the very first connection ends up having this problem.

This is SQL Server 2008 R2 and we have also reproduced this problem on 2012.

Edit

OK, some more information.

First, we are enabling pooling, or rather, we're not explicitly disabling it, nor are we twiddling the connection string to make "N" pools.

However, this connection is the first being opened with this particular connection string, thus it is not retrieved from the pool. Also see my note below about it being permanently "sick".

This connection is being set up like this:

var conn = new SqlConnection(...);
conn.StateChance += connection_StateChange;

private void connection_StateChange(Object sender, StateChangeEventArgs e)
{
    if (e.CurrentState == ConnectionState.Open)
    {
        using (IDbCommand cmd = ((SqlConnection)sender).CreateCommand())
        {
            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
            cmd.ExecuteNonQuery();
        }

We're not executing any other SQL before this.

Note that this code is used many times during the lifetime of the application, it is only the very first connection it opens that ends up being wrong.

This connection also becomes permanently sick. Since every time we open the connection (even though we might get it out of the connection pool), the above state change event executes, attempting to set the isolation level again. This also fails, but just for this single connection.

Additionally we've found one thing that impacts this since I posted this question.

...;Type System Version=SQL Server 2000;...

...;Type System Version=SQL Server 2008;MultipleActiveResultSets=true;...

then this problem goes away, at the breakpoint listed earlier, the connection now has "READ UNCOMMITTED" state.

This was a red herring, the connection was no longer being reported in our overview until we had actually executed code there.

We're continuing our debugging.

The problem here is that the SqlConnection.BeginTransaction that does not take parameters defaults to read committed. I guess we didn't understand what the "default isolation level" text is on that page.

That page has this text:

If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter (BeginTransaction). The isolation level set for a transaction persists after the transaction is completed and until the connection is closed or disposed. Setting the isolation level to Snapshot in a database where the snapshot isolation level is not enabled does not throw an exception. The transaction will complete using the default isolation level.

(my highlight)

Here's a LINQPad script that demonstrates:

void Main()
{
    using (var conn = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated security=true"))
    {
        conn.Open();
        Dump(conn, "after open");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after set iso");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "BEGIN TRANSACTION";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after sql-based begin transaction");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "COMMIT";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after sql-based commit");

        var trans = conn.BeginTransaction();

        Dump(conn, "after .net begin transaction", trans);

        trans.Commit();

        Dump(conn, "after .net commit");
    }
}

public static void Dump(SqlConnection connection, string title, SqlTransaction transaction = null)
{
    using (var cmd = new SqlCommand())
    {
        cmd.Connection = connection;
        if (transaction != null)
            cmd.Transaction = transaction;
        cmd.CommandText = "SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID";
        Debug.WriteLine(title + "=" + Convert.ToInt32(cmd.ExecuteScalar()));
    }
}

It will output:

after open=2
after set iso=1
after sql-based begin transaction=1
after sql-based commit=1
after .net begin transaction=2
after .net commit=2

Here you can see that manually beginning and committing a transaction through SQL would not change the isolation level, but beginning a transaction in .NET without explicitly stating the isolation level still changes it to read committed.

Since everywhere we read, starting a transaction without explicitly stating the isolation level said that it inherited the isolation level of the session, I guess we didn't understand that .NET would not do the same.