9 Data Concurrency and Consistency

Multiversion Read Consistency

In Oracle Database, multiversioning is the ability to simultaneously materialize multiple versions of data. Oracle Database maintains multiversion read consistency, which means that database queries have the following characteristics:

• Read-consistent queries

  The data returned by a query is committed and consistent with respect to a single point in time.

  Important:

  Oracle Database never permits dirty reads, which occur 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 (see "Summary of Locking Behavior").

Read Consistency and Undo Segments

To manage the multiversion read consistency model, the database must create a read-consistent set of data when a table is simultaneously queried and updated. Oracle Database achieves this goal through undo data.

Whenever a user modifies data, Oracle Database creates undo entries, which it writes to undo segments ("Undo Segments"). The undo segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Thus, multiple versions of the same data, all at different points in time, can exist in the database. The database can use snapshots of data at different points in time to provide read-consistent views of the data and enable nonblocking queries.

Read consistency is guaranteed in single-instance and Oracle Real Application Clusters (Oracle RAC) environments. Oracle RAC uses a cache-to-cache block transfer mechanism known as Cache Fusion to transfer read-consistent images of data blocks from one database instance to another.

See Also:

• "Internal LOBs" to learn about read consistency mechanisms for LOBs

• Oracle Database 2 Day + Real Application Clusters Guide to learn about Cache Fusion

Read Consistency: Example

Figure 9-1 shows a query that uses undo data to provide statement-level read consistency in the read committed isolation level.

Figure 9-1 Read Consistency in the Read Committed Isolation Level

Description of "Figure 9-1 Read Consistency in the Read Committed Isolation Level"

As the database retrieves data blocks on behalf of a query, the database ensures that the data in each block reflects the contents of the block when the query began. The database rolls back changes to the block as needed to reconstruct the block to the point in time the query started processing.

The database uses a mechanism called an SCN to guarantee the order of transactions. As the SELECT statement enters the execution phase, the database determines the SCN recorded at the time the query began executing. In Figure 9-1, this SCN is 10023. The query only sees committed data with respect to SCN 10023.

In Figure 9-1, blocks with SCNs after 10023 indicate changed data, as shown by the two blocks with SCN 10024. The SELECT statement requires a version of the block that is consistent with committed changes. The database copies current data blocks to a new buffer and applies undo data to reconstruct previous versions of the blocks. These reconstructed data blocks are called consistent read (CR) clones.

In Figure 9-1, the database creates two CR clones: one block consistent to SCN 10006 and the other block consistent to SCN 10021. The database returns the reconstructed data for the query. In this way, Oracle Database prevents dirty reads.

See Also:

"Database Buffer Cache" and "System Change Numbers (SCNs)"

Read Consistency and Transaction Tables

The database uses a transaction table, also called an interested transaction list (ITL), to determine if a transaction was uncommitted when the database began modifying the block. The block header of every segment block contains a transaction table.

Entries in the transaction table describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes. The transaction table points to the undo segment, which provides information about the timing of changes made to the database.

In a sense, the block header contains a recent history of transactions that affected each row in the block. The INITRANS parameter of the CREATE TABLE and ALTER TABLE statements controls the amount of transaction history that is kept.

See Also:

Oracle Database SQL Language Reference to learn about the INITRANS parameter

Locking Mechanisms

In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.

ANSI/ISO Transaction Isolation Levels

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.

These isolation levels are defined in terms of phenomena that must be prevented between concurrently executing transactions. The preventable phenomena are:

• Dirty reads

  A transaction reads data that has been written by another transaction that has not been committed yet.

• Nonrepeatable (fuzzy) reads

  A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data. For example, a user queries a row and then later queries the same row, only to discover that the data has changed.

• Phantom reads

  A transaction reruns a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

  For example, a transaction queries the number of employees. Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire. More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.

The SQL standard defines four levels of isolation in terms of the phenomena that a transaction running at a particular isolation level is permitted to experience. Table 9-1 shows the levels.

Oracle Database offers the read committed (default) and serializable isolation levels. Also, the database offers a read-only mode.

Read Committed Isolation Level

In the read committed isolation level, which is the default, every query executed by a transaction sees only data committed before the query—not the transaction—began. This level of isolation 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.

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 READ COMMITTED;

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> SELECT last_name, salary
FROM employees WHERE last_name IN 
('Banda','Greene','Hintz');
 
LAST_NAME         SALARY
------------- ----------
Banda               6200
Greene              9900

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

-- prompt does not return 

SQL> COMMIT;

1 row updated.
 
SQL>

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

COMMIT;

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

Serializable Isolation Level

In the serialization 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

Table 9-3 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               7100
Greene              9500
Hintz               7100

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

1 row updated.

SQL> COMMIT;

Read-Only Isolation Level

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. Thus, 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.

Summary of Locking Behavior

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 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.

Use of Locks

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 is performed 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 database 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, email, 
       phone_number 
FROM   hr.employees 
WHERE  last_name = 'Himuro';

EMPLOYEE_ID EMAIL   PHONE_NUMBER
----------- ------- ------------
        118 GHIMURO 515.127.4565

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

EMPLOYEE_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, email, 
       phone_number 
FROM   hr.employees 
WHERE  last_name = 'Himuro';

EMPLOYEE_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.

Lock Modes

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, holding share locks to prevent concurrent access by a writer who needs an exclusive lock. Several 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.

Lock Conversion and Escalation

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 user 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 likelihood 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.

Lock Duration

Oracle Database automatically releases a lock when some event occurs so that the transaction no longer requires the resource. In most cases, 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 (see Table 10-2 for an example).

Locks and Deadlocks

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 signalled 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
   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.

DML Locks

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).

Row Locks (TX)

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement. The row lock exists until the transaction commits or rolls back.

Row locks primarily serve as a queuing mechanism to prevent two transactions from modifying the same row. The database always locks a modified row in exclusive mode so that other transactions cannot modify the row until the transaction holding the lock commits or rolls back. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.

Note:

If a transaction terminates because of database instance failure, then block-level recovery makes a row available before the entire transaction is recovered.

If a transaction obtains a lock for a row, then the transaction also acquires a lock for the table containing the row. The table lock prevents conflicting DDL operations that would override data changes in a current transaction. Figure 9-2 illustrates an update of the third row in a table. Oracle Database automatically places an exclusive lock on the updated row and a subexclusive lock on the table.

Figure 9-2 Row and Table Locks

Description of "Figure 9-2 Row and Table Locks"

Row Locks and Concurrency

Table 9-6 illustrates how Oracle Database uses row locks for concurrency. Three sessions query the same rows simultaneously. Session 1 and 2 proceed to make uncommitted updates to different rows, while session 3 makes no updates. Each session sees its own uncommitted updates but not the uncommitted updates of any other session.

Table 9-6 Data Concurrency Example

Time	Session 1	Session 2	Session 3	Explanation
t0	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	Three different sessions simultaneously query the ID and salary of employees 100 and 101. The results returned by each query are identical.
t1	UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100;			Session 1 updates the salary of employee 100, but does not commit. In the update, the writer acquires a row-level lock for the updated row only, thereby preventing other writers from modifying this row.
t2	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 612 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	Each session simultaneously issues the original query. Session 1 shows the salary of 612 resulting from the t1 update. The readers in session 2 and 3 return rows immediately and do not wait for session 1 to end its transaction. The database uses multiversion read consistency to show the salary as it existed before the update in session 1.
t3		UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101;		Session 2 updates the salary of employee 101, but does not commit the transaction. In the update, the writer acquires a row-level lock for the updated row only, preventing other writers from modifying this row.
t4	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 612 101 600	SELECT employee_id, salaryFROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 700	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	Each session simultaneously issues the original query. Session 1 shows the salary of 612 resulting from the t1 update, but not the salary update for employee 101 made in session 2. The reader in session 2 shows the salary update made in session 2, but not the salary update made in session 1. The reader in session 3 uses read consistency to show the salaries before modification by session 1 and 2.

See Also:

• Oracle Database SQL Language Reference

• Oracle Database Reference to learn about V$LOCK

Storage of Row Locks

Unlike some databases, which use a lock manager to maintain a list of locks in memory, Oracle Database stores lock information in the data block that contains the locked row.

The database uses a queuing mechanism for acquisition of row locks. If a transaction requires a lock for an unlocked row, then the transaction places a lock in the data block. Each row modified by this transaction points to a copy of the transaction ID stored in the block header (see "Overview of Data Blocks").

When a transaction ends, the transaction ID remains in the block header. If a different transaction wants to modify a row, then it uses the transaction ID to determine whether the lock is active. If the lock is active, then the session asks to be notified when the lock is released. Otherwise, the transaction acquires the lock.

See Also:

Oracle Database Reference to learn about V$TRANSACTION

Locks and Foreign Keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

Locks and Unindexed Foreign Keys

When both of the following conditions are true, the database acquires a full table lock on the child table:

• No index exists on the foreign key column of the child table.

• A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.

  Suppose that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id. Figure 9-3 shows a session modifying the primary key attributes of department 60 in the departments table.

Figure 9-3 Locking Mechanisms with Unindexed Foreign Key

Description of "Figure 9-3 Locking Mechanisms with Unindexed Foreign Key"

In Figure 9-3, the database acquires a full table lock on employees during the primary key modification of department 60. This lock enables other sessions to query but not update the employees table. For example, employee phone numbers cannot be updated. The table lock on employees releases immediately after the primary key modification on the departments table completes. If multiple rows in departments undergo primary key modifications, then a table lock on employees is obtained and released once for each row that is modified in departments.

Note:

DML on a child table does not acquire a table lock on the parent table.

Locks and Indexed Foreign Keys

When both of the following conditions are true, the database does not acquire a full table lock on the child table:

• A foreign key column in the child table is indexed.

• A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.

A lock on the parent table prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or child table during the primary key modification. This situation is preferable if primary key modifications occur on the parent table while updates occur on the child table.

Figure 9-4 shows child table employees with an indexed department_id column. A transaction deletes department 280 from departments. This deletion does not cause the database to acquire a full table lock on the employees table as in the scenario described in "Locks and Unindexed Foreign Keys".

Figure 9-4 Locking Mechanisms with Indexed Foreign Key

Description of "Figure 9-4 Locking Mechanisms with Indexed Foreign Key"

If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, the deletion of department 280 can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.

See Also:

• "Foreign Key Constraints"

• "Overview of Indexes"

DDL Locks

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.

System Locks

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.