Implementing an Explicit Transaction using CommittableTransaction

The CommittableTransaction class provides an explicit way for applications to use a transaction, as opposed to using the TransactionScope class implicitly. It is useful for applications that want to use the same transaction across multiple function calls or multiple thread calls. Unlike the TransactionScope class, the application writer needs to specifically call the Commit and Rollback methods in order to commit or abort the transaction.

Overview of the CommittableTransaction class

The CommittableTransaction class derives from the Transaction class, therefore providing all the functionality of the latter. Specifically useful is the Rollback method on the Transaction class that can also be used to rollback a CommittableTransaction object.

The Transaction class is similar to the CommittableTransaction class but does not offer a Commit method. This enables you to pass the transaction object (or clones of it) to other methods (potentially on other threads) while still controlling when the transaction is committed. The called code is able to enlist and vote on the transaction, but only the creator of the CommittableTransaction object has the ability to commit the transaction.

You should note the followings when working with the CommittableTransaction class,

• Creating a CommittableTransaction transaction does not set the ambient transaction. You need to specifically set and reset the ambient transaction, to ensure that resource managers operate under the right transaction context when appropriate. The way to set the current ambient transaction is by setting the static Current property on the global Transaction object.

• A CommittableTransaction object cannot be reused. Once a CommittableTransaction object has been committed or rolled back, it cannot be used again in a transaction. That is, it cannot be set as the current ambient transaction context.

Creating a CommittableTransaction

The following sample uses the Microsoft.Data.SqlClient NuGet package. Import Microsoft.Data.SqlClient in C# or Visual Basic, and set the SQL_CONNECTION_STRING environment variable to a development database that contains Northwind's Region table. Use a trusted server certificate and keep encryption and certificate validation enabled. For more information, see Configure SQL Server to use certificates and Encryption and certificate validation in Microsoft.Data.SqlClient.

//Create a committable transaction
tx = new CommittableTransaction();

string connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING");
if (string.IsNullOrWhiteSpace(connectionString))
    throw new InvalidOperationException("Set the SQL_CONNECTION_STRING environment variable.");
using SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand();

//Open the SQL connection
myConnection.Open();

//Give the transaction to SQL to enlist with
myConnection.EnlistTransaction(tx);

myCommand.Connection = myConnection;

// Restore database to near it's original condition so sample will work correctly.
myCommand.CommandText = "DELETE FROM Region WHERE (RegionID = 100) OR (RegionID = 101)";
myCommand.ExecuteNonQuery();

// Insert the first record.
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')";
myCommand.ExecuteNonQuery();

// Insert the second record.
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'MidEastern')";
myCommand.ExecuteNonQuery();

// Commit or rollback the transaction
while (true)
{
    Console.Write("Commit or Rollback? [C|R] ");
    string choice = Console.ReadLine();
    if (choice == null)
        throw new InvalidOperationException("Enter C to commit or R to roll back.");

    if (string.Equals(choice, "C", StringComparison.OrdinalIgnoreCase))
    {
        tx.Commit();
        break;
    }
    else if (string.Equals(choice, "R", StringComparison.OrdinalIgnoreCase))
    {
        tx.Rollback();
        break;
    }
}
myConnection.Close();
tx = null;

tx = New CommittableTransaction

Dim connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
If String.IsNullOrWhiteSpace(connectionString) Then
    Throw New InvalidOperationException("Set the SQL_CONNECTION_STRING environment variable.")
End If
Using myConnection As New SqlConnection(connectionString)
    Dim myCommand As New SqlCommand()

    'Open the SQL connection
    myConnection.Open()

    'Give the transaction to SQL to enlist with
    myConnection.EnlistTransaction(tx)

    myCommand.Connection = myConnection

    'Restore database to near it's original condition so sample will work correctly.
    myCommand.CommandText = "DELETE FROM Region WHERE (RegionID = 100) OR (RegionID = 101)"
    myCommand.ExecuteNonQuery()

    'Insert the first record.
    myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')"
    myCommand.ExecuteNonQuery()

    'Insert the second record.
    myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'MidEastern')"
    myCommand.ExecuteNonQuery()

    'Commit or rollback the transaction
    While (True)
        Console.Write("Commit or Rollback? [C|R] ")
        Dim choice = Console.ReadLine()
        If choice Is Nothing Then
            Throw New InvalidOperationException("Enter C to commit or R to roll back.")
        End If

        If String.Equals(choice, "C", StringComparison.OrdinalIgnoreCase) Then
            tx.Commit()
            Exit While
        ElseIf String.Equals(choice, "R", StringComparison.OrdinalIgnoreCase) Then
            tx.Rollback()
            Exit While
        End If
    End While

    myConnection.Close()
End Using
tx = Nothing

Creating an instance of CommittableTransaction does not automatically set the ambient transaction context. Therefore, any operation on a resource manager is not part of that transaction. The static Current property on the global Transaction object is used to set or retrieve the ambient transaction and the application must manually set it to ensure that resource managers can participate in the transaction. It is also a good practice to save the old ambient transaction and restore it when you finish using the CommittableTransaction object.

To commit the transaction, you need to explicitly call the Commit method. For rolling back a transaction, you should call the Rollback method. It is important to note that until a CommittableTransaction has been committed or rolled back, all the resources involved in that transaction are still locked.

A CommittableTransaction object can be used across function calls and threads. However, it is up to the application developer to handle exceptions and specifically call the Rollback(Exception) method in case of failures.

Asynchronous Commit

The CommittableTransaction class also provides a mechanism for committing a transaction asynchronously. A transaction commit can take substantial time, as it might involve multiple database access and possible network latency. When you want to avoid deadlocks in high throughput applications, you can use asynchronous commit to finish the transactional work as soon as possible, and run the commit operation as a background task. The BeginCommit and EndCommit methods of the CommittableTransaction class allow you to do so.

You can call BeginCommit to dispatch the commit holdup to a thread from the thread pool. You can also call EndCommit to determine if the transaction has actually been committed. If the transaction failed to commit for whatever reason, EndCommit raises a transaction exception. If the transaction is not yet committed by the time EndCommit is called, the caller is blocked until the transaction is committed or aborted.

The easiest way to do an asynchronous commit is by providing a callback method, to be called when committing is finished. However, you must call the EndCommit method on the original CommittableTransaction object used to invoke the call. To obtain that object, you can downcast the IAsyncResult parameter of the callback method, since the CommittableTransaction class implements IAsyncResult class.

The following example shows how an asynchronous commit can be done.

public void DoTransactionalWork()
{
     Transaction oldAmbient = Transaction.Current;
     CommittableTransaction committableTransaction = new CommittableTransaction();
     Transaction.Current = committableTransaction;

     try
     {
          /* Perform transactional work here */
          // No errors - commit transaction asynchronously
          committableTransaction.BeginCommit(OnCommitted,null);
     }
     finally
     {
          //Restore the ambient transaction
          Transaction.Current = oldAmbient;
     }
}
void OnCommitted(IAsyncResult asyncResult)
{
     CommittableTransaction committableTransaction;
     committableTransaction = asyncResult as CommittableTransaction;
     Debug.Assert(committableTransaction != null);
     try
     {
          using(committableTransaction)
          {
               committableTransaction.EndCommit(asyncResult);
          }
     }
     catch(TransactionException e)
     {
          //Handle the failure to commit
     }
}

See also

• Implementing an Implicit Transaction using Transaction Scope
• Transaction Processing