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
