11.9.1 Transaction Limits

A transaction in Oracle consists of one or more DML statements that form a unit of work, or a single data definition language (DDL) statement. Every transaction must have a beginning and an end point. A transaction begins with the first executable SQL statement, or a SET TRANSACTION command, a statement a programmer can use to delimit a transaction and optionally give it an isolation level and a name, as well as other characteristics. Its general form is

For example, if we wanted to read faculty departments and ranks, but not do any updates, we could write

If we plan to update any records, we could write a statement such as

When a transaction begins, the DBMS creates an undo data segment, also known as a rollback segment, to hold log entries containing the older versions of data items that can be used for both read consistency and for any undo operations that might be needed. A slot on the transaction table, a table that holds the IDs of transactions using the segment, is also assigned, and the transaction is given a unique ID that is entered into the transaction table. As the transaction executes, log entries having that ID and other information are made to the undo segment using the immediate update protocol described in Section 11.8.3, and updates are made to the database buffers. The transaction ends when an explicit COMMIT or ROLLBACK is reached, when a DDL command is reached, or an implicit END is reached, such as when the user exits or an error occurs that causes the process to terminate abnormally. A new transaction begins when another executable SQL command or another SET TRANSACTION appears.

Oracle uses both timestamps and system change numbers (SCNs) (as described in Chapter 7, Section 7.3.2). SCNs are internal logical timestamps generated when a transaction modifies data and when a transaction commits. They are recorded in the undo data segment and can be used for recovery.

Oracle also allows the programmer to specify savepoints, which are points in the transaction that can optionally be assigned names, so that the application can later roll back to that point if necessary. For example, the command

could be placed in the middle of a long transaction so that if there is a reason to roll back, such as an exception, the programmer can write

which rolls back to the last savepoint.

We can also assign an optional name to any savepoint, as in

Having a name allows us to specify which of possibly many savepoints a rollback refers to, as in

which rolls back to the named savepoint.

11.9.2 Isolation Levels

Although the SET TRANSACTION statement can be used to specify the type of concurrency control for the transaction, Oracle automatically assigns an isolation level if none is specified. Oracle supports three isolation levels, which provide different degrees of protection from other transactions. They are as follows:

• Read committed, which is the default level of isolation for transactions that only read data. It provides statement-level consistency, guaranteeing that each SQL statement in a transaction reads only data committed before the statement started, even if the single statement involves reading many rows of data. This level protects the statement from dirty reads. However, because data can be changed by other transactions that committed during the execution of the transaction containing the statement, there could be nonrepeatable reads and phantom data when later reads are performed within the same transaction. If you do not specify an isolation level in the SET TRANSACTION statement and the transaction involves only SELECT operations, this level is assigned.

• Serializable, which is transaction-level consistency, corresponds to the SQL standard for serializability. This is the level Oracle uses by default for any transaction involving updates. It ensures that a transaction sees only data committed before the transaction started. It protects the transaction from dirty reads, nonrepeatable reads, and phantom data. This is the level assigned if you do not specify an isolation level but the transaction involves INSERT, UPDATE, or DELETE operations, or if you specify READ WRITE in the SET TRANSACTION statement.

• Read-only, which is also transaction-level consistency, but which is used for long transactions involving many reads, but not writes. The transaction sees a consistent snapshot of the database. The undo data segments are used to create parts of the snapshot as needed. Other users can query or update the database during the transaction, but the transaction never sees their effects. No locking is done. This level corresponds to READ ONLY in the SET TRANSACTION statement.

11.9.3 Types of Locks

Oracle uses DML locks to protect the stored data, DDL locks to protect the schema, and system locks to protect the internal structures, such as the files holding the database. Both DDL and system locks are generated automatically by the system as needed and are not under the direct control of users.

For DML operations, Oracle uses a combination of locking and a multiversion concurrency control mechanism, with no read locks. Read consistency is maintained using the undo data segment records to recreate old values, so no read locks are needed. Oracle automatically chooses the lowest level of locking necessary for each transaction, storing locks in the data block containing the data, not in a separate table. Locks may be applied at the row level or table level, and they may be shared or exclusive. Lock conversion is used to maximize currency, and locks are released when a COMMIT or ROLLBACK is reached. Deadlock is detected automatically and is handled by rolling back one of the statements involved in the deadlock, not the entire transaction. Users can override Oracle’s automatic locking protocol.

Types of locks, whether acquired automatically by Oracle or specified by the user, include shared or exclusive row locks and table locks.

• A row lock (TX) locks a single row of a table and is needed when a transaction modifies a row using INSERT, UPDATE, DELETE, MERGE, or SELECT . . . FOR UPDATE. It is held until the transaction commits or rolls back. Whenever a row lock is obtained, the transaction is also given a shared table lock for the table the row is in, a technique described in Section 11.4.3 as intention locking. DDL commands that would interfere with the current transaction cannot be performed.

• A table lock (TM) locks a table and is needed when a transaction modifies a table using INSERT, UPDATE, DELETE, MERGE, and SELECT . . . FOR UPDATE. It is also granted when a LOCK TABLE statement is executed. It prevents DDL operations that would interfere with the transaction from being performed.

Table locks can have several modes, as follows:

• ROW SHARE (RS). This lock, also called SS, is a shared table lock that indicates that a transaction has locked rows of the table. It allows concurrent reading of the table but prevents other users from gaining an exclusive lock on the table.

• ROW EXCLUSIVE (RX). Also called SX, this is a shared table lock that indicates that the transaction updates rows of the table. It allows other transactions to access other rows of the same table concurrently.

• SHARE TABLE (S). This lock allows concurrent queries but not updates of the locked table. Different transactions can hold this type of lock concurrently.

• SHARE ROW EXCLUSIVE (SRX).This is a form of lock that allows other users to read rows of the table concurrently but prevents them from locking the table in shared mode and from updating rows of the table, so only one transaction can hold this type of lock on a table.

• EXCLUSIVE (X).The strictest form of table lock; no other transaction can be granted any type of lock or perform any DML statement on the table.

Users can override the default DML locking mechanism by specifying an isolation level in the SET TRANSACTION statement, by issuing a SELECT...FOR UPDATE statement, or by the explicit LOCK TABLE command, having the form

For example, the following command requests an exclusive lock on the Student table

NOWAIT specifies that the transaction will not wait for locks held by another transaction to be released but will return control to the transaction if the lock is not available immediately. WAIT specifies that the transaction should wait either until it can obtain the lock or until the specified number of seconds have elapsed before returning control.

11.9.4 Recovery Management

For recovery, Oracle has a recovery manager (RMAN), a GUI tool that the DBA can use to control backup and recovery operations. RMAN can be used to make backups of the database or parts thereof, to make backups of recovery logs, to restore data from backups, and to perform recovery operations (including redo and undo operations) as needed. It maintains control files, rollback segments, redo logs, and archived redo logs. When a redo log is filled, it can be archived automatically. Oracle also provides a backup feature for environments where high availability is important in the form of a managed standby database. This is a copy of the operational database, usually kept at a separate location, that can take over if the regular database fails. It is kept nearly up to date by shipping the archived redo logs and applying the updates to the standby database. As an alternative to RMAN, the DBA can choose to manage backup and recovery manually using a combination of SQL*Plus recovery commands and operating system commands. Oracle Flashback technology can also be used to restore the database or portions thereof to an earlier state (as described in Section 7.4).

The following command is used by an administrator who has the SYSDBA privilege to create a flashback archive to store historical data for a specified period.

For example, to set up a default archive called University_archive in the USERS tablespace with 1 megabyte of storage to be retained for 1 year, the DBA could write

This archive can be used to store historical data for specific objects, such as tables, although there are some restrictions on the types of objects that can be archived. The DBA can specify that data for a table should be archived by adding a FLASHBACK ARCHIVE clause to the initial table definition

The archive can also be added after table creation using an ALTER TABLE command, as in

The archive can be used to retrieve past values for a specific time using SELECT statements with an AS OF clause

All values over a period of time can be retrieved using either the system change numbers or timestamps with the clause