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.
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 an update involving a reference to an object stored in a databank with the TRANS or LOG option (i.e. if none of the objects referenced in the update 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.
Transactions must be ended with the COMMIT or ROLLBACK statement.
This statement requests that the operations in the write-set are executed on the database, making the changes permanent and visible to other users. The SQLSTATE value returned when a COMMIT statement is executed indicates either that the transaction commitment was successful (SQLSTATE = '00000') or that a transaction conflict occurred (SQLSTATE <> '00000').
- 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, chapter 8, Handling Transactions, for details.
The following SET TRANSACTION options are used to optimize transaction performance:
- READ ONLY
This setting should always be used for transactions that do not require update access to the database. Significant performance gains can be achieved, especially for queries retrieving large numbers of rows, when this setting is used in queries when there is no need for update access to the database.
- READ WRITE
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.
The following options are available:
This setting guarantees that the end result of the operations performed by two or more concurrent transactions will be the same as if the transactions had been executed in a serial fashion, where one executes to completion before the other starts.
- REPEATABLE READ
This setting offers the same consistency guarantee as serializable, except that the concurrency effect known as phantoms may be encountered (see below for a reference to the definition of this concurrency effect).
- READ COMMITTED
This setting offers the same consistency guarantee as repeatable read, except that the concurrency effect known as non-repeatable read may also be encountered (see below for a reference to the definition of this concurrency effect).
- READ UNCOMMITTED
This setting offers the same consistency guarantee as read committed, except that the concurrency effect known as dirty read may also be encountered (see below for a reference to the definition of this concurrency effect).
For a definition of the concurrency effects mentioned above (phantoms, non-repeatable read and dirty read) refer to the Mimer SQL Reference Manual, Chapter 12, 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, Chapter 12, 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
CONNECTSET CONNECTION Yes DISCONNECT Yes A ROLLBACK is performed on any active transaction. ENTERLEAVE (program ident) No
Data Definition Statements
Data Manipulation Statements
SELECT EXPRESSIONSELECT INTOFETCHINSERTDELETEDELETE CURRENTUPDATEUPDATE CURRENT Yes OPENCLOSE Yes COMMIT and ROLLBACK close any open cursors
DECLARE CONDITIONDECLARE CURSORDECLARE HANDLERDECLARE VARIABLE Not applicable Declarative statement
GET DIAGNOSTICSRESIGNALSIGNAL Yes
Dynamic SQL Statements
PREPAREDESCRIBEEXECUTEEXECUTE IMMEDIATEALLOCATE CURSORALLOCATE DESCRIPTORDEALLOCATE DESCRIPTORDEALLOCATE PREPAREGET DESCRIPTORSET DESCRIPTOR Yes See Dynamic SQL.
ESQL Control Statements
DECLARE SECTIONWHENEVER Not applicable Declarative statement
Procedure Control Statements
CALLCASECOMPOUND STATEMENTIFLEAVELOOPREPEATRETURNSETWHILE Yes
System Administration Statements
ALTER DATABANK RESTORECREATE BACKUPCREATE INCREMENTAL BACKUPSET DATABANKSET DATABASESET SHADOWUPDATE STATISTICS No These statements create internal transactions to ensure data dictionary consistency
Transaction Control Statements
SET SESSIONSET TRANSACTIONSTART No These statements control transaction behavior COMMITROLLBACK Yes
Cursors in Transactions
The transaction terminating statements COMMIT and ROLLBACK automatically close any cursors opened by the current connection, regardless of whether they were opened before or after the transaction was started.
If a cursor is stacked, all instances of the cursor are closed. Any cursors that may be retained in dormant connections are not affected. Cursors are also closed automatically by LEAVE and DISCONNECT.
In SET TRANSACTION START EXPLICIT mode, cursors may be opened and used outside transactions. Such cursors remain open when an ENTER or 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 is rows 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.
Use of the general error handling statement WHENEVER, see the Mimer SQL Reference Manual, Chapter 12, WHENEVER, for a description) in transactions requires some care:
- Program control can be transferred to an exception routine in the event of an error. Make sure that the exception routine is designed to take care of uncompleted transactions.
Most commonly, the first SQL statement in the exception routine should be GET DIAGNOSTICS. The exception routine should normally also execute a ROLLBACK statement. Remember that if the exception routine is used from a statement outside a transaction, any open cursors belonging to the current ident will be closed by the ROLLBACK statement. GET DIAGNOSTICS can be used to determine whether or not a transaction is active.
- For transaction conflict, the SQLSTATE value returned from the COMMIT statement falls into the SQLERROR class. If the transaction is to be retried in the event of conflict, make sure that no WHENEVER SQLERROR GOTO exception statement is operative.
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; ...
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40