Handling Transactions in SQL Azure

Article
1/17/2024

In this article I am going to touch on some of the aspects of transaction handling in SQL Azure.

Local Transactions

SQL Azure supports local transactions. These types of transactions are done with the Transact-SQL commands BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION. They work exactly the same as they do on SQL Server.

Isolation Level

SQL Azure default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options set to ON, learn more about isolation levels here. You cannot change the database default isolation level. However, you can control the isolation level explicitly on a connect ion. On way to do this you can use any one of these in SQL Azure before you BEGIN TRANSACTION:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET TRANSACTON ISOLATION LEVEL controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server and spans batches (GO statement). All of the above works exactly the same as SQL Server.

Distributed Transactions in SQL Azure

SQL Azure Database does not support distributed transactions, which are transactions that multiple transaction managers (multiple resources). For more information, see Distributed Transactions (ADO.NET). This means that SQL Azure doesn’t allow Microsoft Distributed Transaction Coordinator (MS DTC) to delegate distributed transaction handling. Because of this you can’t use ADO.NET or MSDTC to commit or rollback a single transaction that spans across multiple SQL Azure databases or a combination of SQL Azure and an on-premise SQL Server.

This doesn’t mean that SQL Azure doesn’t support transactions, it does. However, it only supports transactions that are not escalated to a resource manager such as MS DTC. An article entitled: Transaction Management Escalation on MSDN can give you more information.

TransactionScope

The TransactionScope class provides a simple way to mark a block of code as participating in a transaction, without requiring you to interact with the transaction itself. The TransactionScope class works with the Transaction Manager to determine how the transaction will be handled. If the transaction manager determines that the transaction should be escalated to a distributed transaction, using the TransactionScope class will cause a runtime exception when running commands against SQL Azure, since distributed transactions are not supported.

So the question is when is it safe to use the TransactionScope class with SQL Azure? The simple answer is whenever you use it in a way that the Transaction Manager does not promote the transaction to a distributed transaction. So another way to ask the question is what causes the transaction manager to promote the transaction? Here are some cases that cause the transaction to be promoted:

· When you have multiple connections to different databases.
· When you have nested connections to the same database.
· When the ambient transaction is a distributed transaction, and you don’t declare a TransactionScopeOption.RequiresNew.
· When you invoke another resource manager with a database connection.

Juval Lowy wrote an excellent whitepaper (downloadable here) all about System.Transactions, where he covers promotion rules in detail.

Because transaction promotion happens at runtime you need to make sure you understand all your runtime code paths in order to use TransactionScope successfully. You don’t want the thread calling your method to be involved in an ambient transaction.

SqlTransaction

One way to write your code without using the TransactionScope class is to use SqlTransaction. The SqlTransaction class doesn’t use the transaction manager, it wraps the commands within a local transaction that is committed when you call the Commit() method. You still can’t have a single transaction across multiple databases; however SqlTransaction class provides a clean way in C# to wrap the commands. If your code throws an exception, the using statement guarantees a call to IDispose which rolls back the transaction.

Here is some example code to look over:

using (SqlConnection sqlConnection =
    new SqlConnection(ConnectionString))
{
    sqlConnection.Open();

    using (SqlTransaction sqlTransaction =
        sqlConnection.BeginTransaction())
    {
        // Createthe SqlCommand object and execute the first command.
        SqlCommand sqlCommand = new SqlCommand("sp_DoFirstPieceOfWork",
            sqlConnection, sqlTransaction);

        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCommand.ExecuteNonQuery();
        // Createthe SqlCommand object and execute the first command.
        SqlCommand sqlCommand = new SqlCommand("sp_DoSecondPieceOfWork",
            sqlConnection, sqlTransaction);

        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCommand.ExecuteNonQuery();

        sqlTransaction.Commit();
    }
}

Share via