When you are using Microsoft SQL Server connection pooling, a recycled connection gets all the session details reset (eg: temp tables are deleted). Internally this is done by the special sproc sp_reset_connection. This answer has a full list of details which are reset – What does sp_reset_connection do . One notable thing which is not on this list is the transaction isolation level. So, for example, if a connection has run a transaction at the read uncommitted level, and that connection gets recycled, the next thread which receives that connection will have a transaction isolation level of read uncommitted, which is different from the default on brand new connections of read committed. This behavior is intended (by Microsoft), as it’s documented in the TDS spec; there’s this sentence in there “Distributed
transactions and isolation levels will not be reset.“.

This is all background. There are several other SO questions about leaky transaction isolation levels, but none of them cover what I want to know …

• one – How to prevent leak of transaction isolation level in pooled connections
• another – SQL Server: Isolation level leaks across pooled connections

Given that Dapper and Entity Framework have a common pattern of opening a connection before running one query and then closing it after, it would seem that those libraries are expecting connection pooling to be in use. To expand, when a SqlConnection object starts out as closed, EF statements, and dapper database methods all will open the connection before executing, and then close it after. If the connection has already been opened, either explicitly by calling Open or as a side effect of starting a transaction, then Dapper and EF use the connection as is (they don’t close it after).

Since this common pattern is used by Microsoft (and others), this suggests (to me) that connection pooling is expected to be used. And the SqlConnection object even has connection pooling enabled by default.

So this leaky (or persistent) transaction isolation level thing would seem to be highly counterintuitive. It’s like a really nasty boobytrap for the uninitiated. If a query needs to be run as read uncommitted (or less troublesome, serialized), and then is innocently returned to the connection pool, the next thread which comes along may get a nasty surprise.

here’s a small sample program demonstrating this nasty surprise:

private static void PoolTest()
{
    for (int poolMode = 1; poolMode <= 2; poolMode += 1)
    {
        string poolTest = poolMode == 1 ? "simple" : "pooled";
        string unique = DateTime.UtcNow.Ticks.ToString();
        var csb = new SqlConnectionStringBuilder()
        {
            DataSource = @"localhost", InitialCatalog = "AdventureWorks",
            IntegratedSecurity = true, WorkstationID = unique
        };
        // set up an antagonist connection to manage the test table, and to insert a record uncommitted
        csb.Pooling = false;
        var setupConn = new SqlConnection(csb.ConnectionString + ";");
        // set up a protagonist connection to run the test
        csb.Pooling = poolMode == 2;
        var testConn = new SqlConnection(csb.ConnectionString);
        // use a "global" temp table so that both connections can see it
        string evilTable = "##eviltable" + unique;

        // open the antagonist connection (and keep it open), create the table, and insert a record, committed
        setupConn.Open();
        setupConn.Execute($"create table {evilTable} (MyKey varchar(10) not null primary key, MyValue varchar(20))");
        setupConn.Execute($"insert into {evilTable} (MyKey, MyValue) values ('key1', 'expected')");
        bool scary = false;

        // all queries are run using dapper.  when dapper encounters a closed connection, it opens the connection,
        // runs the query, and then closes the connection.  this strategy is not unique to dapper, entity
        // framework works the same way.  to see such a thing in action, use dapper's Execute and Query to create a
        // temp table (single-pound) and then try to access it (it won't be there).  you have to explicitly open a
        // connection or start a transaction to prevent this behavior (again, this isn't just dapper; EF does the
        // same thing).  this would seem to open and close a LOT of connections to the database server, which is
        // why connection pooling is attractive.
        // run 4 test scenarios:
        // 1st: just a simple query
        // 2nd: on an alternate connection, begin a transaction, insert a record (dirty), and stall and later
        //    rollback.  while the alternate connection is stalled, run the query.  it should block until
        //    the alternate connection rolls back
        // 3rd: the same as the 2nd scenario, except that before we start, the primary connection starts
        //    a transaction using read-uncommitted and then immediately rolls it back.  you would think this
        //    wouldn't matter.
        // 4th: same as the 1st scenario
        
        for (int test = 1; test <= 4; test += 1)
        {
            // for scenario 3, open a transaction at read uncommitted level, and roll it back
            if (test == 3)
            {
                testConn.Open();
                var presumablyInnocent = testConn.BeginTransaction(IsolationLevel.ReadUncommitted);
                presumablyInnocent.Rollback();
                testConn.Close();
            }

            Thread background = null;
            // for scenarios 2 and 3, insert a record uncommitted (transaction still
            // open), and wait 3 seconds
            if (test == 2 || test == 3)
            {
                var lockingTransaction = setupConn.BeginTransaction(IsolationLevel.ReadCommitted);
                setupConn.Execute($"insert into {evilTable} (MyKey, MyValue) values ('key2', 'UNEXPECTED')",
                    transaction: lockingTransaction);
                background = new Thread(() => {
                    Thread.Sleep(TimeSpan.FromSeconds(3));
                    lockingTransaction.Rollback();
                });
                background.Start();
            }

            // probe the test table, writing all records to console
            var items = testConn.Query<Dto>($"select MyKey, MyValue from {evilTable}").ToList();
            foreach (var item in items)
                Console.WriteLine($"{poolTest}/{test}: {item}");
            scary |= items.Any(item => item.MyValue == "UNEXPECTED");
            // wait for the background thread (if there was one)
            background?.Join();
        }
        if (scary)
            Console.WriteLine($"{poolTest}/F: we got a dirty read, all unexpected-like; isn't that scary");
        if (poolMode == 1)
            Console.WriteLine();
    }
}

// this is the test table, a simplification of tables used everywhere
private class Dto
{
    public string? MyKey { get; set; }
    public string? MyValue { get; set; }
    public override string ToString() => $"{MyKey} {MyValue}";
}

There are several solutions to this issue. None of them are ideal. The most obvious is to reset the transaction isolation level of a connection before Closing or Disposing it, returning it to the pool. In EF Core, it is possible to use a DB Interceptor (IDbConnectionInterceptor) to do this.
I’ve seen various answers suggesting use of a different connection string for each prospective isolation level, or just never using isolation levels at all; these are not very useful outside of toy or very small programs.

So here’s my actual question:
What does Microsoft intend for us to do ? Every straightfoward example I’ve seen is susceptible to this leak. I’ve seen the TDS documentation specify this behavior, but there’s never any hint as to why it’s done this way. So, why is connection pooling implemented in such a way that new connections and recycled connections have different (and troublesome) default transaction isolation level behavior?