Is there some fundamental reason that TransactionScope does not work as expected?

Question

For the following code...

using System.Transactions;
using Microsoft.Data.SqlClient;

var connectionString = "...";


using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew,
           new TransactionOptions { IsolationLevel = IsolationLevel.Snapshot }))
{
    TestExec(connectionString);
    TestExec(connectionString);
    scope.Complete();
}


TestExec(connectionString);

static void TestExec(string connectionString)
{
    using var conn = new SqlConnection(connectionString);
    conn.Open();
    var cmd = new SqlCommand
    {
        CommandText = @"
DECLARE @useroptions table (Opt varchar(50), Value varchar(50));
INSERT @useroptions EXEC ('dbcc useroptions')
SELECT Value AS [isolation level], CURRENT_TRANSACTION_ID() AS [tran_id], @@SPID FROM @useroptions WHERE Opt = 'isolation level'",
        Connection = conn
    };

    using var reader = cmd.ExecuteReader();
    while (reader.Read())
        Console.WriteLine($"Isolation Level:{reader.GetString(0)} TransactionId:{reader.GetInt64(1)}, Session:{reader.GetInt16(2)}");
}

When the Connection String is for on premise SQL Server then typical output is below

Isolation Level:snapshot TransactionId:2696760, Session:57
Isolation Level:snapshot TransactionId:2696760, Session:57
Isolation Level:snapshot TransactionId:2696821, Session:57

When the Connection String is for Azure SQL Database then typical output is...

Isolation Level:snapshot TransactionId:4636394, Session:92
Isolation Level:read committed snapshot TransactionId:4636394, Session:92
Isolation Level:read committed snapshot TransactionId:4636446, Session:92

Neither output is really what is hoped for.

In the "On Premise" case the isolation level is not reset when the scope ends and a new transaction starts. This causes many complaints (such as this StackOverflow question).

In the "SQL Database" case the isolation level is reset but happens too early and is not maintained for the whole scope. I came across this behaviour as in my case the first query does a read and the second a write and the code was relying on optimistic concurrency exceptions which never came (as the write query was not running at snapshot as expected).

It looks as though the "on premise" behaviour was temporarily the same as the SQL database behaviour but this was rolled back. This second bug is reported here.

Can anyone shed any light on this matter?

• Is the difference in behaviour due to differing implementations in the server (of sp_reset_connection) or the client?
• If it is Server Side was this a deliberate design decision to fix the issue on premise as per KB3025845 but leave Azure behaving differently?
• If that was a deliberate design decision is there some fundamental reason why we can't get both cases working as hoped for?

Answer 1

I'm guessing you're probably looking for a more qualified answer from someone who has more internal knowledge on the why things are this way. Unfortunately I'm not that person.

But in case you haven't come across it yet, this behavior (for the SQL Server product) is officially documented, and in my mind accepted as by design behavior (even if it was initially some kind of bug) by Microsoft, as mentioned in Snapshot Isolation in SQL Server - Managing Concurrency with Isolation Levels:

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.

And FWIW, Kendra Little briefly touches on this specific issue when using the SNAPSHOT isolation level in How to Choose Between RCSI and Snapshot Isolation Levels:

This typically means that you need to segment out the queries which you’d like to use snapshot into their own connection pool as well, because resetting a connection doesn’t reset the isolation level at the same time.