Transaction control v14

There might be times when you want all updates to a database to occur successfully or for none to occur in case of any error. A set of database updates that occur successfully as a single unit or not at all is called 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 for the bank’s books to balance. The deposit and withdrawal are one transaction.

You can create an SPL application that uses a style of transaction control compatible with Oracle databases if the following conditions are met:

• The edb_stmt_level_tx parameter is set to TRUE. This prevents the action of unconditionally rolling back all database updates in the BEGIN/END block if any exception occurs.
• The application isn't running in autocommit mode. If autocommit mode is on, each successful database update is immediately committed and can't be undone. The manner in which autocommit mode is turned on or off depends on the application.

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 COMMIT command is encountered. In this case, the effect of all database updates made during the transaction become permanent.
• A ROLLBACK command 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_TRANSACTION was declared. In this case, the commitment or rollback of the transaction occurs independently of the calling program.

A transaction can span one or more BEGIN/END blocks, or a single BEGIN/END block can contain one or more transactions.