Concurrency and Transaction in Oracle

Locks Obtained by DML Statements in Oracle

from Oracle Database Concepts

Oracle provides data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle automatically manages locking.

Keep in mind that Oracle locking is fully automatic and requires no user action. Implicit locking occurs for all SQL statements so that database users never need to lock any resource explicitly. Oracle's default locking mechanisms lock data at the lowest level of restrictiveness to guarantee data integrity while allowing the highest degree of data concurrency.

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.

The following table summarize how Oracle automatically locks data on behalf of different DML operations.

DML Statement	Row Locks	Table Locks
SELECT ... FROM table
INSERT INTO table ...	Exclusive Row Locks	Row Exclusive Table Locks
UPDATE table ...	Exclusive Row Locks	Row Exclusive Table Locks
DELETE FROM table ...	Exclusive Row Locks	Row Exclusive Table Locks
SELECT ... FROM table ... FOR UPDATE OF ...	Exclusive Row Locks	Row Share Table Locks

• Exclusive Row Lock
  • A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE 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.
• Row Exclusive Table Locks
  • A row exclusive table lock generally indicates that the transaction holding the lock has made one or more updates to rows in the table.
  • 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.
  • A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing.
• Row Share Table Locks
  • A row share table lock 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 held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table.
  • A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table using only LOCK TABLE table IN EXCLUSIVE MODE statement.

Locking for SELECT statement

• 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 FOR UPDATE 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.)

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

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