DML Locks: Row-Level Lock: Exclusive Table-Level Lock: Row exclusive
DDL Locks: DDL is not allowed if DML Lock was there and "ORA-00054: resource bus" will
be returned unless DDL_LOCK_TIMEOUT is defined in seconds (11g).
Explicit Table Locking: DDL requires Table exclusive DML lock. You can specify that a DDL
command wait for a specific length of time before it fails:
LOCK TABLE ... IN lockmode MODE [NOWAIT | WAIT integer]
lockmode: [ROW] SHARE, [[SHARE] ROW] EXCLUSIVE, SHARE UPDATE
integer in seconds
Latches: are internal mechanisms that protect shared data structures in the SGA.
Data dictionary locks: whenever the dictionary objects are being modified.
Distributed locks: used in a distributed database system or in RAC.
Internal locks: are used by Oracle to protect access to structures such as datafiles,
tablespaces, and rollback segments.
-- current locks in the DB
select SID,
DECODE(TO_CHAR(BLOCK),'0','Not-Blocking','1','Blocking') IS_BLOCKING,
DECODE(TYPE,'TM','DML enqueue','TX','Transaction enqueue','UL','User
supplied',TYPE) LOCK_TYPE,
DECODE(TO_CHAR(LMODE),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') HELD_LMODE,
DECODE(TO_CHAR(REQUEST),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') REQUEST_LMODE
from v$lock;
-- Oracle 11g: to allow DDL wait for lock instead or returning error
ALTER SESSION SET ddl_lock_timeout = 30;
-- explicit table locking (to acquire an exclusive lock=no updates)
-- to release the lock: ROLLBACK, COMMIT
lock table emp in EXCLUSIVE mode nowait ;
Identifying Blocking Sessions
-- oracle supplied script printing blocking sessions in tree-like view
@$ORACLE_HOME/rdbms/admin/utllockt.sql
select SID,
DECODE(TYPE,'TM','DML enqueue','TX','Transaction enqueue','UL','User
supplied',TYPE) LOCK_TYPE,
DECODE(TO_CHAR(LMODE),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') HELD_LMODE,
DECODE(TO_CHAR(REQUEST),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') REQUEST_LMODE
from v$lock
where BLOCK=1;
SELECT sid, username, blocking_session blocking_sid
FROM V$SESSION WHERE blocking_session_status='VALID';
DDL Locks: DDL is not allowed if DML Lock was there and "ORA-00054: resource bus" will
be returned unless DDL_LOCK_TIMEOUT is defined in seconds (11g).
Explicit Table Locking: DDL requires Table exclusive DML lock. You can specify that a DDL
command wait for a specific length of time before it fails:
LOCK TABLE ... IN lockmode MODE [NOWAIT | WAIT integer]
lockmode: [ROW] SHARE, [[SHARE] ROW] EXCLUSIVE, SHARE UPDATE
integer in seconds
Latches: are internal mechanisms that protect shared data structures in the SGA.
Data dictionary locks: whenever the dictionary objects are being modified.
Distributed locks: used in a distributed database system or in RAC.
Internal locks: are used by Oracle to protect access to structures such as datafiles,
tablespaces, and rollback segments.
-- current locks in the DB
select SID,
DECODE(TO_CHAR(BLOCK),'0','Not-Blocking','1','Blocking') IS_BLOCKING,
DECODE(TYPE,'TM','DML enqueue','TX','Transaction enqueue','UL','User
supplied',TYPE) LOCK_TYPE,
DECODE(TO_CHAR(LMODE),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') HELD_LMODE,
DECODE(TO_CHAR(REQUEST),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') REQUEST_LMODE
from v$lock;
-- Oracle 11g: to allow DDL wait for lock instead or returning error
ALTER SESSION SET ddl_lock_timeout = 30;
-- explicit table locking (to acquire an exclusive lock=no updates)
-- to release the lock: ROLLBACK, COMMIT
lock table emp in EXCLUSIVE mode nowait ;
Identifying Blocking Sessions
-- oracle supplied script printing blocking sessions in tree-like view
@$ORACLE_HOME/rdbms/admin/utllockt.sql
select SID,
DECODE(TYPE,'TM','DML enqueue','TX','Transaction enqueue','UL','User
supplied',TYPE) LOCK_TYPE,
DECODE(TO_CHAR(LMODE),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') HELD_LMODE,
DECODE(TO_CHAR(REQUEST),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') REQUEST_LMODE
from v$lock
where BLOCK=1;
SELECT sid, username, blocking_session blocking_sid
FROM V$SESSION WHERE blocking_session_status='VALID';