This chapter discusses transaction principles, logging and handling transactions.
A transaction is an environment where it is possible to COMMIT all of the operations performed within it, or to ensure that all of them fail.
In general, three transaction phases exist:
•build-up, during which the database operations are requested
•prepare, during which the transaction is validated
•commitment, during which the operations performed in the transaction are written to disk.
Transaction build-up, which may be started explicitly or implicitly; prepare and commitment are both initiated explicitly through a request to commit the transaction (using COMMIT).
In interactive application programs, build-up takes place typically over a time period determined by the user, while prepare and commitment are part of the internal process of committing a transaction, which occurs on a time-scale determined by machine operations.
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 will see the database as though the changes had already been applied. All changes requested during transaction build-up become visible to other users when the transaction is successfully committed.
A major function of the transaction handling in Mimer SQL multi-user systems is concurrency control. This means protecting the database from inconsistency which might arise when two users attempt to change the same information at the same time.
Mimer SQL supports distributed transactions based on the XA interface as defined by the Open Group and Microsoft's Distributed Transaction Coordinator (DTC) protocol. This means that Mimer SQL can be used in application environments that support distributed transactions.
See the Mimer SQL Programmer's Manual, Transaction Handling and Database Security, for a more detailed discussion of transaction handling and database security.
Transaction control also provides the basis for protection of the database against hardware failure.
Changes made to a database may be logged, to provide back-up protection in the event of hardware failure, provided that the changes occur within a transaction and that the databanks involved have the LOG option. Transaction handling is, therefore, important even in standalone environments where concurrency control issues do not arise.
The system logging databank, LOGDB is where transaction changes are recorded. It contains a record of all transactions executed since the latest back-up copy of a databank was taken and the log cleared. The latest back-up copy of the databank, together with the contents of LOGDB, may be used to restore the databank in the event of a databank crash.
Transaction control and logging is determined at the databank level by options set when the databank is defined.
The options are:
|
Description |
|
|---|---|
|
LOG |
All operations on the databank are performed under transaction control. All transactions are logged. |
|
TRANSACTION |
All operations on the databank are performed under transaction control. No transactions are logged. |
|
WORK |
All operations on the databank are performed without transaction control (even if they are requested within a transaction), and are not logged. Sets of operations (DELETE, UPDATE or INSERT on several rows) which are interrupted will not be rolled back. |
|
READ ONLY |
Only read only operations are allowed, i.e. DELETE, UPDATE or INSERT can not be performed on tables in a databank with this option. |
All important databanks should be defined with LOG option, so that valuable data is not lost by any system failure.
Transaction control statements in Mimer SQL are:
•COMMIT
•COMMIT BACKUP
•ROLLBACK
•SET TRANSACTION READ ONLY
•SET TRANSACTION READ WRITE
•SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
•SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
•SET TRANSACTION ISOLATION LEVEL READ COMMITTED
•SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
•SET TRANSACTION START EXPLICIT
•SET TRANSACTION START IMPLICIT
•SET TRANSACTION DIAGNOSTICS SIZE
•SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY
•SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE
•SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
•SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ
•SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
•SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
•SET SESSION CHARACTERISTICS AS TRANSACTION DIAGNOSTICS SIZE
•START BACKUP
•START TRANSACTION
SQL Statement Restrictions in Transactions
The following SQL statements may not be used inside a transaction:
|
ENTER |
SET SHADOW |
|
LEAVE |
SET TRANSACTION |
|
SET DATABANK |
START BACKUP |
|
SET DATABASE |
START TRANSACTION |
|
SET SESSION |
|
Data definition statements (e.g. ALTER, DROP, UPDATE STATISTICS) may be used inside a transaction provided they are the only statement executed in that transaction.
It is strongly recommended that the SET TRANSACTION READ ONLY option be used for each transaction that does not perform updates to the database and that the SET TRANSACTION READ WRITE option be used only when a transaction performs updates.
Taking a little extra care to set these options appropriately will ensure the transaction performance remains optimal at all times.
The default transaction read option can be defined by using SET SESSION, see Default Transaction Options. If this has not been used to set the default transaction read option, the default is READ WRITE.
Consistency Within a Transaction
The SET TRANSACTION ISOLATION LEVEL options are provided to control the degree to which the updates performed by one transaction are affected by the updates performed by other transactions which are executing concurrently.
The default isolation level can be defined by using SET SESSION, see Default Transaction Options. If this has not been used to set a default isolation level, the default is REPEATABLE READ. This isolation level guarantees that the end result of the operations performed by two or more concurrent transactions is the same as if the transactions had been executed in a serial fashion, except that an effect known as ‘Phantoms’ may occur.
This is where one transaction reads a set of rows that satisfy some search condition. Another transaction then performs an update which generates one or more new rows that satisfy that search condition. If the original query is repeated (using exactly the same search condition), extra rows appear in the result set that were previously not found.
The other isolation levels are: READ UNCOMMITTED, READ COMMITTED and SERIALIZABLE.
All four isolation levels guarantee that each transaction will be executed completely or not at all and that no updates will be lost.
Refer to Mimer SQL Reference Manual, SET TRANSACTION, for a full description of the effects that are possible, or guaranteed never to occur, at each of the four isolation levels.
•SET SESSION
The SET SESSION statement is provided so that default values for certain transaction control settings can be defined.
SET SESSION allows the default settings for SET TRANSACTION READ and SET TRANSACTION ISOLATION LEVEL to be defined.
•SET TRANSACTION READ and SET TRANSACTION ISOLATION LEVEL
The transaction control settings defined by SET TRANSACTION READ, see Optimizing Transactions, and SET TRANSACTION ISOLATION LEVEL, see Consistency Within a Transaction, apply to the single next transaction to be started. If these statements are not used explicitly before each transaction, the default settings apply.