Transaction control provides a means of protecting the database from corruption which might arise from two users attempting to the change the same information at the same time and also provides the basis for ensuring database consistency, see Database Consistency.
Optimistic Concurrency Control
Mimer SQL transaction management uses Optimistic Concurrency Control (OCC), which is described in the Mimer SQL Programmer's Manual, chapter 9, Transaction Handling and Database Security.
This type of concurrency control overcomes many of the problems that can occur with conventional locking techniques (e.g. deadlocks and locks being retained by defunct connections). Superior performance is achieved because there is no need for the overhead of a deadlock detection mechanism, since deadlocks cannot occur.
A transaction is an atomic operation. Atomic means that all the changes that form the transaction are applied to the database, or none of them are applied.
Three transaction phases exist: build-up, during which the database operations are requested, prepare, during which the transaction is validated, and commitment, during which the operations performed in the transaction are written to disk.
The transaction begins by taking a snapshot of the database in a consistent state.
During build-up, changes requested to the contents of the database are kept in a write-set and are not visible to other users of the system. This allows the database to remain fully accessible to all users. The application program in which build-up occurs sees the database as though the changes had already been applied. Changes requested during transaction build-up become visible to other users when the transaction is successfully committed.
During build-up, a read-set records the state of the database as seen at the time of each operation (including intended changes). If the state of the database at commitment is inconsistent with the read-set, a conflict is reported and the transaction is rolled back (i.e. the write-set is erased and no changes are made to the database). This can happen if, for instance, a transaction asks to update a row which is deleted by another user after build-up has started but before the transaction is committed. The application program is responsible for taking appropriate action if a transaction conflict occurs.
Transaction control behavior in application programs and a number of the system facilities, notably Backup and Restore - see Backing-up and Restoring Data, is controlled at the databank level by setting the option (LOG, TRANS or NULL) for the databank.
Only operations performed in databanks set up with the LOG option are logged in the Mimer SQL system databank LOGDB. Write operations against tables in LOG and TRANS databanks must be performed under transaction control (i.e. within a transaction).
Refer to the Mimer SQL Programmer's Manual, chapter 9, Transaction Handling and Database Security for more information.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40