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.

Structure

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

Activities

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 SUBTRANS statement
A subtransaction is started using the SUBTRANS BEGIN statement and ended using the SUBTRANS END or SUBTRANS ROLLBACK statement.
Using SAVEPOINT statement
A subtransaction is started by setting an SQL savepoint using the SAVEPOINT statement. An SQL savepoint can be deactivated using the RELEASE SAVEPOINT or ROLLBACK TO statement.

Choose one of these procedures and stick to it.

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

Inner Transactions

You can use the INNER TRANSACTION statement to embed one transaction in another transaction. The embedded transaction can be completed at any time; the surrounding 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.
Exclusive locks for rows that have not yet been modified and shared 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.

More Information

Concepts of the Database System, Transactions, Locks

Database Administration, Configuring Locks for Database Objects, Analyzing the Database Performance

SQL Statements: Overview

SQL Reference Manual