Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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.

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:

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 TRANS 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-99
Core
Fully compliant.
SQL-99
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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX