www.mimer.com/developer

Handling Transactions

---

Transaction control statements in Mimer SQL are:

• COMMIT;
• 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 READ ONLY;
• SET SESSION READ WRITE;
• SET SESSION ISOLATION LEVEL SERIALIZABLE;
• SET SESSION ISOLATION LEVEL REPEATABLE READ;
• SET SESSION ISOLATION LEVEL READ COMMITTED;
• SET SESSION ISOLATION LEVEL READ UNCOMMITTED;
• SET SESSION DIAGNOSTICS SIZE;
• START TRANSACTION;

SQL Statement Restrictions in Transactions

The following SQL statements may not be used inside a transaction:

ENTER	SET SESSION
LEAVE	SET SHADOW
SET DATABANK	SET TRANSACTION
SET DATABASE	START TRANSACTION

The following SQL statements may be used inside a transaction provided they are the only statement executed in that transaction:

ALTER	CREATE TRIGGER
COMMENT	DROP DATABANK
CREATE BACKUP	DROP FUNCTION
CREATE DATABANK	DROP INDEX
CREATE FUNCTION	DROP MODULE
CREATE INCREMENTAL BACKUP	DROP PROCEDURE
CREATE INDEX	DROP SCHEMA
CREATE MODULE	DROP SHADOW
CREATE PROCEDURE	DROP TABLE
CREATE SCHEMA	DROP TRIGGER
CREATE SHADOW	UPDATE STATISTICS
CREATE TABLE

In addition, the following BSQL commands, see Mimer BSQL, may not be used inside a transaction:

EXIT

LOAD

UNLOAD

Transaction Handling in BSQL

Normal Mimer SQL transaction handling behavior applies in BSQL. The default transaction start setting of implicit means that, by default, a transaction is started whenever one is needed.

For a detailed description of transaction handling behavior in Mimer SQL, refer to the Mimer SQL Programmer's Manual.

A special feature of BSQL is that all implicitly started transactions are automatically committed at the end of each statement, so that by default no attention needs to be paid to transaction handling at all in BSQL.

The START and COMMIT (or ROLLBACK) statements may be used together to group a number of statements into a single transaction when this is required.

Any transactions explicitly started using START will not be automatically committed by BSQL, so COMMIT or ROLLBACK must be used.

Optimizing Transactions

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 the description of SET TRANSACTION in the Mimer SQL Reference Manual for a full description of the effects that are possible, or guaranteed never to occur, at each of the four isolation levels.

Exception Diagnostics Within Transactions

• SET TRANSACTION DIAGNOSTICS SIZE

  The SET TRANSACTION DIAGNOSTICS SIZE option allows the size of the diagnostics area to be defined. A 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 (5 or whatever has been defined to be the default by using SET SESSION) applies unless an alternative is explicitly set before each transaction.

Default Transaction Options

• 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.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se