Keep Data Consistent With Transactions

Employ the manual and automatic transaction models supported by the .NET Framework to maintain consistent data in your applications.

Technology Toolbox: VB.NET, C#, SQL Server 2000, ASP.NET

Most of the applications you write probably deal with manipulating data one way or another, and changes to the data beyond simple updates are usually handled in transactions. Many business applications employ complex business rules, often combining multiple insert, update, and delete statements into one business object's method call. Such sophisticated data-handling applications can work wonders for a business, but it's also no wonder when they don't work. In particular, you as the developer face the challenge of maintaining data consistency—especially when one statement within the method call succeeds and another statement fails.

Transactions provide the solution to such problems. Transactions enable you to roll back the changes made to data when an error occurs, or commit the changes when all the data operations succeed. This ability keeps your application's data consistent day in and day out (see Figure 1).

I'll help you master transactions. I'll discuss what transactions are, describe the transaction models supported by the .NET Framework, and show you some handy techniques for adding transactional support to your applications.

A transaction consists of a set of related operations that either succeed or fail as a single unit. Transactions have been around as long as people have been doing business. For example, a transaction might involve transferring money between your savings and checking accounts. The bank first responds to your transaction request by verifying that you have enough money in your savings account to support the withdrawal. The bank then withdraws the requested amount of money from your savings account and deposits it into your checking account.

Both operations—withdrawing from your savings account and depositing into your checking account—must work for the transaction to succeed. The bank won't withdraw the money if the deposit fails. The bank wraps the transfer into a transaction, ensuring that your accounts maintain their balances in the event of a failure.

Successful transactions commit the changes made to your accounts to the bank's database. Failed transactions result in the bank aborting the operation and rolling your account balances back to their pre-transaction dollar amounts (except for penalties, of course).

The .NET Framework provides both manual and automatic models for implementing transactions. You can use either model in any application environment. However, each model has certain features that make it the right choice in a given situation.

The manual model works well in applications that use one data resource—such as SQL Server—on one server. The manual model provides measured control over the transaction and lets you nest transactions. It also works with the .NET Data Provider for SQL Server and the OLE DB .NET Data Provider.

Manual Model Gives More, Takes More
On the other hand, the manual model doesn't enlist data resources into the transaction automatically, nor does it coordinate transactions between data resources. This means you must handle enlistment and coordination. Furthermore, if you need to pass a transaction between objects, you must do so explicitly.

You create a transaction with the manual model and the .NET Data Provider for SQL Server with the SqlConnection and SqlTransaction objects. The SqlConnection object's BeginTransaction method creates and returns a SqlTransaction object:

Dim conn As New SqlClient.SqlConnection
Dim trans As SqlTransaction

conn.ConnectionString = _

' Return a transaction object from the 
' opened connection
trans =  conn.BeginTransaction()

You must open the connection before beginning your transaction (you'll receive an error later if you skip this step). Now mark the beginning of your transaction by calling the BeginTransaction method. Assign the SqlTransaction object returned from the BeginTransaction method call to a SqlCommand object's Transaction property. Doing so enlists the command in the transaction:

' Create the command
Dim orderCommand As New _
	SqlCommand("InsertOrder_sp", conn)

' Enlist the command with the 
' transaction
orderCommand.Transaction = trans

All SqlCommands enlisted with a transaction execute within that transaction's boundary.

Now end the transaction by calling the Transaction object's Commit or Rollback method. You reward successful transactions with the Commit method, and you kill failed transactions with the Rollback method. You can use a Try/Catch block to commit or roll back changes based on the success or failure of the transaction (see Listing 1). Using nested transactions mandates committing or rolling back all subordinate transactions before the root transaction can end.

You'll be pleased to know that the automatic model removes some of the labor involved with creating a transaction and is supported by Microsoft Transaction Server, COM+, and the Common Language Runtime. Unfortunately, the automatic model doesn't support nested transactions.

The automatic model provides the benefit of automatic enlistment and coordination of data resources, and it transfers transactions between objects automatically (see Listing 2). Use the automatic model for applications where the transaction spans multiple transaction-aware resources. For example, you might have a transaction that must be coordinated with SQL Server, Oracle, and Microsoft Message Queue Server. The automatic enlistment and coordination properties of the automatic model simplify transactions in this kind of heterogeneous environment.

You can add automatic transactional support to ASP.NET pages, XML Web services, and custom .NET classes. You determine which objects should participate in a transaction by setting attributes on each object (see Table 1). Each attribute determines whether the object participates in an ongoing transaction, initiates and runs within a new transaction, or ignores a transaction altogether. Various transaction attribute values affect your object's transactional behavior in different ways.

Put Transaction Support on Auto
The process for adding automatic transactional support to objects differs depending on the object type. ASP.NET pages running on Windows 2000 or higher support automatic transactions. You simply set a transaction page directive:

<%@ Page Transaction="Required" %>

The sample application accompanying this column illustrates and discusses how to add automatic transactions to XML Web services. Be warned that adding transactions to a custom .NET class takes a bit more effort than ASP.NET pages or XML Web services.

You must prepare .NET classes to execute within an automatic transaction. Set a reference to the System.EnterpriseServices assembly from within your project, so your class inherits from ServicedComponent. Next, apply the appropriate transaction attribute to your class:

<Transaction(TransactionOption. _
	Required)> _ 
	Public Class SalesComponentCOM
	Inherits ServicedComponent
End Class

Finally, sign the assembly containing your class with a strong name. Strong names are a topic unto themselves, but fortunately the .NET Framework SDK documentation fully describes the process of creating a strong name and signing an assembly with it.

You determine the outcome of a transaction pretty much the same way in both the automatic and manual models. Your classes vote to commit or abort a transaction. Web services, ASP.NET pages, and .NET classes can vote in a few different ways.

One option is to not vote at all. In the absence of an explicit vote, the transaction is committed by default. However, I don't recommend this. Not casting an explicit vote can impose a performance hit due to you not releasing the resources the transaction was using.

A second option is to add the AutoComplete attribute on your transaction-aware methods. The AutoComplete attribute commits the transaction upon successful completion or rolls back if an error occurs:

<AutoComplete()> Public Function _
	MakeSale() ?
End Function

You can exercise even more control over the transaction's outcome by using the System.EnterpriseServices.ContextUtil class's SetComplete and SetAbort methods. These methods resemble the Commit and Rollback methods used in the manual model. SetComplete and SetAbort enable you to commit or roll back the transaction, respectively. For example, you can use the ContextUtil class to control transaction outcome with a Try/Catch block (see Listing 2). If your class is participating in an ongoing transaction (meaning it's not the root transaction), casting a vote doesn't commit or roll back the transaction immediately. Nothing happens until the root transaction object is deactivated. Also, the entire transaction gets rolled back if any one rollback vote is cast.

You can now add transactions to your applications with relative ease. Regardless of which transaction model you choose, adding transactional support to your applications enables you to control when data changes are committed or, if necessary, rolled back. Transaction support also adds a layer of data consistency not found in applications that don't use transactions. Download my sample code and see how transactions can help you in your next project.


  • Windows Server 2019 Preview Build 17650 Released

  • SQL Server 2016 Service Pack 2 Now Available

  • (Not) Keeping Up with the Dynamics 365s

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.