Transaction control v15
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:
edb_stmt_level_txparameter is set to
TRUE. This prevents the action of unconditionally rolling back all database updates in the
BEGIN/ENDblock 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.
COMMITcommand is encountered. In this case, the effect of all database updates made during the transaction become permanent.
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.
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.
commit rollback pragma_autonomous_transaction