Managing Your Transactions

[Previous] [Next]

There are times when you want to make a batch of changes to your database and you require that either all or none of the changes are made to the database; you can't allow partial transactions to complete. For example, imagine that you want to transfer money from your savings account to your checking account. The bank won't be happy if the withdrawal from your savings account fails but the deposit into your checking account succeeds. Similarly, you'll be less than thrilled if the converse occurs. If either task does not complete successfully, the other task should be canceled. In transactional programming, this all-or-nothing trait is called atomicity.

ADO offers a number of features to help you use transactions in your application.

BeginTrans, CommitTrans, and RollbackTrans Methods

You can use ADO's transactional methods to manage atomic units of work. Let's say that you're writing the software for an ATM machine. You could create code such as the following that makes use of ADO's transactional methods:

 On Error Resume Next 'Start the transaction. cnDatabase.BeginTrans strSQL = "UPDATE Savings SET BalDue = BalDue _ 50 " & _ "WHERE AccountNumber = 123456789" cnDatabase.Execute strSQL If cnDatabase.Errors.Count > 0 Then 'An error occurred, cancel changes. cnDatabase.RollbackTrans Else strSQL = "UPDATE Checking SET BalDue = BalDue + 50 " & _ "WHERE AccountNumber = 123456789" cnDatabase.Execute strSQL If cnDatabase.Errors.Count > 0 Then 'An error occurred, cancel all of the changes. cnDatabase.RollbackTrans Else 'No errors occurred, commit all of the changes. cnDatabase.CommitTrans End If End If 

Transactions are so simple to use they're almost dangerous: improper use of transactions can easily cause problems in your application. There are more don'ts than dos when using transactions in ADO, but don't let that scare you away from using them. For example, transactions should be isolated. Work performed on one transaction should not affect another transaction that's active at the same time. If you modify a row in one transaction, another user should not be able to modify that row in another transaction while your transaction is active.

Another point to remember is that you should close your transactions as quickly as possible. One way to accomplish this is to avoid requiring user interaction while your transaction is active. Imagine that you have designed an application that a number of salespeople are using to enter new orders. When they click on the button to submit an order, your code initiates a transaction and then enters the details for the order. If an item is out of stock, your code should handle that scenario gracefully by closing the transaction and presenting a message to the user. Do not leave the transaction active while your code presents the message box and the salesperson acknowledges receipt of the message that the order did not go through. End your transactions as quickly as possible to minimize potential locking conflicts.

Attributes Property

If the adXactCommitRetaining flag is turned on, a new transaction begins when you call CommitTrans. Similarly, if the adXactAbortRetaining flag is turned on, a new transaction starts when you call RollbackTrans. You can change the value of the Attributes property before opening your connection or after you've already established a connection to your database. The new value will take effect the next time you call one of the transaction methods (discussed in the previous section) on the connection.

Keep in mind that if you set the Attributes property to anything other than the default (0) and you begin a transaction, you might need to change this property value in order to close your Connection object. Calling the Close method on the Connection object will cause an error if a transaction is still pending. You can release the Connection object by setting the object to Nothing:

 Set cnDatabase = New ADODB.Connection cnDatabase.CursorLocation = adUseClient cnDatabase.Attributes = adXactCommitRetaining + _ adXactAbortRetaining cnDatabase.Open strConn, strUserID, strPassword 'Start a transaction. cnDatabase.BeginTrans  'Commit the transaction. cnDatabase.CommitTrans 'Because we've set the Attributes property to include ' adXactCommitRetaining, CommitTrans began another transaction. 'Reset the Attributes property to the default and call ' CommitTrans again before closing the transaction. cnDatabase.Attributes = 0 cnDatabase.CommitTrans cnDatabase.Close Set cnDatabase = Nothing 

IsolationLevel Property

If you're building a database application that will simultaneously handle transactions from multiple users, you need to know something about transaction isolation levels. Since entire books—such as Principles of Transaction Processing for the Systems Professional, by Philip A. Bernstein and Eric Newcomer [Morgan Kaufmann Publishers, 1996]—are dedicated to transaction processing, we'll introduce the topic only briefly here. Ideally you'll be convinced that you need to do further homework on this topic.

Most books on transactions state that each transaction should be isolated. Simultaneous transactions should not affect each other. This leads to very simple programming, at least in theory.

Definitions of Some Transactional Terms

dirty read	When one transaction reads uncommitted changes from another transaction.
nonrepeatable read	When data read in transaction A is modified by transaction B before transaction A completes.
phantom row	When transaction A examines the results of a query and then transaction B inserts a row that satisfies the criteria for transaction A's query before transaction A completes. This term comes from the fact that in this case, transaction A could run the same query twice and see a new row appear mysteriously (like a phantom) in the second set of results.

A truly isolated transaction should not be subject to dirty reads, nonrepeatable reads, or phantom rows. Isolating a transaction to this level, however, can lock large quantities of data on the server, leading to deadlocks or poor performance. In many cases, you'll find that you don't need to completely isolate your transactions.

SQL-92 is an ANSI standard for data access. SQL Server supports all four SQL-92 isolation levels, shown in the following table, and uses the Read Committed isolation level by default. You can set the ADO Connection object's IsolationLevel property to use any of the four SQL-92 isolation levels.

SQL-92 Isolation Levels	Dirty Read	Nonrepeatable Read	Phantom Row
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

A word of caution: Leave this feature alone unless you know what you're doing and you've discussed your plans with your database administrator. For example, say you want to see changes pending in another user's transaction, so you set the IsolationLevel property to adXactReadUncommitted (or adXactBrowse), call BeginTrans, and then execute a query to view that data. You will have no way of knowing which of the records you retrieved are marked as pending in another transaction, nor will you receive any notification if the data you read is no longer valid because that pending transaction rolled back.