There may be circumstances where it is desired that all updates to a database are to occur successfully, or none are to occur at all if any error occurs. A set of database updates that are to all occur successfully as a single unit, or are not to occur at all, 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 the bank’s books will be out of balance. The deposit and withdrawal are one transaction.An SPL application can be created that uses a style of transaction control compatible with Oracle databases if the following conditions are met:
• 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:
• A COMMIT command is encountered in which case the effect of all database updates made during the transaction become permanent.
• A ROLLBACK command is encountered in which 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, PSQL, etc.) in which case the action of the application determines whether the transaction is committed or rolled back.Note: Unlike Oracle, DDL commands such as CREATE TABLE do not implicitly occur within their own transaction. Therefore, DDL commands do not automatically cause an immediate database commit as in Oracle, and DDL commands may be rolled back just like DML commands.A transaction may span one or more BEGIN/END blocks, or a single BEGIN/END block may contain one or more transactions.The following sections discuss the COMMIT and ROLLBACK commands in more detail.3.6.1 COMMITThe COMMIT command makes all database updates made during the current transaction permanent, and ends the current transaction.The COMMIT command may be used within anonymous blocks, stored procedures, or functions. Within an SPL program, it may appear in the executable section and/or the exception section.In the following example, the third INSERT command in the anonymous block results in an error. The effect of the first two INSERT commands are retained as shown by the first SELECT command. Even after issuing a ROLLBACK command, the two rows remain in the table as shown by the second SELECT command verifying that they were indeed committed.Note: The edb_stmt_level_tx configuration parameter shown in the example below can be set for the entire database using the ALTER DATABASE command, or it can be set for the entire database server by changing it in the postgresql.conf file.3.6.2 ROLLBACKThe ROLLBACK command undoes all database updates made during the current transaction, and ends the current transaction.The ROLLBACK command may be used within anonymous blocks, stored procedures, or functions. Within an SPL program, it may appear in the executable section and/or the exception section.In the following example, the exception section contains a ROLLBACK command. Even though the first two INSERT commands are executed successfully, the third results in an exception that results in the rollback of all the INSERT commands in the anonymous block.The following is a more complex example using both COMMIT and ROLLBACK. First, the following stored procedure is created which inserts a new employee.Note that this procedure has no exception section so any error that may occur is propagated up to the calling program.The following anonymous block is run. Note the use of the COMMIT command after all calls to the emp_insert procedure and the ROLLBACK command in the exception section.The following SELECT command shows that employees Farrell and Tyler were successfully added.A SELECT command run against the table yields the following:The ROLLBACK command in the exception section successfully undoes the insert of employee Harrison. Also note that employees Farrell and Tyler are still in the table as their inserts were made permanent by the COMMIT command in the first anonymous block.