Transactions

The first transaction is opened when a database session is opened with a CONNECT statement. You can use a SET statement to change the properties of a database session. The transaction is concluded with the COMMIT, ROLLBACK, or RELEASE statement. The RELEASE statement also closes the database session.

Subtransactions

A transaction can be divided into other basic units known as subtransactions. You can use one of the following procedures to open or close subtransactions:

?     Using the SUBTRANS statements
A subtransaction is initiated by the SUBTRANS BEGIN statement and concluded with the SUBTRANS END or SUBTRANS ROLLBACK statements.

?     Using an SQL savepoint
A subtransaction is initiated by setting an SQL savepoint using the SAVEPOINT statement. AN SQL savepoint can be deactivated using the RELEASE SAVEPOINT statement or the ROLLBACK TO statement.

Choose one of these procedures and stick to it.

Subtransactions do not affect locks. These are only released by COMMIT or ROLLBACK. COMMIT or ROLLBACK implicitly closes all subtransactions.

Inner Transactions

You can use the INNER TRANSACTION statement to embed one transaction in another transaction. You can use the embedded transaction any way you want, the embedding transaction is continued irrespectively.

Locks

Since the database system permits concurrent transactions on the same database objects, locks on rows, tables, and the database catalog are necessary to isolate individual transactions.

?     You can influence how implicit locks are assigned by setting the isolation level using the CONNECT statement.

?     Locks can be assigned explicitly using the LOCK statement or by assigning a LOCK option.

?     Write locks for rows that have not yet been modified, and read locks on rows can be released by the UNLOCK statement before the end of the transaction.

The locks assigned to a transaction are released at the end of the transaction, making the respective database objects accessible again to other transactions.

SQL Statements for Transaction Management

CONNECT Statement

SET Statement

COMMIT Statement

ROLLBACK Statement

SUBTRANS Statement

SQL SAVEPOINT Statements

INNER TRANSACTION Statement

LOCK Statement

UNLOCK Statement

RELEASE Statement