The lock
mechanism defaults to a fine-grained, row-level locking mode. Different transactions
can be updating different rows within the same table without interfering with
one another.
Although
the default model is to lock at the row level, Oracle Database supports manual
locking at higher levels if needed:
SQL>
LOCK TABLE employees IN EXCLUSIVE MODE;
Table(s)
Locked.
With the
preceding statement, any other transaction that tries to update a row in the
locked table must wait until the transaction that issued the lock request
completes. EXCLUSIVE is the strictest lock mode. The following are the other
lock modes:
• ROW
SHARE: Permits
concurrent access to the locked table but prohibits sessions from locking the
entire table for exclusive access
•
ROW
EXCLUSIVE: Is the
same as ROW SHARE, but also prohibits locking in SHARE mode. The ROW EXCLUSIVE
locks are automatically obtained when updating, inserting, or deleting data. ROW
EXCLUSIVE locks allow multiple readers and one writer.
•
SHARE: Permits concurrent queries but
prohibits updates to the locked table. A SHARE lock is required (and automatically
requested) to create an index on a table. However, online index creation
requires a ROW SHARE lock that is used when building the index.
Data
Concurrency (continued)
Share locks allow multiple readers and no
writers. Share locks are also used transparently when deleting or updating rows
in a parent table that has a child table with foreign key constraints on the
parent.
•
SHARE
ROW EXCLUSIVE: Is
used to query a whole table and to allow others to query rows in the table, but
prohibits others from locking the table in SHARE mode or updating rows
•
EXCLUSIVE: Permits queries on the locked table
but prohibits any other activity on it. An EXCLUSIVE lock is required to drop a
table.
Like any
request for a lock, manual lock statements wait until all sessions that either
already have locks or have previously requested locks release their locks. The LOCK
command accepts a special argument that controls the waiting behavior NOWAIT.
NOWAIT
returns control to you immediately if the specified table is already locked by
another session:
SQL>
LOCK TABLE hr.employees IN SHARE MODE NOWAIT;
LOCK TABLE
hr.employees IN SHARE MODE NOWAIT
*
ERROR at
line 1:
ORA-00054:
resource busy and acquire with NOWAIT specified
It is
usually not necessary to manually lock objects. The automatic locking mechanism
provides the data concurrency needed for most applications. Oracle recommends
that you avoid using manual locks, especially when developing applications.
Severe performance issues often occur from unnecessarily high locking levels.
