none
Second query executed with wrong isolation level RRS feed

  • Question

  • Hi,

    on SQL Server, executing two queries inside a common TransactionScope, both are executed using the isolation level defined in the TransactionScope, as expected.

    The same does not happen on SQL Azure: the second query is executed with the default Azure isolation level that is "Read Committed Snapshot". 

    See test code below:

      class Program
      {
        const string AzureConnectionString = "Server=tcp:xxxx.database.windows.net,1433;Initial Catalog=xxxx; Persist Security Info=False;User ID = lcms; Password=xxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout = 30;";
        const string SqlServerConnectionString = "Server=localhost,1433;Initial Catalog=LCMS;Integrated Security=True";
    
        static void Main(string[] args)
        {
          Console.WriteLine("SQL SERVER");
          string connectionString = SqlServerConnectionString;
          using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
          {
            TestExec(connectionString); // Expected print: "read uncommitted"
            TestExec(connectionString); // Expected print: "read uncommitted"
          }
    
          Console.WriteLine("SQL AZURE");
          connectionString = AzureConnectionString;
          using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
          {
            TestExec(connectionString); // Expected print: "read uncommitted"
            TestExec(connectionString); // Expected print: "read uncommitted", Actual: "read committed snapshot"
          }
        }
    
        static void TestExec(string connectionString)
        {
          using (var conn = new SqlConnection(connectionString))
          {
            conn.Open();
            var cmd = new SqlCommand()
            {
              CommandText = "dbcc useroptions",
              Connection = conn
            };
    
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
              if (reader.GetString(0) == "isolation level")
                Console.WriteLine(reader.GetString(1));
            }
          }
        }
      }

    Reproduced with .NET Framework 4.7.2. 

    Anyone can suggest what I'm missing here or even if this is a Azure by design behaviour?
    It looks like a bug.


    chrjs

    Monday, June 17, 2019 1:18 PM

All replies

  • Hi Chrjs,

    Firstly, I think you need to be using .NET Framework 2.x. Please see: System.Transactions Integration with SQL Server (link), if you want to use TransactionScope directly.

    "The .NET Framework version 2.0 introduced a transaction framework that can be accessed through the System.Transactions namespace. This framework exposes transactions in a way that is fully integrated in the .NET Framework, including ADO.NET."

    See the following if you want to wrap T-SQL around TransactionScope, which I think will work with Azure SQL Database.

    Using TransactionScope around a stored procedure with transaction in SQL Server 2014 (link). 


    Yes, TransactionScope can still work when wrapping a TSQL BEGIN / COMMIT TRANSACTION or an ADO SqlConnection.BeginTransaction. When wrapping a single connection, the behaviour is similar to nesting transactions in Sql:
    
    @@TranCount will be incremented on each BEGIN TRAN
    
    COMMIT TRAN will simply decrement @@TRANCOUNT. The transaction will only be committed if @@TRANCOUNT hits zero.

    Please see the following to see a feature comparison between Azure SQL Database and SQL Server (link).


    Tuesday, June 18, 2019 7:59 PM
    Moderator
  • TransactionScope is available starting from .NET 2.0... this does not mean I need to use 2.x only.

    In my case, there is no explicit transaction management in TSQL code. A simple TSQL command is executed inside a TransactionScope, and works as expected with SQL Server.

    The question is why on SQL Azure the behaviour is different and apparently bugged, meaning that the second command executed inside the same TransactionScope uses a wrong isolation level (not the isolation level defined in the TransactionScope).


    chrjs

    Wednesday, June 19, 2019 8:16 AM
  • Azure SQL Database does not support full SQL Server functionality. If you want full SQL Server parity in Azure, go with SQL Server on Azure VM. If you want full SQL Server + VNet and private endpoints, go with Azure SQL Managed Instance. 

    Reiterating the following information: Please see the following to see a feature comparison between Azure SQL Database and SQL Server (link).

    Regards,

    Mike

    Wednesday, June 19, 2019 5:52 PM
    Moderator
  • Hi Mike,

    thanks for the link but I already looked into it.

    The only features related to Transactions that are mentioned in your link are related to cross-database and distributed transactions, that I know are not supported in SQL Azure, but it is not my case. I'm querying the same database inside a single TransactionScope context.

    I can't find any documentation saying that TransactionScope cannot be used or have some limitations on SQL Azure.

    Form an external point of view, seems more an issue on .NET client side than SQL Azure itself, but I don't know if ADO.NET have something specific to SQL Azure.

    My goal is to migrate the product to SQL Azure, so using SQL Server on a VM is not an option.
    With this issue, it is hard to migrate a big product because can be frequent that multiple queries or SP executions are triggered inside the same TransactionScope and we need them to be executed with the correct isolation level as defined by TransactionScope documentation.

    Can you go a little bit in deep into the issue?

    Thanks,

    Christian




    chrjs

    Monday, July 1, 2019 8:15 AM
  • Please take a look at the following Stack Overflow thread (link) which states:

    Quick update on distributed transactions with Azure SQL Database: A couple of days ago, we introduced support for distributed transactions in Azure SQL Database. The feature that lights up the capability is called elastic database transactions. It focuses on scenarios using the .NET distributed transaction APIs such as TransactionScope. These APIs start working against Azure SQL Database once you installed the new 4.6.1 release of the .NET framework. You can find more information about how to get started here.

    Please let us know if you have additional questions. I think the issue is that TransactionScope does not work in Azure SQL when you attempt to open a second connection.

    Thank you,

    Mike




    Monday, July 1, 2019 10:16 PM
    Moderator
  • Hi Mike,

    I see that the code reported in the link is exactly the same code of my issue: two connections inside a single TransactionScope.

    Here the code reported in the link:

    using (var scope = new TransactionScope())
    {
        using (var conn1 = new SqlConnection(connStrDb1))
        {
            conn1.Open();
            SqlCommand cmd1 = conn1.CreateCommand();
            cmd1.CommandText = string.Format("insert into T1 values(1)");
            cmd1.ExecuteNonQuery();
        }
    
        using (var conn2 = new SqlConnection(connStrDb2))
        {
            conn2.Open();
            var cmd2 = conn2.CreateCommand();
            cmd2.CommandText = string.Format("insert into T2 values(2)");
            cmd2.ExecuteNonQuery();
        }
    
        scope.Complete();
    }

    To use Elastic transactions, seems that the only thing needed is to use .NET 4.6.1 or above (I used 4.7.2). So I suppose I've nothing more to do to support Elastic transactions that in my code.

    Said so, it is pretty bizarre that the sample code provided in the link (that means it is supported) works if the connections are to two different databases and instead is not supported if the connections are on the same database (my case). What is your explanation?


    chrjs

    Tuesday, July 2, 2019 7:52 AM
  • Hi Chrjs,

    It only works when tied to a single connection when using Azure SQL Database (PaaS). So, it is not supported in your case. I am not sure how you got the information that it is supported? I see you opened a support ticket but the solution here is to use the elastic transactions.

    "Often times because its a best practice to open a connection late and close early, there might be situation where the scope spans two connections. Those scenarios are not supported in sql azure."

    "An example of where it might not work is; taking yr example; Assuming MyDataContext.GetDataContext() returns a new instance of a connection."

       using (var tx = new TransactionScope(TransactionScopeOption.RequiresNew, 
                                            new TransactionOptions() 
                                                {  IsolationLevel = IsolationLevel.ReadCommitted }
                                           ))
       {
           try{ 
               DoSomething(); //a method with using (var db = MyDataContext.GetDataContext())
               DoSomethingElse(); //another method with using (var db = MyDataContext.GetDataContext())
               tx.Complete();
           }
           catch { //Exception handler
           }
       }

    And in your case, you clearly have conn1.Open() and conn2.Open(), so your code will not work as I am attempting to convey.

    using (var scope = new TransactionScope())
    {
        using (var conn1 = new SqlConnection(connStrDb1))
        {
            conn1.Open();
            SqlCommand cmd1 = conn1.CreateCommand();
            cmd1.CommandText = string.Format("insert into T1 values(1)");
            cmd1.ExecuteNonQuery();
        }
    
        using (var conn2 = new SqlConnection(connStrDb2))
        {
            conn2.Open();
            var cmd2 = conn2.CreateCommand();
            cmd2.CommandText = string.Format("insert into T2 values(2)");
            cmd2.ExecuteNonQuery();
        }
    
        scope.Complete();
    }


    Tuesday, July 2, 2019 3:19 PM
    Moderator
  • Hi Mike,

    the code with 2 connections inside the same scope comes directly from the Elastic transactions page, for this reason I said "seems supported".

    My code also seems ok for Elastic transactions: it is compiled with .NET 4.7.2 and connects to SQL Azure, so Elastic transactions should be active by default. What else I've to do to enable the Elastic transactions?

    Please keep in consideration that I'm connecting to the same DB so actually the Elastic transactions should not be required.


    chrjs

    Wednesday, July 3, 2019 9:02 AM
  • I did some checks around the need of Elastic transactions. I checked in my code that the transaction is NOT promoted to DTC, neither connecting to SQL Server nor to SQL Azure, so if I'm not wrong I should not have the need of Elastic transactions.

    I checked by attaching to the event TransactionManager.DistributedTransactionStarted that is not fired during the execution.


    chrjs

    Wednesday, July 3, 2019 9:22 AM
  • I had a first response from the support ticket that finally helps to better understand what is happening.

    Issue is related to how the connection pool manages the reset of a connection, so a workaround for the issue is to add Pooling=No in the SQL Azure connection string, so disabling the connection pooling.

    These other information directly from the support:

    <<The reason why when using connection pooling this happen is due to the behavior of sp_reset_connection that is used when ADO.Net get a connection from the connection pooling.

    This article explain very well the sp_reset_connection and the connection pool: https://blog.greglow.com/2018/07/30/sql-what-is-sp_reset_connection-and-should-i-care-about-it/

     

    The behavior of sp_reset_connection has changed in some SQL Server versions, that can justify the behavior the customer is seeing when running on SQL Server.

    In SQL Server 2014 the isolation level for pooled connection is reset when connection is returned to the pool. In earlier versions it is not.

    This again changed back to previous behavior in SQL 2014 CU6 and SQL 2014 SP1 CU1 with this fix: https://support.microsoft.com/en-us/help/3025845/fix-the-transaction-isolation-level-is-reset-incorrectly-when-the-sql>>

    I still need to know if there is something better that setting Pooling=No, or even if Microsoft may plan to fix this in future the same way has been done on SQL Server.


    chrjs

    Wednesday, July 3, 2019 2:07 PM
  • Thank you for providing this additional detail, Chrjs.

    Regards,

    Mike

    Saturday, July 6, 2019 12:01 AM
    Moderator