CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Table Locks
Version 11.1
 
General

Dictionary Objects
ALL_WM_LOCKED_TABLES GV$LOCK_TYPE
DBA_BLOCKERS GV$_LOCK
DBA_DDL_LOCKS USER_WM_LOCKED_TABLES
DBA_DML_LOCKS V_$DLM_ALL_LOCKS
DBA_LOCK V_$DLM_LOCKS
DBA_LOCK_INTERNAL V_$GLOBAL_BLOCKED_LOCKS
DBMS_LOCK V_$LOCK
DBMS_LOCK_ALLOCATED (table) V_$LOCKED_OBJECT
GV$DLM_ALL_LOCKS V_$LOCKS_WITH_COLLISIONS
GV$DLM_LOCKS V_$LOCK_ACTIVITY
GV$GLOBAL_BLOCKED_LOCKS V_$LOCK_ELEMENT
GV$LOCK V_$LOCK_TYPE
GV$LOCKED_OBJECT V_$_LOCK
GV$LOCKS_WITH_COLLISIONS WM$ALL_LOCKS_VIEW
GV$LOCK_ACTIVITY WM$LOCKROWS_INFO
GV$LOCK_ELEMENT WM$LOCKROWS_INFO_IDX
 
Lock Definitions

Descriptions
Name

Description

sub-share Can be used on an aggregate object to indicate that share locks are being aquired on sub-parts of the object.
sub-exclusive Can be used on an aggregate object to indicate that exclusive locks are being aquired on sub-parts of the object.
share-sub-exclusive Indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks.
  
Lock Escalation
How to make lock escalation look good Subject: Re: How to make lock escalation look good...
Date: Wed, 21 May 2003 14:03:35 GMT
From: "Jim Kennedy" <kennedy-down_with_spammers@attbi.com>
Newsgroups:  comp.databases.oracle.server

I've worked with DB2 and it requires throwing most transactions out the window. Worse than that dynamic sql MUST be followed immediately by a commit or no one else can do one. (their query tool does this for you) DB2 does NOT do dynamic sql; it does static sql. What it does with "dynamic" sql is create a plan and bind it in then run it - turning dynamic to static and that puts a lock on the plan table until you commit. So anyone else running dynamic sql can't until you commit. (plan table is a source of serialization for the entire system). When I worked with it (DB2 on a mainframe) we had to make sure all DML had a commit immediately after it.
 
Lock Demo

Locking Demo
Session 1 Session 2
conn / as sysdba conn scott/tiger
UPDATE emp
SET deptno=deptno+10
WHERE deptno < 40;
SELECT username, gv$lock.sid,
TRUNC(id1/power(2,16)) rbs,
BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq, lmode, request
FROM gv$lock, gv$session
WHERE gv$lock.type = 'TX'
AND gv$lock.sid = gv$session.sid
AND gv$session.username = 'SCOTT';

SELECT XIDUSN, XIDSLOT, XIDSQN
FROM gv$transaction;
rollback;
 
Lock Table
Table Locking LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT | WAIT <seconds>];
LOCK TABLE uwclass.servers IN exclusive MODE WAIT 120;
 
Lock Related Queries

Active Table Locks
SELECT SUBSTR(a.object,1,25) TABLENAME,
SUBSTR(s.username,1,15) USERNAME,
SUBSTR(p.pid,1,5) PID,
SUBSTR(p.spid,1,10) SYSTEM_ID,
DECODE(l.type,
  'RT','Redo Log Buffer',
  'TD','Dictionary',
  'TM','DML',
  'TS','Temp Segments',
  'TX','Transaction',
  'UL','User',
  'RW','Row Wait',
  l.type) LOCK_TYPE
FROM gv$access a, gv$process p, gv$session s, gv$lock l
WHERE s.sid = a.sid
AND s.paddr = p.addr
AND l.sid = p.pid
GROUP BY a.object, s.username, p.pid, l.type, p.spid
ORDER BY a.object, s.username;

Active Locks
SELECT s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name
object, k.kaddr, DECODE(l.locked_mode,
  1, 'No Lock',
  2, 'Row Share',
  3, 'Row Exclusive',
  4, 'Shared Table',
  5, 'Shared Row Exclusive',
  6, 'Exclusive') locked_mode,
  DECODE(k.type,
    'BL','Buffer Cache Management (PCM lock)',
  'CF','Controlfile Transaction',
  'CI','Cross Instance Call',
  'CU','Bind Enqueue',
  'DF','Data File',
  'DL','Direct Loader',
  'DM','Database Mount',
  'DR','Distributed Recovery',
  'DX','Distributed Transaction',
  'FS','File Set',
  'IN','Instance Number',
  'IR','Instance Recovery',
  'IS','Instance State',
  'IV','Library Cache Invalidation',
  'JQ','Job Queue',
  'KK','Redo Log Kick',
  'LA','Library Cache Lock',
  'LB','Library Cache Lock',
  'LC','Library Cache Lock',
  'LD','Library Cache Lock',
  'LE','Library Cache Lock',
  'LF','Library Cache Lock',
  'LG','Library Cache Lock',
  'LH','Library Cache Lock',
  'LI','Library Cache Lock',
  'LJ','Library Cache Lock',
  'LK','Library Cache Lock',
  'LL','Library Cache Lock',
  'LM','Library Cache Lock',
  'LN','Library Cache Lock',
  'LO','Library Cache Lock',
  'LP','Library Cache Lock',
  'MM','Mount Definition',
  'MR','Media Recovery',
  'NA','Library Cache Pin',
  'NB','Library Cache Pin',
  'NC','Library Cache Pin',
  'ND','Library Cache Pin',
  'NE','Library Cache Pin',
  'NF','Library Cache Pin',
  'NG','Library Cache Pin',
  'NH','Library Cache Pin',
  'NI','Library Cache Pin',
  'NJ','Library Cache Pin',
  'NK','Library Cache Pin',
  'NL','Library Cache Pin',
  'NM','Library Cache Pin',
  'NN','Library Cache Pin',
  'NO','Library Cache Pin',
  'NP','Library Cache Pin',
  'NQ','Library Cache Pin',
  'NR','Library Cache Pin',
  'NS','Library Cache Pin',
  'NT','Library Cache Pin',
  'NU','Library Cache Pin',
  'NV','Library Cache Pin',
  'NW','Library Cache Pin',
  'NX','Library Cache Pin',
  'NY','Library Cache Pin',
  'NZ','Library Cache Pin',
  'PF','Password File',
  'PI','Parallel Slaves',
  'PR','Process Startup',
  'PS','Parallel Slave Synchronization',
  'QA','Row Cache Lock',
  'QB','Row Cache Lock',
  'QC','Row Cache Lock',
  'QD','Row Cache Lock',
  'QE','Row Cache Lock',
  'QF','Row Cache Lock',
  'QG','Row Cache Lock',
  'QH','Row Cache Lock',
  'QI','Row Cache Lock',
  'QJ','Row Cache Lock',
  'QK','Row Cache Lock',
  'QL','Row Cache Lock',
  'QM','Row Cache Lock',
  'QN','Row Cache Lock',
  'QO','Row Cache Lock',
  'QP','Row Cache Lock',
  'QQ','Row Cache Lock',
  'QR','Row Cache Lock',
  'QS','Row Cache Lock',
  'QT','Row Cache Lock',
  'QU','Row Cache Lock',
  'QV','Row Cache Lock',
  'QW','Row Cache Lock',
  'QX','Row Cache Lock',
  'QY','Row Cache Lock',
  'QZ','Row Cache Lock',
  'RT','Redo Thread',
  'SC','System Commit number',
  'SM','SMON synchronization',
  'SN','Sequence Number',
  'SQ','Sequence Enqueue',
  'SR','Synchronous Replication',
  'SS','Sort Segment',
  'ST','Space Management Transaction',
  'SV','Sequence Number Value',
  'TA','Transaction Recovery',
  'TM','DML Enqueue',
  'TS','Table Space (or Temporary Segment)',
  'TT','Temporary Table',
  'TX','Transaction',
  'UL','User-defined Locks',
  'UN','User Name',
  'US','Undo segment Serialization',
  'WL','Writing redo Log',
  'XA','Instance Attribute Lock',
  'XI','Instance Registration Lock') type
FROM gv$session s, sys.gv$lock c, sys.gv$locked_object l,
     dba_objects o, sys.gv$lock k, gv$lock v
WHERE o.object_id = l.object_id
AND l.session_id = s.sid
AND k.sid = s.sid
AND s.saddr = c.saddr
AND k.kaddr = c.kaddr
AND k.kaddr = v.kaddr
AND v.saddr = s.saddr
AND k.lmode = l.locked_mode
AND k.lmode = c.lmode
AND k.request = c.request
ORDER BY object;

List Locks
set wrap off
col lock_type format a12
col mode_held format a10
col mode_requested format a10
col blocking_others format a20
col username format a10

SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1
FROM dba_lock l
WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread');

Locked Objects
SELECT oracle_username USERNAME, owner OBJECT_OWNER,
object_name, object_type, s.osuser,
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 gv$locked_object v, dba_objects d,
gv$lock l, gv$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;
Locked Objects SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;
Locked Objects SELECT DECODE(request,0,'Holder: ','Waiter:') || sid sess, id1, id2, lmode, request, type
FROM gv$lock
WHERE (id1, id2, type) IN (
  SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;

Objects that have been lock for 2 minutes or more 
SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 120
AND h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert desc;
 
Other Related Topics
Deadlocks
SELECT FOR UPDATE
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [78 users online]    © 2010 psoug.org