Transaction Processing

Transaction Processing

As we saw in section "Using Transactions" of Chapter 14, transaction processing allows developers to group individual updates to a database into a single logical unit of work.

ADO's transaction processing support is controlled with the ADOConnection component, using the BeginTrans, CommitTrans, and RollbackTrans methods, which have effects similar to those of the corresponding dbExpress and BDE methods. To investigate ADO transaction processing support, you will build a test program called TransProcessing. The program has an ADOConnection component with the ConnectionString set to the Jet 4.0 OLE DB provider and the dbdemos.mdb file. It has an ADOTable component hooked to the Customer table and a DataSource and DBGrid for displaying the data. Finally, it has three buttons to execute the following commands:


With this program, you can make changes to the database table and then roll them back, and they will be rolled back as expected. I emphasize this point because transaction support varies depending on the database and on the OLE DB provider you are using. For example, if you connect to Paradox using the ODBC OLE DB provider, you will receive an error indicating that the database or the OLE DB provider is not capable of beginning a transaction. You can find out the level of transaction processing support you have using the Transaction DDL dynamic property of the connection:

if ADOConnection1.Properties['Transaction DDL'].Value > DBPROPVAL_TC_NONE then

If you are trying to access the same Paradox data using the Jet 4.0 OLE DB provider, you won't receive an error but you also won't be able to roll back your changes, due to limitations of the OLE DB provider.

Another strange difference becomes evident when you're working with Access: If you use the ODBC OLD DB provider, you'll be able to use transactions—but not nested transactions. Opening a transaction when another is active will result in an error. Using the Jet engine, however, Access supports nested transactions.

Nested Transactions

Using the TransProcessing program, you can try this test:

  1. Begin a transaction.

  2. Change the ContactName of the Around The Horn record from Thomas Hardy to Dick Solomon.

  3. Begin a nested transaction.

  4. Change the ContactName of the Bottom-Dollar Markets record from Elizabeth Lincoln to Sally Solomon.

  5. Roll back the inner transaction.

  6. Commit the outermost transaction.

The net effect is that only the change to the Around The Horn record is permanent. If, however, the inner transaction had been committed and the outer transaction rolled back, then the net effect would have been that none of the changes were permanent (even the changes in the inner transaction). This is as you would expect, with the only limit being that Access supports five levels of nested transactions.

ODBC does not support nested transactions, the Jet OLE DB provider supports up to five levels of nested transactions, and the SQL Server OLE DB provider doesn't support nesting at all. You might get a different result depending on the version of SQL server or the driver, but the documentation and my experiments with the servers seem to indicate that this is the case. Apparently only the outermost transaction decides whether all the work is committed or rolled back.

ADOConnection Attributes

There is another issue you should consider if you intend to use nested transactions. The ADOConnection component has an Attributes property that determines how the connection should behave when a transaction is committed or rolled back. It is a set of TXActAttributes that, by default, is empty. TXActAttributes contains only two values: xaCommitRetaining and xaAbortRetaining (this value is often mistakenly written as xaRollbackRetaining—a more logical name for it). When xaCommitRetaining is included in Attributes and a transaction is committed, a new transaction is automatically started. When xaAbortRetaining is included in Attributes and a transaction is rolled back, a new transaction is automatically started. Thus if you include these values in Attributes, a transaction will always be in progress, because when you end one transaction another will always be started.

Most programmers prefer to be in greater control of their transactions and not to allow them to be automatically started, so these values are not commonly used. However, they have a special relevance to nested transactions. If you nest a transaction and set Attributes to [xaCommitRetaining, xaAbortRetaining], then the outermost transaction can never be ended. Consider this sequence of events:

  1. An outer transaction is started.

  2. An inner transaction is started.

  3. The inner transaction is committed or rolled back.

  4. A new inner transaction is automatically started as a consequence of the Attributes property.

The outermost transaction can never be ended, because a new inner transaction will be started when one ends. The conclusion is that the use of the Attributes property and the use of nested transactions should be considered mutually exclusive.

Lock Types

ADO supports four different approaches to locking your data for update: ltReadOnly, ltPessimistic, ltOptimistic, and ltBatchOptimistic (there is also an ltUnspecified option, but for the reasons mentioned earlier, we will ignore unspecified values). The four approaches are made available through the dataset's LockType property. In this section I will provide an overview of the four approaches, and in subsequent sections we will take a closer look at them.

The ltReadOnly value specifies that data is read-only and cannot be updated. As such, there is effectively no locking control required, because the data cannot be updated.

The ltPessimistic and ltOptimistic values offer the same pessimistic and optimistic locking control as the BDE. One important benefit that ADO offers over the BDE in this respect is that the choice of locking control remains yours. If you use the BDE, the decision to use pessimistic or optimistic locking is made for you by your BDE driver. If you use a desktop database such as dBase or Paradox, then the BDE driver uses pessimistic locking; if you use a client/server database such as InterBase, SQL Server, or Oracle, the BDE driver uses optimistic locking.

Pessimistic Locking

The words pessimistic and optimistic in this context refer to the developer's expectation of conflict between user updates. Pessimistic locking assumes that there is a high probability that users will attempt to update the same records at the same time and that a conflict is likely. In order to prevent such a conflict, the record is locked when the edit begins. The record lock is maintained until the update is completed or cancelled. A second user who attempts to edit the same record at the same time will fail in their attempt to place their record lock and will receive a "Could not update; currently locked" exception.

This approach to locking will be familiar to developers who have worked with desktop databases such as dBase and Paradox. The benefit is that the user knows that if they can begin editing a record, then they will succeed in saving their update. The disadvantage of pessimistic locking is that the user is in control of when the lock is placed and when it is removed. If the user is skilled with the application, then this lock could be as short as a couple of seconds. However, in database terms, a couple of seconds is an eternity. On the other hand, the user might begin an edit and go to lunch, and the record would be locked until the user returns. As a consequence, most proponents of pessimistic locking guard against this eventuality by using a Timer or other such device to time out locks after a certain amount of keyboard and mouse inactivity.

Another problem with pessimistic locking is that it requires a server-side cursor. Earlier we looked at cursor locations and saw that they have an impact on the availability of the different cursor types. Now you can see that cursor locations also have an impact on locking types. Later in this chapter, we will discuss more benefits of client-side cursors; if you choose to take advantage of these benefits, then you'll be unable to use pessimistic locking.

Pessimistic locking is an area of dbGo that changed in Delphi 6 (compared to Delphi 5). This section describes the way pessimistic locking works in versions 6 and 7. To highlight how it works, I've built the PessimisticLocking example. It is similar to other examples in this chapter, but the CursorLocation property is set to clUseServer and the LockType property is set to ltPessimistic. To use it, run two copies from Windows Explorer and attempt to edit the same record in both running instances of the program: You will fail because the record is locked by another user.

Part I: Foundations