Blocking and Waiting Locks (vlockb2.sql)

What This Script Does

This script identifies blocking and waiting lock situations in the database by showing both the blocking sessions and the sessions waiting for locks. It provides comprehensive session details to help DBAs quickly identify and resolve lock contention issues that can severely impact application performance.

Script

rem vlockb.sql
ttitle 'Blocking/Waiting Locks'
rem
set lines 132
rem
col ID1 format 9999999999
col ID2 format 9999999999
col LMODE format 999
col REQUEST format 999
col SID format 9999
col username format a9
col osuser format a8
col module format a15
col machine format a15
rem ADDR             RAW(4)
rem KADDR            RAW(4)
rem TYPE             VARCHAR2(2)
col CTIME format 99999
col BLOCK format 99999
col blocker format a7
rem
break on id1 on id2 skip 1
rem
select /*+ ordered use_nl(l,s) */
   l.ID1,
   l.ID2,
   decode(l.block, 0, null, 'Blocker') blocker,
   l.SID,
   s.username,
   s.osuser,
   s.machine,
   l.LMODE,
   l.REQUEST,
   l.TYPE,
   l.ADDR,
   l.KADDR,
   l.CTIME,
   l.BLOCK
from v$lock l,
     v$session s
where (l.id1, l.id2) in
   (select id1, id2
      from v$lock
     where request > 0)
  and s.sid=l.sid+0
order by l.id1, l.id2, l.block desc, l.lmode desc, l.CTIME desc
/

Usage

-- Run the script in SQL*Plus or SQLcl
@vlockb2.sql

-- No parameters required
-- Shows only active blocking situations
-- Ordered to show blockers first, then waiters

Required Privileges

SELECT on V$LOCK
SELECT on V$SESSION
Available to most database users

Sample Output

Blocking/Waiting Locks

       ID1        ID2 BLOCKER   SID USERNAME  OSUSER   MACHINE         LMODE REQUEST TYPE         ADDR     KADDR     CTIME  BLOCK
---------- ---------- ------- ----- --------- -------- --------------- ----- ------- ---- -------- -------- ------ ------
    131075      45678 Blocker   234 APPUSER   oracle   app-server01        6       0 TX   07000000 08000000    450      1
                                345 HR_USER   jsmith   workstation02       0       6 TX   07000001 08000001     25      0
                                456 APPUSER   oracle   app-server02        0       6 TX   07000002 08000002     10      0

    262150      12345 Blocker   567 BATCH_USR batch    batch-server        6       0 TM   07000003 08000003   1250      2
                                678 REPORT    rptuser  report-srv01        0       4 TM   07000004 08000004     75      0
                                789 APPUSER   oracle   app-server03        0       4 TM   07000005 08000005     60      0

Key Output Columns

ID1/ID2: Lock resource identifiers
BLOCKER: Indicates session holding the blocking lock
SID: Session ID
USERNAME: Database username
OSUSER: Operating system user
MACHINE: Client machine name
LMODE: Lock mode held (0-6)
REQUEST: Lock mode requested (0-6)
TYPE: Lock type (TX, TM, etc.)
CTIME: Time lock held in seconds
BLOCK: Number of sessions being blocked

Understanding Lock Modes

Lock Mode Values (LMODE/REQUEST)

0: None
1: Null (NULL)
2: Row-S (SS)
3: Row-X (SX)
4: Share (S)
5: S/Row-X (SSX)
6: Exclusive (X)

Common Lock Types

TX: Transaction locks (row-level)
TM: Table locks (DML operations)
UL: User-defined locks
CF: Control file transaction

Performance Analysis

Blocking Patterns

Single blocker, multiple waiters: Common bottleneck
Chain blocking: Session A blocks B, B blocks C
Long CTIME values: Indicate long-running transactions
High BLOCK count: Shows widespread impact

Impact Assessment

CTIME > 300: Long-held locks (>5 minutes)
BLOCK > 5: Significant concurrency impact
Multiple TX locks: Row-level contention
TM locks with TX: Possible unindexed foreign keys

Common Use Cases

Active Lock Investigation

-- Identify current blocking situations
@vlockb2.sql
-- Note blocking SIDs and investigate their activity

Kill Blocking Sessions

-- After identifying blockers
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- Get serial# from v$session

Application Deadlock Analysis

-- Run during deadlock alerts
@vlockb2.sql
-- Check for circular dependencies in output

Troubleshooting Lock Issues

Identifying Root Cause

Check blocking session activity:

SELECT sql_id, sql_text
FROM v$session s, v$sql q
WHERE s.sid = &blocking_sid
AND s.sql_id = q.sql_id;

Find locked objects:

SELECT object_name, object_type
FROM dba_objects
WHERE object_id = &id1;

Session wait details:

SELECT event, wait_time, seconds_in_wait
FROM v$session
WHERE sid IN (SELECT sid FROM v$lock WHERE request > 0);

Common Lock Scenarios

Unindexed Foreign Keys

Symptom: TM locks during deletes/updates
Solution: Create indexes on foreign key columns
Detection: Check child tables of locked parent

Long-Running Transactions

Symptom: High CTIME values
Solution: Commit more frequently
Prevention: Implement transaction timeouts

Application Design Issues

Symptom: Frequent same-resource contention
Solution: Review locking order
Prevention: Use SELECT FOR UPDATE NOWAIT

Advanced Analysis

Lock Dependency Tree

-- Show complete blocking hierarchy
WITH lock_tree AS (
  SELECT LEVEL lvl,
         LPAD(' ', 2*(LEVEL-1)) || l.sid sid_tree,
         l.id1, l.id2, l.lmode, l.request, l.ctime, l.block
  FROM v$lock l
  START WITH l.block > 0
  CONNECT BY PRIOR l.id1 = l.id1
         AND PRIOR l.id2 = l.id2
         AND PRIOR l.block > 0
         AND l.request > 0
)
SELECT * FROM lock_tree;

Historical Lock Analysis

-- If using AWR
SELECT * FROM dba_hist_enqueue_stat
WHERE enq_type IN ('TX','TM')
AND wait_count > 0
ORDER BY cum_wait_time DESC;

Lock Wait Time Analysis

-- Current wait times
SELECT l.sid,
       s.username,
       l.type,
       l.id1,
       l.id2,
       l.ctime lock_held_seconds,
       w.seconds_in_wait
FROM v$lock l, v$session s, v$session_wait w
WHERE l.sid = s.sid
AND s.sid = w.sid
AND l.request > 0
ORDER BY w.seconds_in_wait DESC;

Lock Analysis (gvlock.sql) - Basic lock information
Blocking Sessions (gvlockb.sql) - Simpler blocking view
Session Analysis (gvsess.sql) - Detailed session information
Enqueue Waits (enqueue.sql) - Enqueue wait statistics