Section 1.12. Managing Transactions in PLSQL


1.12. Managing Transactions in PL/SQL

The 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:


COMMIT

Saves all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.


ROLLBACK

Erases all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.


ROLLBACK TO SAVEPOINT

Erases all changes made since the specified savepoint was established, and releases locks that were established within that range of the code.


SAVEPOINT

Establishes a savepoint, which then allows you to perform partial ROLLBACKs.


SET TRANSACTION

Allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment.


LOCK TABLE

Allows you to lock an entire database table in the specified mode. This overrides the default row-level locking usually applied to a table.

The following sections explore the COMMIT and ROLLBACK statements, as well as the autonomous transaction feature of PL/SQL.

1.12.1. The COMMIT Statement

When 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 Statement

When 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 Transactions

When 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 transactions


There 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:

  • Top-level (but not nested) anonymous PL/SQL blocks

  • Functions and procedures, defined either in a package or as standalone programs

  • Methods (functions and procedures) of an object type

  • Database triggers

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.




Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net