In Oracle Database, multiversioning is the ability to simultaneously materialize multiple versions of data. Oracle Database maintains multiversion read consistency.

Queries of an Oracle database have the following characteristics:

• Read-consistent queries

  The data returned by a query is committed and consistent for a single point in time.

  Note:

  Oracle Database never permits a dirty read, which occurs when a transaction reads uncommitted data in another transaction.

  To illustrate the problem with dirty reads, suppose one transaction updates a column value without committing. A second transaction reads the updated and dirty (uncommitted) value. The first session rolls back the transaction so that the column has its old value, but the second transaction proceeds using the updated value, corrupting the database. Dirty reads compromise data integrity, violate foreign keys, and ignore unique constraints.

• Nonblocking queries

  Readers and writers of data do not block one another.

In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity.

A lock is a mechanism that prevent destructive interaction between transactions accessing the same resource.

The SQL standard, which has been adopted by both ANSI and ISO/IEC, defines four levels of transaction isolation. These levels have differing degrees of impact on transaction processing throughput.

In the read committed isolation level, every query executed by a transaction sees only data committed before the query—not the transaction—began.

This isolation level is the default. It is appropriate for database environments in which few transactions are likely to conflict.

A query in a read committed transaction avoids reading data that commits while the query is in progress. For example, if a query is halfway through a scan of a million-row table, and if a different transaction commits an update to row 950,000, then the query does not see this change when it reads row 950,000. However, because the database does not prevent other transactions from modifying data read by a query, other transactions may change data between query executions. Thus, a transaction that runs the same query twice may experience fuzzy reads and phantoms.

In the serializable isolation level, a transaction sees only changes committed at the time the transaction—not the query—began and changes made by the transaction itself.

A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database. Serializable isolation is suitable for environments:

• With large databases and short transactions that update only a few rows

• Where the chance that two concurrent transactions will modify the same rows is relatively low

• Where relatively long-running transactions are primarily read only

In serializable isolation, the read consistency normally obtained at the statement level extends to the entire transaction. Any row read by the transaction is assured to be the same when reread. Any query is guaranteed to return the same results for the duration of the transaction, so changes made by other transactions are not visible to the query regardless of how long it has been running. Serializable transactions do not experience dirty reads, fuzzy reads, or phantom reads.

Oracle Database permits a serializable transaction to modify a row only if changes to the row made by other transactions were already committed when the serializable transaction began. The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began:

ORA-08177: Cannot serialize access for this transaction

When a serializable transaction fails with the ORA-08177 error, an application can take several actions, including the following:

• Commit the work executed to that point

• Execute additional (but different) statements, perhaps after rolling back to a savepoint established earlier in the transaction

• Roll back the entire transaction

The following table shows how a serializable transaction interacts with other transactions. If the serializable transaction does not try to change a row committed by another transaction after the serializable transaction began, then a serialized access problem is avoided.

SQL> SELECT last_name, salary FROM employees WHERE 
last_name  IN ('Banda', 
'Greene', 'Hintz');
 
LAST_NAME         SALARY
------------- ----------
Banda               6200
Greene              9500

SQL> UPDATE employees 
SET salary = 7000 
WHERE last_name='Banda';

SQL> SET TRANSACTION 
ISOLATION LEVEL SERIALIZABLE;

SQL> SELECT last_name, salary FROM employees 
WHERE last_name IN 
('Banda', 'Greene', 'Hintz');
 
LAST_NAME        SALARY
------------ ----------
Banda              6200
Greene             9500

SQL> UPDATE employees 
SET salary = 9900 
WHERE last_name = 'Greene';

SQL> INSERT INTO employees 
(employee_id, last_name, 
email, hire_date, job_id) 
VALUES (210, 'Hintz', 
'JHINTZ', SYSDATE,'SH_CLERK');

SQL> COMMIT;

SQL> SELECT last_name, salary 
FROM employees 
WHERE last_name IN 
('Banda', 'Greene', 'Hintz');
 
LAST_NAME         SALARY
------------- ----------
Banda               7000
Greene              9500
Hintz

SQL> SELECT last_name, salary FROM employees 
WHERE last_name IN
('Banda', 'Greene', 'Hintz');
 
LAST_NAME        SALARY
------------- ---------
Banda              6200
Greene             9900

COMMIT;

SQL> SELECT last_name, salary 
FROM employees 
WHERE last_name IN 
('Banda', 'Greene', 'Hintz');
 
LAST_NAME         SALARY
------------- ----------
Banda               7000
Greene              9900
Hintz

SQL> SELECT last_name, salary
FROM employees 
WHERE last_name IN 
('Banda', 'Greene', 'Hintz');
 
LAST_NAME        SALARY
------------- ---------
Banda              7000
Greene             9900
Hintz

SQL> UPDATE employees 
SET salary = 7100 
WHERE last_name = 'Hintz';

SQL> SET TRANSACTION 
ISOLATION LEVEL SERIALIZABLE;

SQL> UPDATE employees 
SET salary = 7200 
WHERE last_name = 'Hintz';

-- prompt does not return

SQL> COMMIT;

UPDATE employees 
SET salary = 7200 
WHERE last_name = 'Hintz'
*
ERROR at line 1: 
ORA-08177: can't 
serialize access for 
this transaction

SQL> ROLLBACK;

SQL> SET TRANSACTION 
ISOLATION LEVEL SERIALIZABLE;

SQL> SELECT last_name, 
salary FROM employees 
WHERE last_name IN 
('Banda', 'Greene', 'Hintz');
 
LAST_NAME         SALARY
------------- ----------
Banda               7000
Greene              9500
Hintz               7100

SQL> UPDATE employees 
SET salary = 7200 
WHERE last_name='Hintz';

1 row updated.

SQL> COMMIT;

The read-only isolation level is similar to the serializable isolation level, but read-only transactions do not permit data to be modified in the transaction unless the user is SYS.

Read-only transactions are not susceptible to the ORA-08177 error. Read-only transactions are useful for generating reports in which the contents must be consistent with respect to the time when the transaction began.

Oracle Database achieves read consistency by reconstructing data as needed from the undo segments. Because undo segments are used in a circular fashion, the database can overwrite undo data. Long-running reports run the risk that undo data required for read consistency may have been reused by a different transaction, raising a snapshot too old error. Setting an undo retention period, which is the minimum amount of time that the database attempts to retain old undo data before overwriting it, appropriately avoids this problem.

The database maintains several different types of locks, depending on the operation that acquired the lock.

In general, the database uses two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource such as a row or a table, but many share locks can be obtained on a single resource.

Locks affect the interaction of readers and writers. A reader is a query of a resource, whereas a writer is a statement modifying a resource. The following rules summarize the locking behavior of Oracle Database for readers and writers:

• A row is locked only when modified by a writer.

  When a statement updates one row, the transaction acquires a lock for this row only. By locking table data at the row level, the database minimizes contention for the same data. Under normal circumstances^Foot 1 the database does not escalate a row lock to the block or table level.

• A writer of a row blocks a concurrent writer of the same row.

  If one transaction is modifying a row, then a row lock prevents a different transaction from modifying the same row simultaneously.

• A reader never blocks a writer.

  Because a reader of a row does not lock it, a writer can modify this row. The only exception is a SELECT ... FOR UPDATE statement, which is a special type of SELECT statement that does lock the row that it is reading.

• A writer never blocks a reader.

  When a row is being changed by a writer, the database uses undo data to provide readers with a consistent view of the row.

  Note:

  Readers of data may have to wait for writers of the same data blocks in very special cases of pending distributed transactions.

In a single-user database, locks are not necessary because only one user is modifying information. However, when multiple users are accessing and modifying data, the database must provide a way to prevent concurrent modification of the same data.

Locks achieve the following important database requirements:

• Consistency

  The data a session is viewing or changing must not be changed by other sessions until the user is finished.

• Integrity

  The data and structures must reflect all changes made to them in the correct sequence.

Oracle Database provides data concurrency, consistency, and integrity among transactions through its locking mechanisms. Locking occurs automatically and requires no user action.

The need for locks can be illustrated by a concurrent update of a single row. In the following example, a simple web-based application presents the end user with an employee email and phone number. The application uses an UPDATE statement such as the following to modify the data:

UPDATE employees
SET    email = ?, phone_number = ?
WHERE  employee_id = ?
AND    email = ?
AND    phone_number = ?

In the preceding UPDATE statement, the email and phone number values in the WHERE clause are the original, unmodified values for the specified employee. This update ensures that the row that the application modifies was not changed after the application last read and displayed it to the user. In this way, the application avoids the lost update problem in which one user overwrites changes made by another user, effectively losing the update by the second user (Table 9-2 shows an example of a lost update).

Table 9-4 shows the sequence of events when two sessions attempt to modify the same row in the employees table at roughly the same time.

SELECT employee_id as ID, 
       email, phone_number 
FROM   hr.employees 
WHERE  last_name='Himuro';

ID  EMAIL   PHONE_NUMBER
--- ------- ------------
118 GHIMURO 515.127.4565

SELECT employee_id as ID,
       email, phone_number 
FROM   hr.employees 
WHERE  last_name='Himuro';

ID  EMAIL   PHONE_NUMBER
--- ------- ------------
118 GHIMURO 515.127.4565

UPDATE hr.employees SET 
phone_number='515.555.1234'
WHERE employee_id=118
AND email='GHIMURO'
AND phone_number = 
'515.127.4565';

1 row updated.

UPDATE hr.employees SET 
phone_number='515.555.1235'
WHERE employee_id=118
AND email='GHIMURO'
AND phone_number = 
'515.127.4565';

-- SQL*Plus does not show
-- a row updated message or
-- return the prompt.

COMMIT;

Commit complete.

0 rows updated.

UPDATE hr.employees SET 
phone_number='515.555.1235'
WHERE employee_id=118
AND email='GHIMURO' AND 
phone_number='515.555.1234';

1 row updated.

SELECT employee_id as ID, 
       email, phone_number 
FROM   hr.employees 
WHERE  last_name='Himuro';

ID  EMAIL   PHONE_NUMBER
--- ------- ------------
118 GHIMURO 515.555.1234

UPDATE hr.employees SET 
phone_number='515.555.1235 
WHERE employee_id=118
AND email='GHIMURO'
AND phone_number =
'515.555.1234';

-- SQL*Plus does not show
-- a row updated message or
-- return the prompt.

ROLLBACK;

Rollback complete.

1 row updated.

COMMIT;

Commit complete.

Oracle Database automatically obtains necessary locks when executing SQL statements. For example, before the database permits a session to modify data, the session must first lock the data. The lock gives the session exclusive control over the data so that no other transaction can modify the locked data until the lock is released.

Because the locking mechanisms of Oracle Database are tied closely to transaction control, application designers need only define transactions properly, and Oracle Database automatically manages locking. Users never need to lock any resource explicitly, although Oracle Database also enables users to lock data manually.

The following sections explain concepts that are important for understanding how Oracle Database achieves data concurrency.

Oracle Database automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity.

The less restrictive the level, the more available the data is for access by other users. Conversely, the more restrictive the level, the more limited other transactions are in the types of locks that they can acquire.

Oracle Database uses two modes of locking in a multiuser database:

• Exclusive lock mode

  This mode prevents the associated resource from being shared. A transaction obtains an exclusive lock when it modifies data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

• Share lock mode

  This mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, each holding a share lock to prevent concurrent access by a writer who needs an exclusive lock. Multiple transactions can acquire share locks on the same resource.

Assume that a transaction uses a SELECT ... FOR UPDATE statement to select a single table row. The transaction acquires an exclusive row lock and a row share table lock. The row lock allows other sessions to modify any rows other than the locked row, while the table lock prevents sessions from altering the structure of the table. Thus, the database permits as many statements as possible to execute.

Oracle Database performs lock conversion as necessary.

In lock conversion, the database automatically converts a table lock of lower restrictiveness to one of higher restrictiveness. For example, suppose a transaction issues a SELECT ... FOR UPDATE for an employee and later updates the locked row. In this case, the database automatically converts the row share table lock to a row exclusive table lock. A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed.

Lock conversion is different from lock escalation, which occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). If a session locks many rows in a table, then some databases automatically escalate the row locks to a single table. The number of locks decreases, but the restrictiveness of what is locked increases.

Oracle Database never escalates locks. Lock escalation greatly increases the probability of deadlocks. Assume that a system is trying to escalate locks on behalf of transaction 1 but cannot because of the locks held by transaction 2. A deadlock is created if transaction 2 also requires lock escalation of the same data before it can proceed.

Oracle Database automatically releases a lock when some event occurs so that the transaction no longer requires the resource.

Usually, the database holds locks acquired by statements within a transaction for the duration of the transaction. These locks prevent destructive interference such as dirty reads, lost updates, and destructive DDL from concurrent transactions.

Oracle Database releases all locks acquired by the statements within a transaction when it commits or rolls back. Oracle Database also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions continue to wait until after the original transaction commits or rolls back completely.

A deadlock is a situation in which two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work.

Oracle Database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, releasing one set of the conflicting row locks. The database returns a corresponding message to the transaction that undergoes statement-level rollback. The statement rolled back belongs to the transaction that detects the deadlock. Usually, the signaled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.

Table 9-5 illustrates two transactions in a deadlock.

SQL> UPDATE employees 
  SET salary = salary*1.1
  WHERE employee_id = 100;

1 row updated.

SQL> UPDATE employees
  SET  salary = salary*1.1
  WHERE employee_id = 200;

1 row updated.

SQL> UPDATE employees 
  SET salary = salary*1.1
  WHERE employee_id = 200;

-- prompt does not return

SQL> UPDATE employees
  SET salary = salary*1.1
  WHERE employee_id = 100;

-- prompt does not return

UPDATE employees
       *
ERROR at line 1:
ORA-00060: deadlock detected
while waiting for resource

SQL>

SQL> COMMIT;

Commit complete.

1 row updated.

SQL>

SQL> COMMIT;

Commit complete.

Deadlocks most often occur when transactions explicitly override the default locking of Oracle Database. Because Oracle Database does not escalate locks and does not use read locks for queries, but does use row-level (rather than page-level) locking, deadlocks occur infrequently.

A DML lock, also called a data lock, guarantees the integrity of data accessed concurrently by multiple users.

For example, a DML lock prevents two customers from buying the last copy of a book available from an online bookseller. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations.

DML statements automatically acquire the following types of locks:

• Row Locks (TX)

• Table Locks (TM)

In the following sections, the acronym in parentheses after each type of lock or lock mode is the abbreviation used in the Locks Monitor of Oracle Enterprise Manager (Enterprise Manager). Enterprise Manager might display TM for any table lock, rather than indicate the mode of table lock (such as RS or SRX).

A data dictionary (DDL) lock protects the definition of a schema object while an ongoing DDL operation acts on or refers to the object.

Only individual schema objects that are modified or referenced are locked during DDL operations. The database never locks the whole data dictionary.

Oracle Database acquires a DDL lock automatically on behalf of any DDL transaction requiring it. Users cannot explicitly request DDL locks. For example, if a user creates a stored procedure, then Oracle Database automatically acquires DDL locks for all schema objects referenced in the procedure definition. The DDL locks prevent these objects from being altered or dropped before procedure compilation is complete.

Oracle Database uses various types of system locks to protect internal database and memory structures. These mechanisms are inaccessible to users because users have no control over their occurrence or duration.