SET TRANSACTION

---

Sets transaction modes for transactions.

Usage

Embedded/Interactive/Procedural.

Description

SET TRANSACTION READ

The SET TRANSACTION READ setting only affects the single next transaction to be started.

The default SET TRANSACTION READ setting (READ WRITE or whatever has been defined to be the default by using SET SESSION) applies unless an alternative is explicitly set before each transaction.

The SET TRANSACTION READ ONLY option is provided so that transaction performance can be optimized for those transactions not performing any updates.

It is strongly recommended that SET TRANSACTION READ ONLY be used for each transaction that does not require update access to the database and that READ WRITE mode only be used for transactions actually performing updates.

Significant performance gains can be achieved, especially for queries retrieving large numbers of rows, when the SET TRANSACTION READ options are used as recommended.

SET TRANSACTION ISOLATION LEVEL

The SET TRANSACTION ISOLATION LEVEL options are provided to control the degree to which the updates performed by a transaction are affected by the updates performed by concurrent transactions.

The SET TRANSACTION ISOLATION LEVEL setting only affects the single next transaction to be started.

The default SET TRANSACTION ISOLATION LEVEL setting (REPEATABLE READ or whatever has been defined to be the default by using SET SESSION) applies unless an alternative is explicitly set before each transaction.

If SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is specified, then a transaction access mode of READ ONLY is implicit for the single next transaction.

All of the isolation levels guarantee that each transaction will be executed completely or not at all and that no updates will be lost.

The execution of concurrent transactions at the most secure isolation level, SERIALIZABLE, guarantees that the execution of the operations of concurrently executing transactions produces the same effect as some serial execution of those same transactions (i.e. an execution where one transaction executes to completion before the next begins).

When the other isolation levels are in effect (READ UNCOMMITTED, READ COMMITTED and REPEATABLE READ), the following effects may occur during the execution of concurrent transactions:

• `Dirty Read' - this is where uncommitted updates can be read by another transaction. This can lead to a situation, in the event of a rollback occurring in an update transaction after another transaction has performed a read, where data has been read which (because it was never committed) must be considered to have never existed.
• `Non-repeatable Read' - this is where a transaction reads a row and then another transaction updates or deletes that specific row. A subsequent attempt to re-read the same specific row retrieves modified information or finds that the row no longer exists, thus it can be said that the original read cannot be repeated.
• `Phantoms' - this is where a 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 the search condition. If the original query is repeated (using the same search condition), extra rows appear in the result-set that where previously not found.

The following table summarizes, for each of the four isolation levels, which of the affects described above are guaranteed never to occur, or must be accepted as possible, where there are concurrent transactions:

Isolation Level	Dirty Read	Non-repeatable Read	Phantoms
READ UNCOMMITTED	POSSIBLE	POSSIBLE	POSSIBLE
READ COMMITTED	NEVER OCCURS	POSSIBLE	POSSIBLE
REPEATABLE READ	NEVER OCCURS	NEVER OCCURS	POSSIBLE
SERIALIZABLE	NEVER OCCURS	NEVER OCCURS	NEVER OCCURS

SET TRANSACTION DIAGNOSTICS SIZE

The SET TRANSACTION DIAGNOSTICS SIZE option allows the size of the diagnostics area to be defined. The unsigned-integer value specifies how many exceptions can be stacked in the diagnostics area, and examined by GET DIAGNOSTICS, in situations where repeated RESIGNAL operations have effectively been performed.

The SET TRANSACTION DIAGNOSTICS SIZE setting only affects the single next transaction to be started.

The default SET TRANSACTION DIAGNOSTICS SIZE setting (50 or whatever has been defined to be the default by using SET SESSION) applies unless an alternative is explicitly set before each transaction.

SET TRANSACTION START

Transactions are started either by an explicit START statement or by an implicit transaction start. The procedure that is followed is determined by using the SET TRANSACTION START statement.

When START is set to IMPLICIT, the first operation involving a databank with either the TRANSACTION or LOG option will start a transaction. The transaction must then be terminated explicitly by either COMMIT or ROLLBACK.

The SET TRANSACTION START setting has effect in the current session until SET TRANSACTION START is next used.

The default setting is START IMPLICIT.

Restrictions

The SET TRANSACTION statement may not be issued within a transaction.

A SET SESSION READ setting or a SET SESSION ISOLATION LEVEL setting may not be changed if there are any holdable cursors remaining open from the previous transaction.

Notes

The SET TRANSACTION START statement is generally issued at the beginning of a session, to set the start mode for transactions. Changing the start mode for transactions in the middle of a session is not generally recommended.

The SET SESSION statement can be used to define the default settings for the SET TRANSACTION READ, SET TRANSACTION ISOLATION LEVEL, and SET TRANSACTION DIAGNOSTICS SIZE options.

Example

 exec sql SET TRANSACTION START EXPLICIT;
 LOOP
    exec sql FETCH C1 INTO :var1,:var2,...,:varn;
    display var1,var2,...,varn;
    prompt "Update row?";
    exit when answer = "yes";
 END LOOP
 exec sql START;
 exec sql UPDATE table SET ...
          WHERE col1 = :var1,
                col2 = :var2, ...;
 exec sql COMMIT;

Standard Compliance

Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.
SQL-2003	Features outside core	Feature F111, "Isolation levels other than serializable" support for READ UNCOMMITTED, READ COMMITTED and REPEATABLE READ. Feature F121, "Basic diagnostics management" support for DIAGNOSTICS SIZE.
	Mimer SQL extension	The form TRANSACTION START is a Mimer SQL extension.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se