About transactions v16
A transaction begins when the first SQL command is encountered in the SPL program. All subsequent SQL commands are included as part of that transaction.
The transaction ends when one of the following occurs:
- An unhandled exception occurs. In this case, the effects of all database updates made during the transaction are rolled back, and the transaction is aborted.
- A
COMMITcommand is encountered. In this case, the effect of all database updates made during the transaction become permanent. - A
ROLLBACKcommand is encountered. In this case, the effects of all database updates made during the transaction are rolled back, and the transaction is aborted. If a new SQL command is encountered, a new transaction begins. - Control returns to the calling application, such as Java or PSQL. In this case, the action of the application determines whether the transaction is committed or rolled back. The exception is when the transaction is in a block in which
PRAGMA AUTONOMOUS_TRANSACTIONwas declared. In this case, the commitment or rollback of the transaction occurs independently of the calling program.
Note
Unlike Oracle, DDL commands such as CREATE TABLE don't implicitly occur in their own transaction. Therefore, DDL commands don't cause an immediate database commit as in Oracle, and you can roll back DDL commands just like DML commands.
A transaction can span one or more BEGIN/END blocks, or a single BEGIN/END block can contain one or more transactions.