1.12. Managing Transactions in PL/SQLThe Oracle RDBMS provides a very robust transaction model, as you might expect from a relational database. Your application code determines what constitutes a transaction, which is the logical unit of work that must be either saved with a COMMIT statement or rolled back with a ROLLBACK statement. A transaction begins implicitly with the first SQL statement issued since the last COMMIT or ROLLBACK (or with the start of a session), or continues after a ROLLBACK TO SAVEPOINT. PL/SQL provides the following statements for transaction management:
The following sections explore the COMMIT and ROLLBACK statements, as well as the autonomous transaction feature of PL/SQL. 1.12.1. The COMMIT StatementWhen you COMMIT, you make permanent any changes made by your session to the database in the current transaction. Once you COMMIT, your changes will be visible to other Oracle sessions or users. The syntax for the COMMIT statement is: COMMIT [WORK] [COMMENT text]; The WORK keyword is optional and can be used to improve readability. The COMMENT keyword specifies a comment that is then associated with the current transaction. The text must be a quoted literal and can be no more than 50 characters in length. The COMMENT text is usually employed with distributed transactions and can be handy for examining and resolving in-doubt transactions within a two-phase commit framework. It is stored in the data dictionary along with the transaction ID. Note that COMMIT releases any row and table locks issued in your session, such as with a SELECT FOR UPDATE statement . It also erases any savepoints issued since the last COMMIT or ROLLBACK. Once you COMMIT your changes, you cannot roll them back with a ROLLBACK statement. The following statements are all valid uses of COMMIT: COMMIT; COMMIT WORK; COMMIT COMMENT 'maintaining account balance'. 1.12.2. The ROLLBACK StatementWhen you perform a ROLLBACK, you undo some or all changes made by your session to the database in the current transaction. Why would you want to erase changes? From an ad hoc SQL standpoint, the ROLLBACK gives you a way to erase mistakes you might have made, as in: DELETE FROM orders; "No, no! I meant to delete only the orders before May 1995!" No problemjust issue ROLLBACK. From an application coding standpoint, ROLLBACK is important because it allows you to clean up or restart from a clean state when a problem occurs. The syntax for the ROLLBACK statement is: ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name]; There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a savepoint at which the ROLLBACK should stop. The parameterless ROLLBACK undoes all outstanding changes in your transaction. The ROLLBACK TO version allows you to undo all changes and release all acquired locks that were issued since the savepoint identified by savepoint_name was marked. (See the next section on the SAVEPOINT statement for more information on how to mark a savepoint in your application.) The savepoint_name is an undeclared Oracle identifier. It cannot be a literal (enclosed in quotes) or a variable name. All of the following uses of ROLLBACK are valid: ROLLBACK; ROLLBACK WORK; ROLLBACK TO begin_cleanup; When you roll back to a specific savepoint, all savepoints issued after the specified savepoint_name are erased, but the savepoint to which you roll back is not. This means that you can restart your transaction from that point and, if necessary, roll back to that same savepoint if another error occurs. Immediately before you execute an INSERT, UPDATE, or DELETE, PL/SQL implicitly generates a savepoint. If your DML statement then fails, a rollback is automatically performed to that implicit savepoint. In this way, only the last DML statement is undone. 1.12.3. Autonomous TransactionsWhen you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller's transaction context. That block becomes an independent transaction that is started by another transaction, referred to as the main transaction . Within the autonomous transaction block, the main transaction is suspended. You perform your SQL operations, commit or roll back those operations, and resume the main transaction. This flow of transaction control is illustrated in Figure 1-5. Figure 1-5. Flow of transaction control among main, nested, and autonomous transactionsThere isn't much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section: PRAGMA AUTONOMOUS_TRANSACTION; The pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent. For the purposes of the autonomous transaction, a PL/SQL block can be any of the following:
You can put the autonomous transaction pragma anywhere in the declaration section of your PL/SQL block. You would probably be best off, however, placing it before any data structure declarations. That way, anyone reading your code will immediately identify the program as an autonomous transaction. This pragma is the only syntax change made to PL/SQL to support autonomous transactions. COMMIT, ROLLBACK, the DML statementsall the rest is as it was before. However, these statements have a different scope of impact and visibility when executed within an autonomous transaction, and you will need to include a COMMIT or ROLLBACK in your program. |