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 creates a new CommittableTransaction and commits it.
tx = New CommittableTransaction
Dim myConnection As New SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind")
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
Dim c As ConsoleKeyInfo
While (True)
Console.Write("Commit or Rollback? [C|R] ")
c = Console.ReadKey()
Console.WriteLine()
If (c.KeyChar = "C") Or (c.KeyChar = "c") Then
tx.Commit()
Exit While
ElseIf ((c.KeyChar = "R") Or (c.KeyChar = "r")) Then
tx.Rollback()
Exit While
End If
End While
myConnection.Close()
tx = Nothing
//Create a committable transaction
tx = new CommittableTransaction();
SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind");
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] ");
ConsoleKeyInfo c = Console.ReadKey();
Console.WriteLine();
if ((c.KeyChar == 'C') || (c.KeyChar == 'c'))
{
tx.Commit();
break;
}
else if ((c.KeyChar == 'R') || (c.KeyChar == 'r'))
{
tx.Rollback();
break;
}
}
myConnection.Close();
tx = null;
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 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
Concepts
Implementing an Implicit Transaction using Transaction Scope