Under some circumstances, it is desired that all updates to a database are to occur successfully, or none is to occur if any error occurs. A set of database updates that are to all occur successfully as a single unit, or are not to occur, is said to be a transaction.
A common example in banking is a funds transfer between two accounts. The two parts of the transaction are the withdrawal of funds from one account and the deposit of the funds in another account. Both parts of this transaction must occur. Otherwise, books of the bank will be out of balance. The deposit and withdrawal are one transaction.
An SPL application that uses a style of transaction control compatible with Oracle databases can be created if the following conditions are met:
The polar_comp_stmt_level_tx parameter must be set to TRUE. This prevents the action of unconditionally rolling back all database updates within the BEGIN/END block if any exception occurs.
The application must not be running in autocommit mode. If the autocommit mode is on, each successful database update is immediately committed and cannot be undone. The manner in which the autocommit mode is turned on or off is application dependent.
A transaction begins when the first SQL statement is encountered in the SPL program. All subsequent SQL statements are included as part of that transaction. The transaction ends when one of the following conditions 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 COMMIT statement is encountered. In this case, the effects of all database updates made during the transaction become permanent.
A ROLLBACK statement 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 statement is encountered, a new transaction begins.
Control returns to the calling application such as Java and PostgreSQL. In this case, the action of the application determines whether the transaction is committed or rolled back unless the transaction is within a block in which PRAGMA AUTONOMOUS_TRANSACTION has been declared in which case the commitment or rollback of the transaction occurs independently of the calling program.
Unlike Oracle, DDL statements such as CREATE TABLE do not implicitly occur within their own transaction. Therefore, DDL statements do not automatically cause an immediate database commit as in Oracle, and DDL statements may be rolled back just like DML statements.
A transaction may span one or more BEGIN/END blocks, or a single BEGIN/END block may contain one or more transactions.
The following topics discuss the COMMIT and ROLLBACK statements in more detail.