Transaction Handling

This topic provides an overview of Transaction Handling, which is used to keep sets of related database updates (transactions) together. Transaction Handling ensures that the updates defined as belonging within a transaction are maintained together as a set. If a transaction is not completed, the updates made since the start of the transaction are deleted from the database and the pre-updated items are restored. This prevents a database from becoming inconsistent due to a process failing in mid-transaction.

The topic describes how to configure a database for Transaction Handling, and how to start and stop Transaction Handling.

The topic also describes Reality's support for distributed transactions, which may be used by applications accessing Reality via the SQL/ODBC interface.

Overview

What is a Transaction?

A transaction is a set of related updates made to a database which are logically grouped together by 'start', 'end' and 'abort' transaction boundary commands. Each update is a single change made to the database, from DataBasic, Proc, TCL or ALL, by creating, altering or deleting an item. An update not belonging to a transaction is described as an ?independent update?. The relationship between updates belonging to a transaction and logically grouped by transaction start and end boundary commands may be defined as follows:

If one update within the transaction is applied to the database, all of the remaining updates within the transaction must be applied in order to maintain a consistent database.

Defining a transaction involves setting boundaries around a set of changes. These boundaries are special commands which mark the beginning and end of a transaction. Transaction boundaries can be defined within the program. A completed transaction is terminated by a transaction commit statement. Hence, a completed transaction is also referred to as a committed transaction.

If a user decides to abandon an operation, or if a program fails, a transaction is rolled back, that is, all changes already made in the transaction are undone.

The following example may make the concept clearer:

Consider a stock control program which generates a set of updates from a single input; first to an Orders file, then to a Customer file, and then finally to a Stock file. The program can be considered in four stages.

1. Details of an order are entered.
2. The Orders file is updated with the name and address of the customer, the goods ordered, the price and the date of order.
3. The Customer file is updated with the date of order, the goods ordered and the price, so that an invoice can be produced.
4. The company Stock file is updated, subtracting the quantity of goods ordered from the current stock.

If the program were aborted after stage 2 and transaction boundaries were not defined appropriately, an order would be sent out, but the customer would not receive the invoice and the Stock file would not be amended, causing 'out of stock' problems in the future.

To maintain a consistent database, stages 1 to 4 must all be completed, that is, they must be maintained as a single transaction. Transaction Handling provides this facility.

What is Transaction Handling?

Transaction Handling ensures that the updates defined as belonging within a transaction are maintained together as a set. If a transaction is not completed, the updates made since the start of the transaction are deleted from the database and the pre-updated items are restored. This restores the database to the consistent state that existed before the transaction started

Transaction Handling also suspends the release of item locks set within transactions. These remain locked until the end of a transaction. This prevents inconsistencies in data due to the simultaneous update of one or more items by processes which are not involved in the transaction.

Transaction Handling supports three transaction boundary commands:

TRANSTART Marks the start of a transaction.

TRANSEND Marks the end of a successful transaction, that is, the transaction is 'committed'.

TRANSABORT Marks the end of an unsuccessful transaction, that is, the transaction is 'rolled back'.

These boundary markers are implemented as TCL commands, DataBasic statements and ALL functions.

A fourth transaction command, TRANSQUERY, can be used to find out the transaction status of the current port. This is executed, either by a TCL command or a DataBasic function.

The transaction boundary markers, TRANSTART, TRANSEND and TRANSABORT, can be used to update existing application code to incorporate transactions. This may require some restructuring of the application in order to collect related updates together, so that they are performed in sequence and can therefore be defined as a transaction. When designing applications to incorporate transactions, the definition of transactions should be an integral part of the design. It is important that transactions are made as short as possible in order to minimise the effect of the Transaction Handling mechanism on the overall efficiency of the system.

For a description of the transaction boundary commands used in DataBasic and ALL, refer to the DataBasic Reference or the ALL Reference Manual. There are also TCL Commands (which can be used to define transactions from Proc).

Executing a Transaction

A transaction is started by executing a TRANSTART from ALL, DataBasic, TCL, or Proc and completed by executing a TRANSEND.

During each transaction the following events occur:

• Whenever a database update occurs within the transaction, a 'Before' image is copied into a central log on disk called the 'raw log'. Each 'Before' image contains enough information to reverse the effect of the change to the database brought about by the update. For example, if you update an existing item, the 'Before' image that is logged contains a copy of the item before it was changed. If you create an item, the 'Before' image that is logged is 'delete item'. 'Before' images are only held for the duration of the transaction.

• Items locked in a transaction are kept locked until a TRANSEND or TRANSABORT is issued. This prevents other transactions or processes reading updated items while the current transaction is still in progress, thus preventing dirty and unrepeatable reads. All item locks set during a transaction remain locked until the 'transaction commit' or 'transaction abort', after which they are released.

  Note

  It is important that all processes use item locking to prevent dirty reads. Items that are updated without having been locked previously are not guaranteed to be recovered correctly by a TRANSABORT.

• TRANSEND generates a 'transaction commit'. Transaction commit indicates that the transaction is completed. 'Before' images are only held in the raw log for the duration of the transaction. Once a transaction is committed, i.e. the 'transaction commit' is logged in the raw log, the 'Before' images are discarded and all the item locks held by the transaction are released.

Aborting a Transaction

If a transaction is aborted, either deliberately by TRANSABORT or by a forced log off occurring in mid-transaction, the transaction is 'rolled back' by applying the 'Before' images to the database in reverse order. The 'Before' images are then discarded.