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

5 votes
1 answer
385 views
                          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?


                        
Asked by Martin Smith (87941 rep)
Jul 3, 2023, 06:23 PM
Last activity: Jul 3, 2023, 11:24 PM
Is there some fundamental reason that TransactionScope does not work as expected?

Related Questions
