An SQL transaction is a sequence of related SQL statements which move a database from one consistent status to an new consistent status. Changes to tables are made either entirely or not at all at the end of an SQL transaction.
An SQL transaction starts when no other transaction is open and an SQL statement is issued which opens a transaction. SQL statements which open transactions include all SQL statements other than
ALTER TABLE with the pragma UTILITY MODE ON
DECLARE CURSOR (not executable)
PERMIT
SET CATALOG
SET SCHEMA
SET SESSION AUTHORIZATION
SET TRANSACTION
WHENEVER (not executable)
Utility statements
The statements EXECUTE and EXECUTE IMMEDIATE open a transaction if the relevant dynamically executable statement opens a transaction.
In SESAM/SQL, SQL statements for querying and updating data (see "SQL statements for querying and updating data") may not be executed in a transaction in which an SQL statement for schema definition and administration, for storage structure management or for the administration of user entries is executed. The statements SET SESSION AUTHORIZATION and SET TRANSACTION and utility statements can only be executed outside a transaction. A utility statement is processed internally as a sequence of internal transactions and can therefore not be rolled back.
An SQL transaction is terminated either when the SQL statement COMMIT [WORK] or ROLLBACK [WORK] is executed or if it is rolled back internally by the DBH.
Changes made to the database since the beginning of a particular transaction are only committed after the COMMIT statement has been executed successfully (exception: autonomous transactions, see "SQL transaction").
If a transaction is terminated with ROLLBACK, all the changes made to the database since the start of the transaction are canceled.
If an irrecoverable error, a longlock or deadlock occurs during a transaction, SESAM/SQL executes an implicit ROLLBACK WORK.
If openUTM is used, the termination of a transaction is carried out using UTM language resources only. The SQL statements COMMIT and ROLLBACK must not be used in UTM applications. A UTM transaction ends when the next synchronization point is set.
An SQL transaction is characterized by a specific isolation level (see "SQL transaction") and a specific transaction mode (see "SQL transaction"). The isolation level and transaction mode can be set with the SQL statement SET TRANSACTION. The settings made with SET TRANSACTION are only valid for the SQL statements in the immediately following transaction. It is irrelevant whether it is an SQL or CALL DML transaction or a mixed transaction. Once the transaction has been terminated, the default values are restored.
An autonomous transaction (see "SQL transaction") enables you to write to a database irrespective of the result of the surrounding transaction.
Isolation level
The isolation level specifies the extent to which consistent reading of rows within a transaction can be affected by concurrent accesses by other transactions. Concurrent transactions are simultaneous attempts by two or more application programs to access a single row. Depending on the selected isolation level, the following phenomena can occur with concurrent accesses:
dirty read
A transaction updates a row or inserts a new row. A second transaction reads this updated or new row before the first transaction has committed the changes. This means that the second transaction has read a row which may still be modified or deleted by the first transaction, i.e. which has not been finalized.non-repeatable read
A transaction reads a row. While this transaction is still open, a second transaction updates or deletes the row concerned and commits the changes. If the first transaction again attempts to access this row, either it receives different values or the attempt to access the row is not successful.phantoms
A transaction selects a number of rows from a table on the basis of a specific condition in a query. While this transaction is still open, a second transaction adds rows to the table which would also fulfil the condition. If the first transaction repeats the same query, the derived table additionally contains the new rows.
For reasons of compatibility, it is possible to use CONSISTENCY LEVEL to specify the consistency level instead of the isolation level.
The following table shows the relationship between the consistency level, the isolation level and the various phenomena which can occur.
Isolation level | Consistency levels | dirty read | non-repeatable read | Phantoms |
READ UNCOMMITTED | 0 | x | x | x |
- | 1 | x | x1 | x |
READ COMMITTED | 2 | - | x | x |
REPEATABLE READ | 3 | - | - | x |
SERIALIZABLE | 4 | - | - | - |
Table 35: Isolation level, consistency level and associated phenomena
1The phenomenon “non-repeatable read” may occur if a row was read earlier with a dirty read.
The default value for the isolation level is SERIALIZABLE, which provides complete protection against concurrent transactions. If the isolation level or the consistency level are included in the configuration file for the application program, this value is taken as default. This value must not be higher than the value permitted by the DBH option MAX-ISOLATION-LEVEL. Otherwise SESAM/SQL reports SQLSTATE 91SCL.
Transaction mode
The transaction mode specifies whether rows can only be read or read and updated within a transaction. READ ONLY is default for READ UNCOMMITTED or for consistency levels 0 and 1. In all other cases, READ WRITE is the default. READ UNCOMMITTED is not permitted if the transaction mode READ WRITE is specified at the same time.
Autonomous transaction
The pragma AUTONOMOUS TRANSACTION enables data to be written to a database irrespective of the surrounding transaction. In particular, the data is written persistently to the database before the SQL statement ROLLBACK WORK has possibly executed the transaction.
The pragma is effective only in the case of SQL statement which are to be updated, in other words with INSERT, UPDATE, DELETE (with a search condition), MERGE, and CALL.
The SQL statement after the pragma AUTONOMOUS TRANSACTION is executed in the user’s current transaction, but in a separate runtime environment (own thread, own transaction context).
SQL statements of the user’s for transaction management have no effect. In other words, the user statements COMMIT WORK and ROLLBACK WORK do not influence persistent writing of data by updating SQL statements of autonomous transactions. The user’s SET TRANSACTION statement has no effect on autonomous transactions. The transaction mode of autonomous transactions is READ/WRITE, and the isolation level is the maximum value which the DBH option permits.
Information on user identification, lock conflicts, and aborting the application is provided in the description of the pragma AUTONOMOUS TRANSACTION in the manual “ SQL Reference Manual Part 1: SQL Statements”.