Sets transaction modes for transactions.
- SET TRANSACTION READ
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.
- SET TRANSACTION ISOLATION LEVEL
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.
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).
- "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:
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.
- SET TRANSACTION START
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 statement may not be issued within a transaction.
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.
EXTENDED Support for SET TRANSACTION START is a Mimer SQL extension.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40