What concurrency problems does a database face

The purpose of a DML lock (data lock) is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. DML statements automatically acquire both table-level locks and row-level locks.

Row Locks (TX)

Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.

There is no limit to the number of row locks held by a statement or transaction, and Oracle does not escalate locks from the row level to a coarser granularity. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.

The combination of multiversion concurrency control and row-level locking means that users contend for data only when accessing the same rows, specifically:

  • Readers of data do not wait for writers of the same data rows.

  • Writers of data do not wait for readers of the same data rows unless  ... is used, which specifically requests a lock for the reader.

  • Writers only wait for other writers if they attempt to update the same rows at the same time.

Note:

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

A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: , , , and with the clause.

A modified row is always locked exclusively so that other transactions cannot modify the row until the transaction holding the lock is committed or rolled back. However, if the transaction dies due to instance failure, block-level recovery makes a row available before the entire transaction is recovered. Row locks are always acquired automatically by Oracle as a result of the statements listed previously.

If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

Table Locks (TM)

Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation. When a DDL or DML statement is on a table, a table lock is acquired. Table locks do not affect concurrency of DML operations. For partitioned tables, table locks can be acquired at both the table and the subpartition level.

A transaction acquires a table lock when a table is modified in the following DML statements: , , , with the clause, and . These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. Any table lock prevents the acquisition of an exclusive DDL lock on the same table and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it.

A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.

Table 13-3 shows the table lock modes that statements acquire and operations that those locks permit and prohibit.

Table 13-3 Summary of Table Locks

SQL StatementMode of Table LockLock Modes Permitted?
RSRXSSRXX

none

Y

Y

Y

Y

Y

RX

Y

Y

N

N

N

RX

Y*

Y*

N

N

N

RX

Y*

Y*

N

N

N

RS

Y*

Y*

Y*

Y*

N

RS

Y

Y

Y

Y

N

RX

Y

Y

N

N

N

S

Y

N

Y

N

N

SRX

Y

N

N

N

N

X

N

N

N

N

N


RS: row share

RX: row exclusive

S: share

SRX: share row exclusive

X: exclusive

*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

The following sections explain each mode of table lock, from least restrictive to most restrictive. They also describe the actions that cause the transaction to acquire a table lock in that mode and which actions are permitted and prohibited in other transactions by a lock in that mode.

Row Share Table Locks (RS)

A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share table lock is automatically acquired for a table when one of the following SQL statements is run:

SELECT ... FROM table ... FOR UPDATE OF ... ; LOCK TABLE table IN ROW SHARE MODE;

A row share table lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

Permitted Operations: A row share table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, other transactions can obtain simultaneous row share, row exclusive, share, and share row exclusive table locks for the same table.

Prohibited Operations: A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table using only the following statement:

LOCK TABLE table IN EXCLUSIVE MODE;
Row Exclusive Table Locks (RX)

A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:

INSERT INTO table ... ; UPDATE table ... ; DELETE FROM table ... ; LOCK TABLE table IN ROW EXCLUSIVE MODE;

A row exclusive table lock is slightly more restrictive than a row share table lock.

Permitted Operations: A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.

Prohibited Operations: A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:

LOCK TABLE table IN SHARE MODE; LOCK TABLE table IN SHARE EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE;
Share Table Locks (S)

A share table lock is acquired automatically for the table specified in the following statement:

LOCK TABLE table IN SHARE MODE;

Permitted Operations: A share table lock held by a transaction allows other transactions only to query the table, to lock specific rows with ... , or to run ... statements successfully. No updates are allowed by other transactions. Multiple transactions can hold share table locks for the same table concurrently. In this case, no transaction can update the table (even if a transaction holds row locks as the result of a statement with the clause). Therefore, a transaction that has a share table lock can update the table only if no other transactions also have a share table lock on the same table.

Prohibited Operations: A share table lock held by a transaction prevents other transactions from modifying the same table and from executing the following statements:

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE; LOCK TABLE table IN ROW EXCLUSIVE MODE;
Share Row Exclusive Table Locks (SRX)

A share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) is more restrictive than a share table lock. A share row exclusive table lock is acquired for a table as follows:

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

Permitted Operations: Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using with the clause, but not to update the table.

Prohibited Operations: A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table. A share row exclusive table lock also prohibits other transactions from obtaining share, share row exclusive, and exclusive table locks, which prevents other transactions from executing the following statements:

LOCK TABLE table IN SHARE MODE; LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE table IN ROW EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE;
Exclusive Table Locks (X)

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:

LOCK TABLE table IN EXCLUSIVE MODE;

Permitted Operations: Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.

Prohibited Operations: An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

DML Locks Automatically Acquired for DML Statements

The previous sections explained the different types of data locks, the modes in which they can be held, when they can be obtained, when they are obtained, and what they prohibit. The following sections summarize how Oracle automatically locks data on behalf of different DML operations.

Table 13-4 summarizes the information in the following sections.

Table 13-4 Locks Obtained By DML Statements

DML StatementRow Locks?Mode of Table Lock
  

X

RX

X

RX

X

RX

X

RS

  
 

RS

 

RX

 

S

 

SRX

 

X


X: exclusive

RX: row exclusive

RS: row share

S: share

SRX: share row exclusive

Default Locking for Queries

Queries are the SQL statements least likely to interfere with other SQL statements because they only read data. , , and statements can have implicit queries as part of the statement. Queries include the following kinds of statements:

SELECT INSERT ... SELECT ... ; UPDATE ... ; DELETE ... ;

They do not include the following statement:

SELECT ... FOR UPDATE OF ... ;

The following characteristics are true of all queries that do not use the clause:

  • A query acquires no data locks. Therefore, other transactions can query and update a table being queried, including the specific rows being queried. Because queries lacking clauses do not acquire any data locks to block other operations, such queries are often referred to in Oracle as nonblocking queries.

  • A query does not have to wait for any data locks to be released; it can always proceed. (Queries may have to wait for data locks in some very specific cases of pending distributed transactions.)

Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE

The locking characteristics of , , , and ... statements are as follows:

  • The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.

  • The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.

  • A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.

  • In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle automatically converts this lock to a row exclusive table lock.