Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

Detect and Resolve Locks

Detect and Resolve Locks

Also see How to debug PL/SQL code

How Oracle handle locking?

For managing access to shared resources oracle use enqueues as locking mechanism. Each enqueue represent a sharable resource. A shared resource can be a table definition, a transaction or any type of structure that represent something sharable between sessions. Each type of actions performed by Oracle sessions on those shared resources will require a certain type of lock or lock mode.

Each resource is represented by an enqueue. An enqueue is uniquely defined by it’s TYPE, ID1 and ID2 (which are columns in the V$LOCK view). The name has the form: . Type has two characters and represent a resource type (e.g. “TM” for the table definition type). ID1 and ID2 are positive numbers and identify the resource fully (e.g. ID1 is the object_id of the table if the resource type is “TM”). For example can there only be one enqueue for user SCOTT’s EMP table (identified by TYPE=TM, ID1=object ID, ID2=0).

The most commonly known resource types are the TM, TX, ST and UL resources.

The TM resource, known as the DML enqueue, is acquired during the execution of a statement when referencing a table so that the table is not dropped or altered during the execution of it.

The TX resource, known as the transaction enqueue, is acquired exclusive when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. Row locking is based on TX enqueues. SMON will acquire it when doing recovery of a killed/crashed process.

The ST resource is used to serialize space management tasks when a session’s job requires extents to be allocated to objects.

The UL resource represent the user-defined locks defined by the DBMS_LOCK package.

The action ‘select on a table’ will require that the executing session has a shared lock on the resource ‘table definition’ of the selected table so that other users should not change the table definition. When conflicting actions are occuring, Oracle will serialize the processing by putting a number of sessions in waiting mode until the work of the blocking session has been completed.

Acquiring a lock is the process of getting a lock on a resource on which we currently do not have a lock. Releasing locks are performed by the sessions when they issue a commit or a DDL statement (i.e. implicit commit), or by SMON if the sessions have been killed.

Row locking in Oracle is based on the TX enqueues and is known as transactional locking. When two or more sessions are changing data on one row of a table (DML statements on the same record), the first session will lock the row by putting his transaction reference in the block containing the row header. The other sessions will look at this lock information and will wait on the transaction (i.e. the TX enqueue of the blocking session) of the first session before proceeding. When the first session performs a commit, the TX resource will be released and the waiters will start their own locking. The waiting sessions are thus waiting on an exclusive TX resource, but their TM resources they are holding give the objects they are in fact waiting on.

If a lock has not been acquired or converted, a deadlock check is made by  the waiting session after a timeout.
For example, following situation  generates a deadlock: 

user A gets an S lock on resource 1, then user B gets an S lock on resource 2; 

later, A request an X lock on resource 2 and waits,then B requests an X lock on resource 1 and waits; 

now, A is waiting for B to release resource 2, which is waiting for A to release  resource 1; 

A is indirectly waiting for A.
It is a deadlock, generating a tracefile in the user_dump_dest and ORA-00060 in the detecting session.

If a session holds an enqueue in share mode, other sessions can then also
    take the enqueue in share mode (for the same resource).

If a session holds an enqueue in exclusive mode, other sessions that wants
    to get it - independently in which level - they have to wait.

The lock and resource information stands within the SGA to allow PMON to
recover in the event of process failure. The PMON is responsible for
releasing the locks of the crashed/killed processes.

Common types of enqueues

JQ - Job Queue. When a job (submitted by DBMS_JOB.SUBMIT) is running, it is protected by a JQ enqueue (which means that only one SNP-process can run the job). ST - Space management Transaction. The ST enqueue is need to be held when the session is allocating/deallocating extents. If the session gets a timeout when requesting the ST enqueue, "ORA-1575 timeout waiting for space management" is returned. TM - DML (Table) enqueue - when a session wants to lock a table, a TM enqueue is requested. If a session deletes a row in the parent-table (DEPT) and a referential constraint (foreign key) is created without an index on the child-table (EMP), or if the session is updating the column(s) that the foreign key references to then a share lock (level 4) is taken on the child table. If another session tries to do changes to the child-table they have to wait (because they want the enqueue in row exclusive mode, and that is not compatible with the share mode). If an index is created on the child-table?s foreign key-column then no share-lock is required on the child-table. TX - Transaction. As soon as a transaction is started a TX enqueue is needed. A transaction is uniquely defined by the rollback segment number. A session can be waiting on a TX enqueue for several reasons: 1) Another session is locking the requested row. 2) When two sessions tries to insert the same unique key into a table (none of them has done a COMMIT), then the last session is waiting for the first one to COMMIT or ROLLBACK. 3) There are no free ITL (Interested Transaction List) in the block header (increase INI_TRANS och PCT_FREE for the segment). UL - User Lock. A session has taken a lock with the DBMS_LOCK.REQUEST function.

Which lock modes are required for which table action?

The following table describes what lock modes on DML enqueues are actually gotten for which table operations in a standard Oracle installation.

Operation Lock Mode LMODE Lock Description
Select NULL 1 null
Select for update SS 2 sub share
Insert SX 3 sub exclusive
Update SX 3 sub exclusive
Delete SX 3 sub exclusive
Lock For Update SS 2 sub share
Lock Share S 4 share
Lock Exclusive X 6 exclusive
Lock Row Share SS 2 sub share
Lock Row Exclusive SX 3 sub exclusive
Lock Share Row Exclusive SSX 5 share/sub exclusive
Alter table X 6 exclusive
Drop table X 6 exclusive
Create Index S 4 share
Drop Index X 6 exclusive
Truncate table X 6 exclusive

compatibility of lock modes

The compatibility of lock modes are normally represented by following matrix:

NULL SS SX S SSX X
NULL Y Y Y Y Y Y
SS Y Y Y Y Y N
SX Y Y Y N N N
S Y Y N Y N N
SSX Y Y N N N N
X Y N N N N N

views used to detect locking problems? A number of Oracle views permits to detect locking problems. To see if the instance has had locking problems since startup, execute the following SELECT: SELECT * FROM v$sysstat WHERE class=4;

STATISTIC# NAME CLASS VALUE
22 enqueue timeouts 4 0 number of enqueue operations (get and convert) that timed out before they could complete.
23 enqueue waits 4 2 how many times a session had to wait for an enqueue. This column is updated first after the wait is finished.
24 enqueue deadlocks 4 0 how many times a deadlock situation has occured (every time the client receives an ORA-60 and a trace file will be created). This value should be zero, else an investigation should be made and the trace files should be checked.
25 enqueue requests 4 213 “enqueue requests” minus “enqueue releases” shows how many locks that are held just now (which is equal to the number of rows in V$LOCK).
26 enqueue conversions 4 0 how many times an session is holding a lock in one mode and then wants to change the mode (for example, first the session is doing an SELECT * FROM emp FOR UPDATE and then a UPDATE emp SET sal=9876 WHERE empno=7839).<
27 enqueue releases 4 204 shows how many times an enqueue was released (freed).

The following query retrieves the event and the segment that caused the wait

Access Privileges: SELECT on dba_extents , v$session_wait 

select username, event,segment_name, segment_type
from dba_extents a, v$session_wait b
where
b.sid=74 and
a.file_id = b.p1 and
b.p2 between (a.block_id and a.block_id+a.blocks – 1);

The following locking information script provides information
regarding the locks currently held in the database.

Access Privileges: SELECT on V_$LOCK, V_$SESSION, SYS.USER$, SYS.OBJ$

select
nvl(S.USERNAME,'Internal') username,
        L.SID,
        nvl(S.TERMINAL,'None') terminal,
        decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') COMMAND,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
                    'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from    V$LOCK L,
        V$SESSION S,
        SYS.USER$ U1,
        SYS.OBJ$ T1
where   L.SID = S.SID
and     T1.OBJ#  = decode(L.ID2,0,L.ID1,1)
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
order by 1,2,5
/

The following script can be used to report the session
and tables that are blocking other sessions

Access Privileges: SELECT on V$LOCK,V$SESSION,SYS.USER$,SYS.OBJ$

select	nvl(S.USERNAME,'Internal') username,
	nvl(S.TERMINAL,'None') terminal,
	L.SID||','||S.SERIAL# Kill,
	U1.NAME||'.'||substr(T1.NAME,1,20) tab,
	decode(L.LMODE,1,'No Lock',
		2,'Row Share',
		3,'Row Exclusive',
		4,'Share',
		5,'Share Row Exclusive',
		6,'Exclusive',null) lmode,
	decode(L.REQUEST,1,'No Lock',
		2,'Row Share',
		3,'Row Exclusive',
		4,'Share',
		5,'Share Row Exclusive',
		6,'Exclusive',null) request
from	V$LOCK L,
	V$SESSION S,
	SYS.USER$ U1,
	SYS.OBJ$ T1
where	L.SID = S.SID
and	T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and	U1.USER# = T1.OWNER#
and	S.TYPE != 'BACKGROUND'
and     S.sid in(select blocking_session from v$session where blocking_session is not null)
order by 1,2,5
/

This script will report the SQL text and Object names related to
locks currently being held in the database.

Access Privileges: SELECT on  v$session, v$sqlarea,  v$lock, v$locked_object, dba_objects

Query 1
-------

SELECT    c.object_name
       || ' in session '
       || a.session_id
       || ' is blocking '
       || d.object_name
       || ' in session '
       || b.session_id
  FROM v$locked_object a,
       v$locked_object b,
       dba_objects c,
       dba_objects d,
       (SELECT l1.SID sid1, ' IS BLOCKING ' BLOCK, l2.SID sid2
          FROM v$lock l1, v$lock l2
         WHERE l1.BLOCK = 1
           AND l2.request > 0
           AND l1.id1 = l2.id1
           AND l1.id2 = l2.id2) l
 WHERE l.sid1 = a.session_id
   AND l.sid2 = b.session_id
   AND a.object_id = c.object_id
   AND b.object_id = d.object_id
   AND c.object_id = d.object_id;

query 2
-------
SELECT    NVL (c.sql_text, 'sid=' || a.SID)
       || ' IS BLOCKING '
       || CHR (10)
       || d.sql_text
  FROM v$session a,
       v$session b,
       v$sqlarea c,
       v$sqlarea d,
       (SELECT l1.SID sid1, ' IS BLOCKING ' BLOCK, l2.SID sid2
          FROM v$lock l1, v$lock l2
         WHERE l1.BLOCK = 1
           AND l2.request > 0
           AND l1.id1 = l2.id1
           AND l1.id2 = l2.id2) l
 WHERE l.sid1 = a.SID
   AND l.sid2 = b.SID
   AND a.sql_address = c.address(+)
   AND b.sql_address = d.address;

Query 3
-------
SELECT
case when c.sql_text is not null then
'SID='||a.sid||' Obj='||o3.object_name||' sql="'||c.sql_text||'" is blocking '
else 'SID='||a.sid||' Obj='||o3.object_name||' is blocking ' end ||
case when d.sql_text is not null then
'SID='||b.sid||' Obj='||o4.object_name||' sql="'||d.sql_text||'"'
else 'SID='||a.sid||' Obj='||o4.object_name end
FROM
 v$session a,
 v$session b,
 v$sqlarea c,
 v$sqlarea d,
 v$lock l1,
 v$lock l2,
 v$locked_object o1,
 v$locked_object o2,
 dba_objects o3,
 dba_objects o4
WHERE
 l1.BLOCK = 1
 AND l2.request > 0
 AND l1.id1 = l2.id1
 AND l1.id2 = l2.id2
 and l1.sid = a.SID
 AND l2.sid = b.SID
 AND a.sql_address = c.address(+)
 AND b.sql_address = d.address
 and l1.sid = o1.session_id
 AND l2.sid = o2.session_id
 AND o1.object_id = o3.object_id
 AND o2.object_id = o4.object_id
/

This script produces information about locks being held or waited on in the database.

Access Privileges: SELECT on DBA_OBJECTS, V$LOCK, V$SESSION

select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
       DECODE(B.ID2, 0, A.OBJECT_NAME,
            'Trans-'||to_char(B.ID1)) OBJECT_NAME,
     B.TYPE,
       DECODE(B.LMODE,0,'--Waiting--',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                   4,'Share',
                      5,'Sha Row Exc',
           6,'Exclusive',
                        'Other') "Lock Mode",
       DECODE(B.REQUEST,0,' ',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                      4,'Share',
                      5,'Sha Row Exc',
                      6,'Exclusive',
                     'Other') "Req Mode"
  from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
  and B.SID = C.SID
  and C.USERNAME is not null
order by B.SID, B.ID2;

This script generates a report of users waiting for locks.

Access Privileges: SELECT on v$session, v$lock

SELECT sn.username, m.sid, m.type,
        DECODE(m.lmode, 0, 'None',
                        1, 'Null',
                        2, 'Row Share',
                        3, 'Row Excl.',
                        4, 'Share',
                        5, 'S/Row Excl.',
                        6, 'Exclusive',
                lmode, ltrim(to_char(lmode,'990'))) lmode,
        DECODE(m.request,0, 'None',
                         1, 'Null',
                         2, 'Row Share',
                         3, 'Row Excl.',
                         4, 'Share',
                         5, 'S/Row Excl.',
                         6, 'Exclusive',
                         request, ltrim(to_char(m.request,
                '990'))) request, m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
        OR (sn.sid = m.sid
                AND m.request = 0 AND lmode != 4
                AND (id1, id2) IN (SELECT s.id1, s.id2
     FROM v$lock s
                        WHERE request != 0
              AND s.id1 = m.id1
                                AND s.id2 = m.id2)
                )
ORDER BY id1, id2, m.request;

The following script displays the objects that are being locked

Access Privileges: SELECT on v$locked_object, dba_objects, v$lock, v$session

SELECT DISTINCT    object_name
                || '   '
                || locked_mode
                || '   '
                || ctime
                || '   '
                || c.SID
                || '  '
                || serial#
           FROM v$locked_object a, dba_objects b, v$lock c, v$session d
          WHERE a.object_id = b.object_id
            AND c.SID = a.session_id
            AND c.SID = d.SID;

To find the query for blocking session

Access Privileges: SELECT on v$session, v$sqlarea

SELECT      'sid='
         || a.SID
         || ' Wait Class='
         || a.wait_class
         || ' Time='
         || a.seconds_in_wait
         || CHR (10)
         || ' Query='
         || b.sql_text
    FROM v$session a, v$sqlarea b
   WHERE a.blocking_session IS NOT NULL AND a.sql_address = b.address
ORDER BY a.blocking_session
/

The following PL/SQL script can be used to display Locked sessions hierachially

Access Privileges: SELECT on v$session

set serveroutput ON

DECLARE
   TYPE array_num_type IS TABLE OF PLS_INTEGER
      INDEX BY BINARY_INTEGER;

   v_session_id   array_num_type;
   v_blocking1    array_num_type;
   v_blocking2    array_num_type;
   v_blocking3    array_num_type;
   v_blocking4    array_num_type;
   v_blocking5    array_num_type;
   v_deadlock     array_num_type;
   sn1            PLS_INTEGER;
   sn2            PLS_INTEGER;
   sn3            PLS_INTEGER;
   sn4            PLS_INTEGER;
   sn5            PLS_INTEGER;
   sn6            PLS_INTEGER;
   is_dead_lock   BOOLEAN;
   str            VARCHAR2 (1000);
BEGIN
   FOR cur1 IN (SELECT SID, blocking_session
                    FROM v$session
                   WHERE blocking_session IS NOT NULL
                ORDER BY logon_time)
   LOOP
      v_session_id (cur1.blocking_session) := cur1.SID;
      v_blocking1 (cur1.SID) := cur1.blocking_session;
   END LOOP;

   sn1 := v_blocking1.FIRST;

   WHILE (sn1 IS NOT NULL)
   LOOP
      sn2 := v_blocking1 (sn1);
      is_dead_lock := FALSE;

      IF v_blocking1.EXISTS (sn2)
      THEN
         sn3 := v_blocking1 (sn2);
         v_blocking2 (sn1) := sn3;

         IF sn3 = sn1
         THEN
            is_dead_lock := TRUE;
         END IF;

         IF v_blocking1.EXISTS (sn3)
         THEN
            sn4 := v_blocking1 (sn3);
            v_blocking3 (sn1) := sn4;

            IF sn4 = sn1 OR sn4 = sn2
            THEN
               is_dead_lock := TRUE;
            END IF;

            IF v_blocking1.EXISTS (sn4)
            THEN
               sn5 := v_blocking1 (sn4);
               v_blocking4 (sn1) := sn5;

               IF sn1 = sn5 OR sn2 = sn5 OR sn3 = sn5
               THEN
                  is_dead_lock := TRUE;
               END IF;

               IF v_blocking1.EXISTS (sn5)
               THEN
                  sn6 := v_blocking1 (sn5);
                  v_blocking5 (sn1) := sn6;

                  IF sn1 = sn6 OR sn2 = sn6 OR sn3 = sn6 OR sn4 = sn6
                  THEN
                     is_dead_lock := TRUE;
                  END IF;
               END IF;
            END IF;
         END IF;
      END IF;

      IF is_dead_lock = TRUE
      THEN
         v_deadlock (sn1) := 1;
      ELSE
         v_deadlock (sn1) := 0;
      END IF;

      sn1 := v_blocking1.NEXT (sn1);
   END LOOP;

   sn1 := v_blocking1.FIRST;

   WHILE (sn1 IS NOT NULL)
   LOOP
      IF v_deadlock (sn1) = 0
      THEN
         str := sn1;
      ELSE
         str := 'Deadlock ' || sn1;
      END IF;

      IF v_blocking1.EXISTS (sn1)
      THEN
         str := str || ' -> ' || v_blocking1 (sn1);
      END IF;

      IF v_blocking2.EXISTS (sn1)
      THEN
         str := str || ' -> ' || v_blocking2 (sn1);
      END IF;

      IF v_blocking3.EXISTS (sn1)
      THEN
         str := str || ' -> ' || v_blocking3 (sn1);
      END IF;

      IF v_blocking4.EXISTS (sn1)
      THEN
         str := str || ' -> ' || v_blocking4 (sn1);
      END IF;

      IF v_blocking5.EXISTS (sn1)
      THEN
         str := str || ' -> ' || v_blocking5 (sn1);
      END IF;

      sn1 := v_blocking1.NEXT (sn1);
      DBMS_OUTPUT.put_line (str);
   END LOOP;
END;
/

Data Dictionary Views related to Locks

V$SESSION_WAIT When a session is waiting on a resource, it can be found waiting on the enqueue wait event,
e.g. SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';
- SID identifier of session holding the lock
- P1, P2, P3 determine the resource when event = 'enqueue'
- SECONDS_IN_WAIT gives how long the wait did occurs

V$SESSION session information and row locking information
- SID, SERIAL# identifier of the session
- LOCKWAIT address of the lock waiting, otherwise null
- ROW_WAIT_OBJ# object identified of the object we are waiting on
(object_id of dba_objects)
- ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# file_id , block_id and
row location within block of the locked row

V$LOCK list of all the locks in the system
- SID identifier of session holding the lock
- TYPE, ID1 and ID2 determine the resource
- LMODE and REQUEST indicate which queue the session is waiting on, as follows:
LMODE > 0, REQUEST = 0 owner
LMODE = 0, REQUEST > 0 acquirer
LMODE > 0, REQUEST > 0 converter
- CTIME time since current mode was converted (see Note 223146.1)
- BLOCK are we blocking another lock
BLOCK = 0 non blocking
BLOCK = 1 blocking others

DBA_LOCK or DBA_LOCKS formatted view on V$LOCK (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)
- SESSION_ID == SID in V$LOCK
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
- MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
- LAST_CONVERT == CTIME of V$LOCK
- BLOCKING_OTHERS formatted value of BLOCK from V$LOCK

V$TRANSACTION_ENQUEUE subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)

V$ENQUEUE_LOCK subset of V$LOCK for the system resources only and
blocked TX resources only. (same description as for the V$LOCK view)

DBA_DML_LOCKS subset of the V$LOCK for the DML (TM) locks only
(created via $ORACLE_HOME/rdbms/admin/catblock.sql
- same description as the DBA_LOCK view)

V$LOCKED_OBJECT same info as DBA_DML_LOCKS, but linked with the
rollback and session information
- XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
- OBJECT_ID object being locked
- SESSION_ID session id
- ORACLE_USERNAME oracle user name
- OS_USER_NAME OS user name
- PROCESS OS process id
- LOCKED_MODE lock mode

V$RESOURCE list of all the currently locked resources in the system.
Each row can be associated with one or more rows in V$LOCK
- TYPE, ID1 and ID2 determine the resource

DBA_DDL_LOCKS has a row for each DDL lock that is being held, and
one row for each outstanding request for a DDL lock. It is subset of DBA_LOCKS
same description as the DBA_LOCK view

DBA_WAITERS view that retrieve information for each session waiting on a
lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
- WAITING_SESSION waiting session
- HOLDING_SESSION holding session
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
- MODE_HELD lock type held
- MODE_REQUESTED lock type requested

DBA_BLOCKERS view that gives the blocking sessions (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
-HOLDING_SESSION holding session