Active Lock Requests (vlock.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides a simple, direct view of all active lock requests in the database. It shows sessions that are currently waiting to acquire locks, which is essential for identifying lock contention and blocking situations in real-time.

Script

set lines 132
select * from v$lock
where request > 0
/

Usage

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

-- No parameters required
-- Shows current lock requests at time of execution

Required Privileges

• SELECT on V$LOCK
• Generally available to most database users

Sample Output

    ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
70000123 70000456        145 TX      65537      12345          0          6        125          0
70000234 70000567        234 TX      65538      67890          0          4         89          0
70000345 70000678        156 TM      98765          0          0          3         45          0
70000456 70000789        278 DL      11111      22222          0          4         12          0

Key Output Columns

• ADDR: Memory address of lock structure
• KADDR: Address of lock state object
• SID: Session ID requesting the lock
• TY: Lock type (TX, TM, DL, etc.)
• ID1/ID2: Lock identifiers (object-specific)
• LMODE: Lock mode currently held (0 = none)
• REQUEST: Lock mode being requested
• CTIME: Time since request started (centiseconds)
• BLOCK: Whether this session is blocking others (0/1)

Understanding Lock Types

Common Lock Types

• TX: Transaction lock (row-level locks)
• TM: Table lock (DML table locks)
• DL: Direct loader lock
• UL: User-defined lock
• RT: Redo thread lock
• TS: Tablespace lock

Lock Modes

• 0: None
• 1: Null (NL)
• 2: Row Share (RS) / Sub Share (SS)
• 3: Row Exclusive (RX) / Sub Exclusive (SX)
• 4: Share (S)
• 5: Share Row Exclusive (SRX) / Share Sub Exclusive (SSX)
• 6: Exclusive (X)

Performance Analysis

Identifying Critical Waits

1. High CTIME values: Long-running lock waits
2. REQUEST = 6 (Exclusive): High-impact lock requests
3. Multiple sessions same ID1/ID2: Lock contention hotspot
4. TX locks: Row-level contention

Wait Time Analysis

• CTIME < 100 (< 1 second): Normal brief waits
• CTIME 100-1000 (1-10 seconds): Concerning waits
• CTIME > 1000 (> 10 seconds): Critical blocking

Common Use Cases

Immediate Troubleshooting

-- Quick check for lock waits
@vlock.sql
-- Look for high CTIME values

Performance Investigation

-- During application slowdowns
@vlock.sql
-- Identify lock types and wait patterns

Real-time Monitoring

-- Continuous monitoring during operations
@vlock.sql
-- Track lock request patterns

Troubleshooting Lock Waits

Identify Blocking Sessions

-- Find who is blocking these requests
SELECT l1.sid waiting_session,
       l2.sid blocking_session,
       l1.type lock_type,
       l1.id1, l1.id2,
       l1.ctime wait_time
FROM v$lock l1, v$lock l2
WHERE l1.request > 0
AND l2.block = 1
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2
AND l1.type = l2.type;

Get Session Details

-- Details about waiting sessions
SELECT s.sid, s.username, s.program, s.machine,
       s.last_call_et, s.status, s.sql_id
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND l.request > 0;

Find Locked Objects

-- What objects are being locked (for TM locks)
SELECT o.owner, o.object_name, o.object_type,
       l.sid, l.type, l.lmode, l.request
FROM v$lock l, dba_objects o
WHERE l.type = 'TM'
AND l.id1 = o.object_id
AND l.request > 0;

Advanced Analysis

Lock Conversion Waits

-- Sessions converting lock modes
SELECT sid, type, id1, id2, lmode, request,
       CASE
         WHEN lmode > 0 AND request > lmode THEN 'UPGRADE'
         WHEN lmode > 0 AND request < lmode THEN 'DOWNGRADE'
         WHEN lmode = 0 THEN 'INITIAL'
       END conversion_type
FROM v$lock
WHERE request > 0;

Lock Wait Chains

-- Find blocking chains
WITH lock_chain AS (
  SELECT l1.sid waiter, l2.sid blocker,
         l1.type, l1.id1, l1.id2, l1.ctime
  FROM v$lock l1, v$lock l2
  WHERE l1.request > 0
  AND l2.block = 1
  AND l1.id1 = l2.id1
  AND l1.id2 = l2.id2
  AND l1.type = l2.type
)
SELECT waiter, blocker, type, ctime,
       CONNECT_BY_ISLEAF is_leaf,
       LEVEL chain_level
FROM lock_chain
CONNECT BY PRIOR waiter = blocker
START WITH blocker NOT IN (SELECT waiter FROM lock_chain);

Historical Lock Analysis

-- Check for recurring lock patterns
SELECT type, COUNT(*) wait_count,
       AVG(ctime) avg_wait_time,
       MAX(ctime) max_wait_time
FROM v$lock
WHERE request > 0
GROUP BY type
ORDER BY wait_count DESC;

Related Scripts

• Locked Objects Detail - Comprehensive locked object analysis
• Blocking Locks - Blocking session identification
• Session Activity - Active session analysis
• Lock Statistics - System-wide lock statistics