Alternatively, you can roll back a portion of a transaction with the ROLLBACK TO SAVEPOINT command. Savepoints are discussed later in this chapter in the section titled "Savepoints."

When you roll back an entire transaction, the following occurs:

1. All changes made by the current transaction are undone using the corresponding rollback segment.
2. All locks on the rows caused by the transaction are released.
3. The transaction is ended.

When you roll back a transaction to a savepoint, the following occurs:

• Only the SQL statements executed after the last savepoint are rolled back.
• The specified savepoint in the ROLLBACK command is preserved, but all other savepoints after that savepoint are removed from the database.
• All locks established since the specified savepoint are released.
• The transaction is still active and can continue.

No privileges are required to roll back your own transaction. Oracle requires that you have the force transaction system privilege to roll back any in-doubt distributed transaction owned by you. If the distributed transaction is owned by someone else, then you are required to have the force any transaction system privilege.

Oracle will perform an implicit rollback if a severe failure occurs with the host computer or in the application program.

Two-Phase Commit

Oracle manages the commits and rollbacks of distributed transactions and maintains data integrity for all the distributed databases participating in the distributed transaction. Oracle performs these tasks by a mechanism known as two-phase commit.

In a nondistributed environment, all transactions are either committed or rolled back as a unit. However, in a distributed environment, commits and rollbacks of a distributed transaction must be coordinated over a network so that all the participating databases either commit or roll back the transaction. This must hold true even if the network fails during the distributed transaction. The two-phase commit guarantees that the nodes participating in the transaction either commit or roll back the transaction, thus maintaining complete data integrity of the global database.

All implicit DML operations performed by integrity constraints, remote procedure calls, and triggers are protected by Oracle's two-phase commit.

Savepoints

A savepoint is like a bookmark in the transaction. You explicitly place this bookmark for reference at a later time. Savepoints are used to break a large transaction up into smaller pieces. This allows you to roll back your work to intermediate points in the transaction rather than rolling back the entire transaction. For example, if you are performing a large number of updates and an error occurs, you only have to roll back to the last savepoint; therefore, you would not need to reprocess every statement.

The following code creates the savepoint named master_credit:

SAVEPOINT master_credit

Savepoint names must be unique within a given transaction. If you create a second savepoint named the same as an earlier savepoint, the previous savepoint is erased.

The following is an example of rolling back a transaction to the employee_1 savepoint:

INSERT INTO employee VALUES
(6,'Tom Bluekers',3,1000.00,'S');
SAVEPOINT employee_1;
INSERT INTO employee VALUES
(7,'Catherine Ann',2,2000.00,'S');
ROLLBACK TO SAVEPOINT employee_1;

In this example, the insertion of the employee Catherine Ann is removed from the transaction. At the point of the rollback to the savepoint, the insertion of Tom Bluekers is the pending data in the current transaction.

Locking

Oracle automatically locks a row on behalf of a transaction to prevent other transactions from acquiring a lock on the same row. You don't want simultaneous row manipulations by two separate transactions. Data locks prevent destructive interference of simultaneous conflicting DDL and DML statements. For example, Oracle prevents a table from being dropped if there are uncommitted transactions on that table. These data locks are automatically released when the transaction completes by a commit or rollback. The next two sections examine two types of data locks: table and row.

Table Locking

DML operations can obtain data locks for specific rows and for specific tables. These locks occur to protect the data in the table when the table is being accessed concurrently by multiple users.

A transaction acquires a table lock when a table is modified by the following DML statements: INSERT, UPDATE, DELETE, SELECT with the UPDATE option, and LOCK TABLE. The table lock is specifically in place to ensure that the current transaction has access to the data and to prevent any conflicting DDL operations that might happen.

An important note to consider is that placing a table lock prevents other transactions from acquiring a lock (row or table) on the same table.

The table lock can be executed in five different modes:

• row share—This table lock is the least restrictive of the table locks. This lock allows for other concurrent transactions to query, insert, update, delete, and lock rows in the same table. The row share table lock will not allow exclusive write access to the same table.
• row exclusive—This lock occurs when several rows in a table have been updated. This lock still allows other transactions to query, insert, update, delete, or lock rows in the same table. The row exclusive lock will not prevent any manual locking or exclusive reads and writes on the same table.
• share lock—The share lock table lock allows for other transactions only to query and lock specific rows. This lock prevents all updates, inserts, and deletes from the same table.
• share row exclusive—This table lock is only accomplished through the lock table with the share row exclusive parameter. This lock will only permit queries and SELECT FOR UPDATE statements.
• exclusive—This lock allows the transaction write access to a table. This lock means that other transactions can only query the table.

Implicit data locking occurs automatically for all SQL statements, so users of the database do not have to explicitly lock any rows. By default, Oracle locks resources at the lowest level possible.

In a multiuser database, locks have two different levels:

• exclusive—This prohibits the sharing of the associated resource. The first transaction that acquires the resource is the only transaction that can alter the resource until the lock is released.
• share—This lock allows the associated resource to be shared, depending on the operations involved. Several transactions can acquire share locks on the same resource. share locks provide a higher degree of data concurrency than exclusive locks.