Using Transactions

Go Up to Understanding Databases and Datasets

A transaction is a group of actions that must all be carried out successfully on one or more tables in a database before they are committed (made permanent). If one of the actions in the group fails, then all actions are rolled back (undone). By using transactions, you ensure that the database is not left in an inconsistent state when a problem occurs completing one of the actions that make up the transaction.

For example, in a banking application, transferring funds from one account to another is an operation you would want to protect with a transaction. If, after decrementing the balance in one account, an error occurred incrementing the balance in the other, you want to roll back the transaction so that the database still reflects the correct total balance.

By default, implicit transaction control is provided for your applications. When an application is under implicit transaction control, a separate transaction is used for each record in a dataset that is written to the underlying database. Implicit transactions guarantee both a minimum of record update conflicts and a consistent view of the database. On the other hand, because each row of data written to a database takes place in its own transaction, implicit transaction control can lead to excessive network traffic and slower application performance. Also, implicit transaction control will not protect logical operations that span more than one record, such as the transfer of funds described previously.

If you explicitly control transactions, you can choose the most effective times to start, commit, and roll back your transactions. When you develop applications in a multi-user environment, particularly when your applications run against a remote SQL server, you should control transactions explicitly.

Note:

InterBase does not support nested transactions.
You can also minimize the number of transactions you need by caching updates. For more information about cached updates, see Working with Cached Updates.

Using a Transaction Component

When you start a transaction, all subsequent statements that read from and write to the database occur in the context of that transaction. Each statement is considered part of a group. Changes must be successfully committed to the database, or every change made in the group must be undone.

Ideally, a transaction should only last as long as necessary. The longer a transaction is active, the more simultaneous users that access the database, and the more concurrent, simultaneous transactions that start and end during the lifetime of your transaction, the greater the likelihood that your transaction will conflict with another when you attempt to commit your changes.

When using a transaction component, you code a single transaction as follows:

Start the transaction by calling the transaction’s StartTransaction method:
```
IBTransaction.StartTransaction;
```
Once the transaction is started, all subsequent database actions are considered part of the transaction until the transaction is explicitly terminated. You can determine whether a transaction is in process by checking the transaction component’s InTransaction property.
When the actions that make up the transaction have all succeeded, you can make the database changes permanent by using the transaction component’s Commit method:
```
IBTransaction.Commit;
```
Alternately, you can commit the transaction while retaining the current transaction context using the CommitRetaining method:
```
  IBTransaction.CommitRetaining;
```
Commit is usually attempted in a try...except statement. That way, if a transaction cannot commit successfully, you can use the except block to handle the error and retry the operation or to roll back the transaction.
If an error occurs when making the changes that are part of the transaction, or when trying to commit the transaction, you will want to discard all changes that make up the transaction. To discard these changes, use the database component’s Rollback method:
```
IBTransaction.Rollback;
```
You can also rollback the transaction while retaining the current transaction context using the RollbackRetaining method:
```
   IBTransaction.RollbackRetaining;
```
Rollback usually occurs in
- Exception handling code when you cannot recover from a database error.
- Button or menu event code, such as when a user clicks a Cancel button.

Advance To:

Caching Updates