Before the database allows a session to modify data, the session must first lock the data that is being modified. A lock gives the session exclusive control over the data so that no other transaction can modify the locked data until the lock is released.
Transactions
can lock individual rows of data, multiple rows, or even entire tables. Oracle
Database supports both manual and automatic locking. Automatically acquired
locks always choose the lowest possible level of locking to minimize potential
conflicts with other transactions.
Locking Mechanism
The
locking mechanism is designed to provide the maximum possible degree of data
concurrency within the database. Transactions that modify data acquire
row-level locks rather than block-level or table-level locks. Modifications to
objects (such as table moves) obtain object-level locks rather than whole
database or schema locks.
Data
queries do not require a lock, and a query succeeds even if someone has locked
the data (always showing the original, prelock
value reconstructed from undo information).
When
multiple transactions need to lock the same resource, the first transaction to
request the lock obtains it. Other transactions wait until the first
transaction completes. The queue mechanism is automatic and requires no
administrator interaction.
All
automatic locking is released on commit. Transactions are completed when a COMMIT or ROLLBACK is
issued. In the case of a failed transaction, the same background process that
automatically rolls back any changes from the failed transaction releases all
locks held by that transaction.
