Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

Transaction Control Statements

The following sections explain how to start, end and optimize transactions. You can also read about consistency and exception diagnostics in transactions. Further, transaction options, cursors and error handling are discussed.

Starting Transactions

Transaction start may be set to EXPLICIT or IMPLICIT.

The default transaction start setting is IMPLICIT, which means a transaction will be started automatically whenever one is needed.

To set the transaction start mode, use the statements: 
 SET TRANSACTION START EXPLICIT;
 
 SET TRANSACTION START IMPLICIT;

Different database connections can use different transaction start options.

The START statement can always be used to explicitly start a transaction. This is useful if a number of related updates are to be performed and it is desirable that all the updates succeed or fail together to maintain consistency.

You cannot start a transaction while a transaction is already active.

Explicit Transaction Start

With this setting, transactions are never automatically started. All transactions must be explicitly started by executing the START statement.

Any update operation (INSERT, UPDATE or DELETE) involving a table in a databank with the TRANS or LOG option must occur within a transaction. An error will be raised if such an update is attempted without first starting a transaction.

All the statements issued after the START statement and before the transaction is concluded are grouped together within that single transaction.

A transaction is concluded by executing a COMMIT or ROLLBACK statement.

Implicit Transaction Start

With this setting, a transaction is started automatically (if one is not already active) by a reference to an object stored in a databank with the TRANS or LOG option (i.e. if none of the objects referenced are stored in a databank with the TRANS or LOG option, no transaction is required and therefore one is not started).

The START statement may be used to explicitly start a transaction if required, typically to allow several updates to be grouped together within a single transaction for consistency, as already described.

An automatically started transaction is concluded by executing a COMMIT or ROLLBACK statement.

All the statements issued after the initiating update and before the concluding COMMIT or ROLLBACK statement are grouped together within that single transaction.

Ending Transactions

Transactions must be ended with the COMMIT or ROLLBACK statement.

Note: The keyword ROLLBACK is used in Mimer SQL for compatibility with SQL standards. A transaction in Mimer SQL is never physically rolled-back in the sense of undoing changes made to the database, since changes are not actually effected until a successful COMMIT is performed.

Transactions that are not successfully committed due to a transaction conflict do not have to be explicitly rolled back. The ROLLBACK statement is most commonly used in exception routines for handling error situations that are detected by the application during transaction build-up.

If a connection or program is terminated without requesting a COMMIT or ROLLBACK for the current transaction, the system will abort the transaction. None of the changes requested during the transaction build-up will be made to the database.

Transaction handling in BSQL differs slightly from that described here - see the Mimer SQL User's Manual, Handling Transactions, for details.

Optimizing Transactions

The following SET TRANSACTION options are used to optimize transaction performance:

The default option is READ WRITE, or the option defined to be the default for the current session by using the SET SESSION statement, see Setting Default Transaction Options.

The SET TRANSACTION READ command only affects the single next transaction started after it is used.

Consistency Within Transactions

The SET TRANSACTION ... ISOLATION LEVEL options can be used to control the degree to which the changes occurring within one transaction are affected by the changes occurring within other concurrently executing transactions.

The default option is REPEATABLE READ, or the option defined to be the default for the current session by using the SET SESSION statement, see Setting Default Transaction Options.

The SET TRANSACTION ... ISOLATION LEVEL command only affects the single next transaction started after it is used.

Options

The following options are available:

For a definition of the concurrency effects mentioned above (phantoms, non-repeatable read and dirty read) refer to the Mimer SQL Reference Manual, SET TRANSACTION.

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

Exception Diagnostics Within Transactions

The SET TRANSACTION DIAGNOSTICS SIZE option allows the size of the diagnostics area to be defined. An unsigned integer value specifies how many exceptions can be stacked in the diagnostics area, and examined by GET DIAGNOSTICS, see the Mimer SQL Reference Manual, 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.

Setting Default Transaction Options

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

As these SET TRANSACTION commands only affect the single next transaction started after they are used, it is often convenient to define the desired default options for each of them.

A detailed description of the SET SESSION statement can be found in the Mimer SQL Reference Manual.

Statements in Transactions

The tables that follow summarize whether statements may or may not be used inside transactions.

Access Control Statements

Statements
Allowed
Comments
GRANT
REVOKE
Yes
Must be the only statement in a transaction.

Connection Statements

Statements
Allowed
Comments
CONNECT
SET CONNECTION
Yes

DISCONNECT
Yes
A ROLLBACK is performed on any active transaction.
ENTER
LEAVE (program ident)
No

Data Definition Statements

Statements
Allowed
Comments
ALTER
COMMENT
CREATE
DROP
Yes
Must be the only statement in a transaction.

Data Manipulation Statements

Statements
Allowed
Comments
SELECT EXPRESSION
SELECT INTO
FETCH
INSERT
DELETE
DELETE CURRENT
UPDATE
UPDATE CURRENT
Yes

OPEN
CLOSE
Yes
ROLLBACK closes all open cursors.
COMMIT closes all open non-holdable cursors.

Declarative Statements

Statements
Allowed
Comments
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE VARIABLE
Not applicable
Declarative statement

Diagnostic Statements

Statements
Allowed
Comments
GET DIAGNOSTICS
RESIGNAL
SIGNAL
Yes

Dynamic SQL Statements

Statements
Allowed
Comments
PREPARE
DESCRIBE
EXECUTE
EXECUTE IMMEDIATE
EXECUTE STATEMENT
ALLOCATE CURSOR
ALLOCATE DESCRIPTOR
DEALLOCATE DESCRIPTOR
DEALLOCATE PREPARE
GET DESCRIPTOR
SET DESCRIPTOR
Yes
See Dynamic SQL.

ESQL Control Statements

Statements
Allowed
Comments
DECLARE SECTION
WHENEVER
Not applicable
Declarative statement

Procedure Control Statements

Statements
Allowed
Comments
CALL
CASE
COMPOUND STATEMENT
IF
LEAVE
LOOP
REPEAT
RETURN
SET
WHILE
Yes

System Administration Statements

Statements
Allowed
Comments
ALTER DATABANK RESTORE
CREATE BACKUP
SET DATABANK
SET DATABASE
SET SHADOW
UPDATE STATISTICS
No
These statements create internal transactions to ensure data dictionary consistency

Transaction Control Statements

Statements
Allowed
Comments
SET SESSION
SET TRANSACTION
START
No
These statements control transaction behavior
COMMIT
ROLLBACK
Yes

Cursors in Transactions

A cursor open by the current connection may be closed implicitly by one of the transaction terminating statements COMMIT and ROLLBACK. ROLLBACK closes all open cursors for the current connection. COMMIT closes all open cursors for the current connection, except cursors declared WITH HOLD. Holdable cursors remain open after COMMIT.

When a stacked cursor is closed, all instances of the cursor are closed.

Cursors are also closed implicitly by LEAVE and DISCONNECT. In SET TRANSACTION START EXPLICIT mode, cursors may be opened and used outside transactions. Such cursors remain accessible when an ENTER statement is issued, and remain open when a LEAVE statement is issued.

This is illustrated in the following statement sequence: 

 ...
 EXEC SQL SET TRANSACTION START EXPLICIT;
 
 EXEC SQL DECLARE c_1 CURSOR FOR SELECT col1
                                    FROM tab1;
 EXEC SQL DECLARE c_2 CURSOR FOR SELECT col2
                                    FROM tab2
                                    WHERE checkcol = :VAR1;
 
 EXEC SQL OPEN c_1;
 
 loop
    EXEC SQL FETCH c_1
                INTO :VAR1;     -- Fetch value from tab1
 
    EXEC SQL ENTER ... ;        -- Change current ident
 
       EXEC SQL OPEN c_2;
       EXEC SQL FETCH c_2
                   INTO ...;    -- Fetch row for c_2
       EXEC SQL CLOSE c_2;
 
    EXEC SQL LEAVE;
 end loop;
 ...

In the above example, the value fetched for the cursor C1 is used to determine the set of rows addressed by cursor C2. Cursor C1 remains open and positioned during the ENTER ... LEAVE sequence.

Each time the loop is executed, a new value is fetched by C1 and a new set of rows is addressed by C2. The same behavior applies when LEAVE RETAIN is used to leave a PROGRAM ident but keep the environment for the ident.

A cursor opened and used outside a transaction may however not be used within a transaction. If the same cursor is required outside and inside a transaction, separate instances must be opened. Remember that separate instances of a cursor address separate result sets: 

 ...
 EXEC SQL SET TRANSACTION START EXPLICIT;
 
 EXEC SQL DECLARE c_1 REOPENABLE CURSOR FOR SELECT col1
                                               FROM tab1;
 
 EXEC SQL OPEN c_1;
 EXEC SQL FETCH c_1
             INTO ...;       -- First row (outside transaction)
 ...
 EXEC SQL START;
 EXEC SQL OPEN c_1;          -- New instance of cursor
 EXEC SQL FETCH c_1
             INTO ...;       -- First row again
 ...

Error Handling in Transactions

In general, errors and exception conditions are reported in SQLSTATE after each executable SQL statement.

The value of SQLSTATE indicates the outcome of the preceding statement, see SQLSTATE Return Codes for a list of SQLSTATE values.

GET DIAGNOSTICS can be used to get detailed status information after an SQL statement.

The value of SQLSTATE after a COMMIT statement indicates the success or failure of the request to commit the transaction, not the outcome of any data manipulations performed within the transaction.

About WHENEVER

Use of the general error handling statement WHENEVER, see the Mimer SQL Reference Manual, WHENEVER, for a description) in transactions requires some care:

If WHENEVER error handling is used in an application program, a suitable program structure for COMMIT statements is: 

 EXEC SQL WHENEVER SQLERROR GOTO exception;
 ...
 EXEC SQL WHENEVER SQLERROR GOTO retry;
 EXEC SQL COMMIT;
 EXEC SQL WHENEVER SQLERROR GOTO exception;
 ...

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