The INNER TRANSACTION statement (inner_transaction_statement) enables you to embed a transaction in another transaction. The embedded transaction can be completed at any time; the surrounding transaction is continued irrespectively.
Syntax
<inner_transaction_statement> ::=
INNER TRANSACTION BEGIN
| INNER TRANSACTION COMMIT
| INNER TRANSACTION ROLLBACK
A transaction is started without ending the already existing transaction. Any sequence of SQL statements can then follow. The sequence of SQL statements my not contain any additional INNER TRANSACTION statements. At any one time, there may be no more than one open inner transaction.
After ending the inner transaction, the existing transaction is continued unchanged.
If the inner transaction is ended with COMMIT, the executed changes remain even if the surrounding transaction is canceled. The prerequisite for this is that an open inner transaction exists.
If an inner transaction is ended with ROLLBACK, then all database changes made since INNER TRANSACTION BEGIN are reversed. The prerequisite for this is that an open inner transaction exists.
Lock requests of the inner transaction can collide with held columns of the surrounding transaction.
If the surrounding transaction is ended and there is still an open inner transaction, then this inner transaction is ended with COMMIT or ROLLBACK, just like the surrounding transaction.
The INNER TRANSACTION statement is especially suitable for applications that want to write information permanently to the database, irrespective of the conclusion of a transaction, for example, for logs.