Lemur zaprasza
[Chapter 2] Choose Your Transaction!Chapter 2 2. Choose Your Transaction!Contents:Transaction Management in PL/SQLDefining Autonomous TransactionsWhen to Use Autonomous TransactionsRules and RestrictionsExamplesOne of the more exciting new features of Oracle8i Release 8.1's version of PL/SQL is a capability called autonomous transactions. In this chapter, I'll discuss several ways you can put this feature to use in your application development environment. 2.1 Transaction Management in PL/SQLPL/SQL is tightly integrated with the Oracle RDBMS; that is, after all, why it is called "PL/SQL" -- procedural language extensions to SQL. When you perform operations in the database, you do so within the context of a transaction, a series of one or more SQL statements that perform a logical unit of work. A transaction can have associated with it a variety of locks on resources (rows of data, program units, etc.). These locks define the context of the transaction; the context also contains the actual data.To appreciate transactions in Oracle, consider the "ACID" principle: a transaction has atomicity, consistency, isolation, and durability, which are defined as follows:AtomicA transaction's changes to a state are atomic: either they all happen or none happens. ConsistentA transaction is a correct transformation of state. The actions taken as a group do not violate any integrity constraints associated with that state.IsolatedEven though many transactions may be executing concurrently, from any given transaction's point of view, other transactions appear to have executed before or after its execution.DurableOnce a transaction completes successfully, the changes to the state are made permanent, and they survive any subsequent failures.A transaction can either be saved by performing a COMMIT or erased by requesting a ROLLBACK. In either case, the affected locks on resources are released (a ROLLBACK TO might only release some of the locks). The session can then start a new transaction.Before the release of PL/SQL 8.1, each Oracle session could have at most one active transaction at a given time. In other words, any and all changes made in your session had to be either saved or erased in their entirety. This restriction has long been considered a drawback in the PL/SQL world. Developers have requested the ability to execute and save or cancel certain DML statements (INSERT, UPDATE, DELETE) without affecting the overall session's transaction. You can now accomplish this goal with the autonomous transaction feature of PL/SQL 8.1. 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 then resume the main transaction. This flow of transaction control is illustrated in Figure 2.1.Figure 2.1: Flow of transaction control between main, nested, and autonomous transactions1.3 New PL/SQL Features Roundup2.2 Defining Autonomous Transactions O'Reilly & Associates. All rights reserved. |