Lock Mechanism in Oracle
Oracle Database provides:
among transactions through a locking mechanisms.
The locks are:
Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. The resources can be either:
Oracle Database automatically obtains and manages necessary locks when executing SQL statements, so you need not be concerned with such details. However, the database also lets you lock data manually.
Locks are not the only event where your session can wait (or freeze)
A lock is a mechanism that prevents destructive interactions, which are interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data.
Oracle Database automatically obtains necessary locks when executing SQL statements. For example, before the database permits a session to modify data, the session must first lock the data. The lock gives the session exclusive control over the data so that no other transaction can modify the locked data until the lock is released.
Because the locking mechanisms of Oracle Database are tied closely to transaction control, application designers need only define transactions properly, and Oracle Database automatically manages locking. Users never need to lock any resource explicitly, although Oracle Database also enables users to lock data manually.
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:
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 behaviour of Oracle Database for readers and writers:
Readers of data may have to wait for writers of the same data blocks in very special cases of pending distributed transactions.
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:
Oracle Database uses two modes of locking in a multi-user database:
The Exclusive lock mode prevents the associated resource from being shared. A transaction obtains an exclusive lock when it modifies data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.
The Share lock mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer who needs an exclusive lock. Several transactions can acquire share locks on the same resource.
Assume that a transaction uses a SELECT … FOR UPDATE statement to select a single table row. The transaction acquires:
Thus, the database permits as many statements as possible to execute.
Oracle Database performs lock conversion as necessary. In lock conversion, the database automatically converts a table lock of lower restrictiveness to one of higher restrictiveness.
For example, suppose a transaction issues a SELECT … FOR UPDATE for an employee and later updates the locked row. In this case, the database automatically converts the row share table lock to a row exclusive table lock. A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed.
Lock conversion is different from lock escalation, which occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table).
If a user locks many rows in a table, then some databases automatically escalate the row locks to a single table.
The number of locks decreases, but the restrictiveness of what is locked increases.
Oracle Database never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Assume that a system is trying to escalate locks on behalf of transaction 1 but cannot because of the locks held by transaction 2. A deadlock is created if transaction 2 also requires lock escalation of the same data before it can proceed.
Oracle Database automatically locks a resource on behalf of a transaction to prevent other transactions from doing something that requires exclusive access to the same resource. The database automatically acquires:
depending on the resource and the operation being performed.
The database never locks rows when performing simple reads.
Oracle Database locks are divided into the following categories.
Located in the directory : oracle_home\db_1\RDBMS\ADMIN
The base table for all locks is the VLOCK view.
Oracle has several views for showing lock status, some of which show the username:
Oracle Database automatically releases a lock when some event occurs so that the transaction no longer requires the resource.
In most cases, the database holds locks acquired by statements within a transaction for the duration of the transaction.
These locks prevent destructive interference such as:
from concurrent transactions.
A table lock taken on a child table because of an unindexed foreign key is held for the duration of the statement, not the transaction. Also, the DBMS_LOCK package enables user-defined locks to be released and allocated at will and even held over transaction boundaries.
Oracle Database releases all locks acquired by the statements within a transaction when it commits or rolls back. Oracle Database also releases locks acquired after a savepoint when rolling back to the savepoint.
However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions continue to wait until after the original transaction commits or rolls back completely
There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time.
There are only three types of user locks:
The DBA_LOCK_INTERNAL view used to show locks for a specific user, and you can specify the query in the form:
SELECT
NVL(b.username,'SYS') username,
session_id,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
FROM
sys.dba_lock_internal a,
sys.v_$session b
where ...
You can also query vaccess and vlocked_object to see specific locks:
select s.sid, s.serial#, p.spid
from
v$session s,
v$process p
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);
OR
Show locked objects
set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
In the menu Database / Monitor / Session Browser
10gR2 or above.
alter session set DDL_WAIT_FOR_LOCKS=true;
Session altered.