V$LOCK - Find Blocking Locks, Deadlocks & Lock Wait Analysis

V$LOCK

Overview

V$LOCK contains one row for every enqueue lock currently held or requested in the instance. An enqueue is Oracle’s internal lock mechanism protecting shared resources — database objects, transactions, and internal structures. This view is the primary tool for identifying blocking lock situations, diagnosing deadlocks, and understanding what resources active transactions hold. Every lock contention incident starts here.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$LOCK or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SID	NUMBER	Session ID holding or requesting this lock
TYPE	VARCHAR2(2)	Lock type code (TX = transaction, TM = DML enqueue, UL = user-defined, etc.)
ID1	NUMBER	Lock identifier 1 — meaning depends on TYPE (for TX: USN * 65536 + SLOT; for TM: object ID)
ID2	NUMBER	Lock identifier 2 — meaning depends on TYPE (for TX: sequence number; for TM: 0)
LMODE	NUMBER	Lock mode currently held (0–6, see decode table below)
REQUEST	NUMBER	Lock mode being requested (0 if not waiting, otherwise 1–6)
CTIME	NUMBER	Seconds since the current lock mode was granted (if held) or since the request began (if waiting)
BLOCK	NUMBER	1 if this session is blocking another session, 0 otherwise

Lock Mode Decode

Code	Name	Description
0	None	No lock
1	Null (N)	Informational lock, no conflict with any mode
2	Row Share (RS)	Sub-share; SELECT FOR UPDATE, LOCK TABLE IN ROW SHARE
3	Row Exclusive (RX)	Sub-exclusive; INSERT, UPDATE, DELETE, MERGE
4	Share (S)	LOCK TABLE IN SHARE MODE
5	Share Row Exclusive (SRX)	LOCK TABLE IN SHARE ROW EXCLUSIVE
6	Exclusive (X)	Exclusive; DDL, LOCK TABLE IN EXCLUSIVE MODE

Essential Queries

Basic Usage

-- All locks currently held or requested (non-idle)
SELECT
    l.sid,
    l.type,
    l.id1,
    l.id2,
    DECODE(l.lmode,
        0, 'None',
        1, 'Null',
        2, 'Row-Share',
        3, 'Row-Exclusive',
        4, 'Share',
        5, 'Share/Row-X',
        6, 'Exclusive',
           'Unknown-' || l.lmode
    )                                               AS lock_mode_held,
    DECODE(l.request,
        0, '-',
        1, 'Null',
        2, 'Row-Share',
        3, 'Row-Exclusive',
        4, 'Share',
        5, 'Share/Row-X',
        6, 'Exclusive',
           'Unknown-' || l.request
    )                                               AS lock_mode_requested,
    l.ctime                                         AS secs_held_or_waiting,
    l.block                                         AS blocking_others
FROM
    v$lock l
WHERE
    l.lmode != 0
    OR l.request != 0
ORDER BY
    l.block DESC,
    l.ctime DESC;

Monitoring Query

-- Blocking lock report: who is blocking whom, with object and SQL details
SELECT
    blocker.sid                                     AS blocker_sid,
    bs.username                                     AS blocker_user,
    bs.status                                       AS blocker_status,
    bs.sql_id                                       AS blocker_sql_id,
    bs.module                                       AS blocker_module,
    waiter.sid                                      AS waiter_sid,
    ws.username                                     AS waiter_user,
    ws.status                                       AS waiter_status,
    ws.sql_id                                       AS waiter_sql_id,
    ws.seconds_in_wait                              AS waiter_secs,
    blocker.type                                    AS lock_type,
    DECODE(blocker.lmode,
        2, 'Row-Share',    3, 'Row-Exclusive',
        4, 'Share',        5, 'Share/Row-X',
        6, 'Exclusive',    TO_CHAR(blocker.lmode)
    )                                               AS held_mode,
    DECODE(waiter.request,
        2, 'Row-Share',    3, 'Row-Exclusive',
        4, 'Share',        5, 'Share/Row-X',
        6, 'Exclusive',    TO_CHAR(waiter.request)
    )                                               AS requested_mode,
    blocker.ctime                                   AS lock_held_secs,
    obj.object_name,
    obj.object_type,
    obj.owner
FROM
    v$lock     blocker
    JOIN v$lock    waiter  ON waiter.id1     = blocker.id1
                          AND waiter.id2     = blocker.id2
                          AND waiter.type    = blocker.type
                          AND waiter.request != 0
                          AND blocker.lmode  != 0
    JOIN v$session bs      ON bs.sid         = blocker.sid
    JOIN v$session ws      ON ws.sid         = waiter.sid
    LEFT JOIN dba_objects obj ON obj.object_id = blocker.id1
                              AND blocker.type = 'TM'
WHERE
    blocker.block = 1
ORDER BY
    lock_held_secs DESC;

Combined with Other Views

-- TX lock analysis: map transaction locks to undo segment details
SELECT
    l.sid,
    s.username,
    s.status,
    s.machine,
    s.module,
    TRUNC(l.id1 / 65536)                           AS undo_segment_no,
    MOD(l.id1, 65536)                              AS transaction_slot,
    l.id2                                          AS sequence_no,
    DECODE(l.lmode,
        6, 'Exclusive TX (writing)',
        3, 'Row-Exclusive (uncommitted DML)',
           'Mode-' || l.lmode
    )                                               AS lock_meaning,
    l.ctime                                        AS secs_held,
    l.block                                        AS blocking_others,
    s.sql_id,
    s.prev_sql_id
FROM
    v$lock l
    JOIN v$session s ON s.sid = l.sid
WHERE
    l.type  = 'TX'
    AND l.lmode != 0
ORDER BY
    l.block DESC,
    l.ctime DESC;

Advanced Analysis

-- TM (DML enqueue) lock analysis: which tables have the most contention
SELECT
    l.sid,
    s.username,
    s.status,
    DECODE(l.lmode,
        2, 'Row-Share',   3, 'Row-Exclusive',
        4, 'Share',       5, 'Share/Row-X',
        6, 'Exclusive',   'Mode-' || l.lmode
    )                                               AS lock_mode,
    DECODE(l.request,
        0, '-',
        2, 'Row-Share',   3, 'Row-Exclusive',
        4, 'Share',       5, 'Share/Row-X',
        6, 'Exclusive',   'Mode-' || l.request
    )                                               AS requested_mode,
    obj.owner,
    obj.object_name,
    obj.object_type,
    l.ctime                                        AS secs_in_state,
    l.block                                        AS blocking_others,
    s.sql_id,
    s.module
FROM
    v$lock l
    JOIN v$session  s   ON s.sid        = l.sid
    JOIN dba_objects obj ON obj.object_id = l.id1
WHERE
    l.type = 'TM'
ORDER BY
    l.block DESC,
    l.ctime DESC;

-- Deadlock candidate detection: sessions mutually waiting for locks
WITH lock_holders AS (
    SELECT l.sid, l.type, l.id1, l.id2, l.lmode, l.request
    FROM   v$lock l
    WHERE  l.lmode != 0
),
lock_waiters AS (
    SELECT l.sid, l.type, l.id1, l.id2, l.lmode, l.request
    FROM   v$lock l
    WHERE  l.request != 0
)
SELECT
    w.sid                                           AS waiter_sid,
    ws.username                                    AS waiter_user,
    h.sid                                           AS holder_sid,
    hs.username                                    AS holder_user,
    w.type                                          AS lock_type,
    w.id1,
    w.id2,
    DECODE(w.request,
        2, 'Row-Share',  3, 'Row-Exclusive',
        4, 'Share',      5, 'Share/Row-X',
        6, 'Exclusive',  'Mode-' || w.request
    )                                               AS mode_requested,
    ws.seconds_in_wait,
    ws.sql_id                                       AS waiter_sql
FROM
    lock_waiters w
    JOIN lock_holders h  ON h.type = w.type
                        AND h.id1  = w.id1
                        AND h.id2  = w.id2
                        AND h.sid  != w.sid
    JOIN v$session ws    ON ws.sid = w.sid
    JOIN v$session hs    ON hs.sid = h.sid
ORDER BY
    ws.seconds_in_wait DESC;

-- Lock wait duration histogram: identify how long sessions have been waiting
SELECT
    CASE
        WHEN ctime < 5    THEN '< 5 seconds'
        WHEN ctime < 30   THEN '5-30 seconds'
        WHEN ctime < 120  THEN '30s-2 minutes'
        WHEN ctime < 600  THEN '2-10 minutes'
        ELSE                   '> 10 minutes'
    END                                             AS wait_duration_bucket,
    type                                            AS lock_type,
    COUNT(*)                                        AS waiting_sessions
FROM
    v$lock
WHERE
    request != 0
GROUP BY
    CASE
        WHEN ctime < 5    THEN '< 5 seconds'
        WHEN ctime < 30   THEN '5-30 seconds'
        WHEN ctime < 120  THEN '30s-2 minutes'
        WHEN ctime < 600  THEN '2-10 minutes'
        ELSE                   '> 10 minutes'
    END,
    type
ORDER BY
    MIN(ctime);

Common Use Cases

• Blocking lock identification — The block = 1 filter is the fastest way to find sessions currently blocking others. Pair with V$SESSION for user and SQL details.
• Transaction lock (TX) analysis — TX locks with LMODE = 6 represent uncommitted DML. The ID1 field encodes undo segment number and transaction slot, linking to V$TRANSACTION.
• DDL lock contention — LMODE = 6 (Exclusive) on a TM lock indicates a DDL operation (ALTER TABLE, TRUNCATE) is holding or waiting for exclusive table access.
• Deadlock investigation — After an ORA-00060, cross-reference the trace file deadlock graph with V$LOCK to understand the circular dependency that caused it.
• Long-running transaction detection — TX lock CTIME growing indefinitely indicates an open transaction that may be blocking others, even if the session appears idle in V$SESSION.
• Lock type profiling — Beyond TX and TM, types like CF (control file), DR (distributed recovery), and DX (distributed transaction) indicate specific infrastructure contention scenarios.

Related Views

• V$LOCKED_OBJECT — Higher-level view showing which database objects are locked by active transactions
• V$SESSION — Join on SID for username, program, machine, status, and current SQL of the locking session
• V$TRANSACTION — Join TX lock ID1/ID2 to V$TRANSACTION for full transaction details including undo usage
• V$ACTIVE_SESSION_HISTORY — ASH captures BLOCKING_SESSION history, showing historical lock patterns even after locks are released
• DBA_BLOCKERS / DBA_WAITERS — Pre-built Oracle views that internally query V$LOCK to summarize blocking scenarios

Version Notes

• Oracle 7/8: View has existed since early Oracle versions. TX and TM lock types have remained consistent across all releases.
• Oracle 10g: Lock monitoring became more integrated with ASH — blocking session information is now captured in V$ACTIVE_SESSION_HISTORY.BLOCKING_SESSION.
• Oracle 11g: No major structural changes. DBA_BLOCKERS and DBA_WAITERS views provide simpler summarized access to blocking data.
• Oracle 12c: CON_ID column added for multitenant environments. In a CDB, V$LOCK shows locks for the current container only; CDB$ROOT shows all containers.
• Oracle 19c: Automatic indexing operations appear as lock holders (DDL-type TM locks) during index creation; V$LOCK is the way to identify them.
• Oracle 21c / 23ai: In 23ai, Blockchain Table operations and JSON Relational Duality write paths may generate TM locks visible here. The lock type encoding (ID1/ID2 semantics) remains unchanged from prior releases.