A SQL statement that executes successfully is different from a committed transaction. Executing successfully means that a single statement was: parsed; found to be a valid SQL construction; and executed without error as an atomic unit. If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement had never been executed. This operation is a statement-level rollback. Errors discovered during SQL statement execution cause statement-level rollbacks. An example of such an error is attempting to insert a duplicate value in a primary key. Single SQL statements involved in a deadlock (competition for the same data) can also cause a statement-level rollback. Errors discovered during SQL statement parsing, such as a syntax error, have not yet been executed, so they do not cause a statement-level rollback.
A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database). A transaction begins with the first executable SQL statement. When a transaction begins, Oracle assigns the transaction to an available rollback segment to record the rollback entries for the new transaction.
A transaction ends when any of the following occurs:
 | You issue a COMMIT or ROLLBACK statement
without a SAVEPOINT clause.
You execute a DDL statement such as | CREATE, DROP, RENAME, or
ALTER. If the current transaction contains any DML statements, Oracle
first commits the transaction, and then executes and commits the DDL
statement as a new, single statement transaction.A user disconnects from Oracle. The current
transaction is committed.
| A user process terminates abnormally. The current
transaction is rolled back. | |
Oracle9i, Release 1 (9.0.1), lets you name a
transaction, using a simple and memorable text string. You name a transaction by
using the SET TRANSACTION ... NAME command before you start the
transaction. Use the SET TRANSACTION statement to
establish the current transaction as read only or read write, establish its
isolation level, or assign it to a specified rollback segment. The operations
performed by a SET TRANSACTION statement affect only
your current transaction, not other users or other transactions.
set_transaction::=
The following statements counts the products and quantities on
hand in the Toronto warehouse in the sample Order Entry (oe)
schema. This report would not be affected by any other user who might be adding
or removing inventory to a different warehouse.
COMMIT; SET TRANSACTION READ ONLY NAME 'Toronto'; SELECT product_id, quantity_on_hand FROM oe.inventories WHERE warehouse_id = 5; COMMIT;
The following statement assigns your current transaction to the
rollback segment rs_1:
SET TRANSACTION USE ROLLBACK SEGMENT rs_1;
Use the SAVEPOINT statement to identify a point in a
transaction to which you can later roll back. Savepoint names must be distinct
within a given transaction. If you create a second savepoint with the same
identifier as an earlier savepoint, the earlier savepoint is erased. After a
savepoint has been created, you can either continue processing, commit your
work, roll back the entire transaction, or roll back to the savepoint.
When a transaction is rolled back to a savepoint, the following occurs:
The transaction remains active and can be continued. Which one(s) of the following updates will become permanent?
UPDATE hr.employees
SET salary = 7000
WHERE last_name = 'Banda';
SAVEPOINT banda_sal;
UPDATE hr.employees
SET salary = 12000
WHERE last_name = 'Greene';
SAVEPOINT greene_sal;
ROLLBACK TO SAVEPOINT banda_sal;
SELECT SUM(salary) FROM hr.employees;
'ROLLBACK TO SAVEPOINT greene_sal;
COMMIT;
Autonomous transactions are independent transactions that can be called from within another transaction. An autonomous transaction lets you leave the context of the calling transaction, perform some SQL operations, commit or roll back those operations, and then return to the calling transaction's context and continue with that transaction. Once invoked, an autonomous transaction is totally independent of the main transaction that called it. It does not see any of the uncommitted changes made by the main transaction and does not share any locks or resources with the main transaction. Changes made by an autonomous transaction become visible to other transactions upon commit of the autonomous transactions. However, when an autonomous block invokes a non-autonomous block (that is, one that is not declared to be autonomous), the called block inherits the transaction context of the calling autonomous block.
The two examples in this section illustrate some of the ways you can use autonomous transactions.
As these examples illustrate, there are four possible outcomes that can occur when you use autonomous and main transactions. The following table presents these possible outcomes. As you can see, there is no dependency between the outcome of an autonomous transaction and that of a main transaction.
| Autonomous Transaction | Main Transaction |
|---|---|
|
Commits |
Commits |
|
Commits |
Rolls back |
|
Rolls back |
Commits |
|
Rolls back |
Rolls back |
In this example, a customer enters a buy order. That customer's information (such as name, address, phone) is committed to a customer information table--even though the sale does not go through.
In the following banking application, a customer tries to make a withdrawal from his or her account. In the process, a main transaction calls one of two autonomous transaction scopes (AT Scope 1, and AT Scope 2).
The following diagrams illustrate three possible scenarios for this transaction.
| Scenario 1: There are sufficient funds to cover
the withdrawal and therefore the bank releases the funds
| Scenario 2: There are insufficient funds to cover the withdrawal, but the
customer has overdraft protection. The bank therefore releases the funds. | Scenario 3: There are insufficient funds to cover
the withdrawal, the customer does not have overdraft protection, and the
bank therefore withholds the requested funds. | |
To define autonomous transactions, you use the pragma (compiler
directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL
compiler to mark a routine as autonomous (independent).
In this context, the term routine includes
| Top-level (not nested) anonymous PL/SQL blocks
| Local, standalone, and packaged functions and procedures | Methods of a SQL object type
| Database triggers | |
You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section. The syntax follows:
PRAGMA AUTONOMOUS_TRANSACTION;
In the following example, you mark a packaged function as autonomous:
CREATE PACKAGE banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;
CREATE PACKAGE BODY banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
...
END;
END banking;
Restriction: You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. For example, the following pragma is illegal:
CREATE PACKAGE banking AS PRAGMA AUTONOMOUS_TRANSACTION; -- illegal ... FUNCTION balance (acct_id INTEGER) RETURN REAL; END banking;
In the next example, you mark a standalone procedure as autonomous:
CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN ... END; In the following example, you mark a PL/SQL block as autonomous:
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; my_empno NUMBER(4); BEGIN ... END;
Restriction: You cannot mark a nested PL/SQL block as autonomous.
In the example below, you mark a database trigger as autonomous.
Unlike regular triggers, autonomous triggers can contain transaction control
statements such as COMMIT and ROLLBACK.
CREATE TRIGGER parts_trigger BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; -- allowed only in autonomous triggers END;
Back to CS643 schedule