A lock is a mechanism that prevents destructive interactions.
Interactions are destructive when they incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data.
Locks play a crucial role in maintaining database concurrency and consistency.
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 circumstances1 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.
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 occurs automatically and requires no user action.
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 are source 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.
