Hunting Locks in Oracle: Finding Blockers, Waiters, and the SQL Behind Them

  • November 7, 2025
  • Posted by: Onsys
  • Category: Database
No Comments

When a production system “freezes” and users complain about timeouts, the usual culprit is blocking locks. This post is a practical playbook for Oracle DBAs to identify who’s blocking whom, what’s locked, and exactly which SQL is responsible—plus some prevention tips. All examples work on Oracle 19c+ (notes for RAC included).

1) Read the room: who’s waiting on whom (right now)

Start with a clean waiter→blocker view.

-- Waiters joined to their blockers
SELECT  w.sid   AS waiting_sid,
        w.serial# AS waiting_serial,
        w.username AS waiting_user,
        w.event, w.seconds_in_wait,
        b.sid   AS blocking_sid,
        b.serial# AS blocking_serial,
        b.username AS blocking_user,
        b.machine AS blocking_machine,
        b.program AS blocking_program
FROM   v$session w
JOIN   v$session b ON b.sid = w.blocking_session
ORDER  BY w.seconds_in_wait DESC;

A quick snapshot for managers: COUNT(*) of rows here is “how many sessions are currently blocked.”

2) Blocking tree (visualize the pile-up)

SELECT LPAD(' ', LEVEL*2)||s.sid AS sid_tree,
       s.serial#, s.username, s.machine, s.program,
       s.event, s.seconds_in_wait
FROM   v$session s
START  WITH s.blocking_session IS NULL
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER  SIBLINGS BY s.sid;

This reveals the root blocker at the top and the chain beneath it.

3) What exactly is locked (object + mode)

Most incidents boil down to two enqueue types: TX (row locks) and TM (table locks).

SELECT o.owner, o.object_name, o.object_type,
       s.sid, s.serial#, s.username,
       DECODE(l.type,'TX','TX (row)','TM','TM (table)',l.type) AS lock_type,
       DECODE(l.lmode,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',
                     4,'Share',5,'S/Row-X (SSX)',6,'Exclusive', TO_CHAR(l.lmode)) AS mode_held,
       DECODE(l.request,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',
                     4,'Share',5,'S/Row-X (SSX)',6,'Exclusive', TO_CHAR(l.request)) AS mode_req
FROM   v$lock l
JOIN   v$session s       ON s.sid = l.sid
LEFT   JOIN v$locked_object lo ON lo.session_id = s.sid
LEFT   JOIN dba_objects o      ON o.object_id = lo.object_id
WHERE  l.block = 1 OR l.request > 0
ORDER  BY mode_req DESC, mode_held DESC;

Hints

  • Many waiters on a single TM? Check for unindexed foreign keys on child tables.
  • TX waits + long seconds_in_wait often mean long-running transactions (missing commits).

4) Pinpoint the row/file/block (deep dive)

SELECT s.sid, s.serial#, s.username,
       o.owner, o.object_name,
       s.row_wait_file#, s.row_wait_block#, s.row_wait_row#,
       s.event, s.wait_class, s.seconds_in_wait
FROM   v$session s
LEFT   JOIN dba_objects o ON o.object_id = s.row_wait_obj#
WHERE  s.state = 'WAITING'
  AND  s.wait_class = 'Application';

Use the file/block/row info with DBA_EXTENTS if you need to map to a specific segment extent.

5) Get the SQL text behind a SID (current or previous)

Sessions may show current or previous SQL. Grab whichever exists.

-- Inspect the session
SELECT s.sid, s.serial#, s.status, s.username,
       s.sql_id, s.sql_child_number,
       s.prev_sql_id, s.prev_child_number,
       s.event, s.seconds_in_wait
FROM   v$session s
WHERE  s.sid = :sid;

-- Fetch SQL text (use sql_id if present; else prev_sql_id)
SELECT q.sql_id, q.child_number, q.sql_fulltext
FROM   v$sql q
WHERE  q.sql_id = :sql_id
ORDER  BY q.child_number;

One-shot join that “just works”:

SELECT s.sid, s.serial#, s.username, s.status,
       COALESCE(s.sql_id, s.prev_sql_id) AS sql_id,
       COALESCE(s.sql_child_number, s.prev_child_number) AS child_no,
       q.sql_text
FROM   v$session s
LEFT   JOIN v$sql q
       ON q.sql_id = COALESCE(s.sql_id, s.prev_sql_id)
WHERE  s.sid = :sid;

Tip: If the blocker is “idle in transaction,” the statement that created the lock is usually under prev_sql_id.

6) Is it still running? Check runtime & plan

-- Live execution monitor for that SID
SELECT sid, sql_id, status, px_server#, elapsed_time, sql_exec_start
FROM   v$sql_monitor
WHERE  sid = :sid
ORDER  BY sql_exec_start DESC;

-- Text plan with actuals (if cached)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id, :child_no, 'ALLSTATS LAST'));

7) RAC-aware views

-- Sessions and blockers across all instances
SELECT inst_id, sid, serial#, username,
       blocking_instance, blocking_session
FROM   gv$session
WHERE  blocking_session IS NOT NULL
ORDER  BY inst_id, sid;

-- SQL text from the right instance
SELECT inst_id, sql_id, child_number, sql_fulltext
FROM   gv$sql
WHERE  sql_id = :sql_id
ORDER  BY child_number;

Prefer asking the app/user to COMMIT/ROLLBACK. If not you can kill the session.

— Single instance
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

— RAC (note the @inst_id)
ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’ IMMEDIATE;

— Or a clean disconnect (19c+)
ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;

8) Quick dictionary shortcuts

SELECT * FROM dba_blockers;
SELECT * FROM dba_waiters;

These are great for lightweight dashboards or alerts.

9) Look back in time (ASH/AWR)

When the incident is over but you need a postmortem:

-- In-memory ASH (recent)
SELECT sample_time, session_id AS sid, session_serial# AS serial#,
       event, blocking_session
FROM   v$active_session_history
WHERE  blocking_session IS NOT NULL
ORDER  BY sample_time DESC
FETCH FIRST 100 ROWS ONLY;

-- AWR history (licensed)
SELECT sample_time, session_id, session_serial#, event, blocking_session
FROM   dba_hist_active_sess_history
WHERE  blocking_session IS NOT NULL
ORDER  BY sample_time DESC
FETCH FIRST 100 ROWS ONLY;

10) On-call playbook (triage steps)

  1. Confirm impact
    Count waiters; capture top blocker and affected objects.
  2. Identify the blocking SQL
    Pull sql_id/prev_sql_id; check v$sql_monitor and plan.
  3. Decide the fix path
    • If it’s a long transaction: get the user/app to COMMIT/ROLLBACK.
    • If it’s schema-wide TM waits: verify FK indexes and recent DDL.
    • If a batch job went rogue: stop job, rollback, or gently kill session.
  4. Escalate carefully
    Killing sessions is last resort. If you must: ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; Prefer DISCONNECT over KILL in 19c+ for cleaner tear-down.
  5. Document & prevent
    Capture SQL, plan, and blocking tree for the postmortem.

11) Common root causes & prevention

  • Unindexed foreign keys → Add supporting indexes on child tables to avoid table-level TM contention.
  • Long transactions / “idle in transaction” → Shorten units of work; enforce commit discipline; watch connection pools.
  • Hot rows / serialization → Reduce contention with hash partitioning, sequence caching, or optimistic logic.
  • DDL in business hours → Schedule DDL off-peak; use editioning patterns where possible.
  • Overzealous explicit locks (LOCK TABLE … IN EXCLUSIVE MODE) → Replace with row-level logic; review isolation needs.
  • Triggers or batch merges touching wide swaths → Break work into smaller batches; ensure selective predicates.

12) Drop-in diagnostic script (paste & run)

-- :sid required
VAR sid NUMBER;
EXEC :sid := &sid;

-- 1) Who is blocking this session (if any)
SELECT s.sid, s.serial#, s.username, s.status,
       s.blocking_session AS blocker_sid, s.event, s.seconds_in_wait
FROM   v$session s
WHERE  s.sid = :sid;

-- 2) SQL text (current or previous)
SELECT COALESCE(s.sql_id, s.prev_sql_id) AS sql_id,
       COALESCE(s.sql_child_number, s.prev_child_number) AS child_no,
       q.sql_text
FROM   v$session s
LEFT   JOIN v$sql q ON q.sql_id = COALESCE(s.sql_id, s.prev_sql_id)
WHERE  s.sid = :sid;

-- 3) Locked objects tied to this SID
SELECT o.owner, o.object_name, o.object_type,
       DECODE(l.type,'TX','TX (row)','TM','TM (table)',l.type) AS lock_type,
       l.lmode, l.request
FROM   v$lock l
LEFT   JOIN v$locked_object lo ON lo.session_id = l.sid
LEFT   JOIN dba_objects o ON o.object_id = lo.object_id
WHERE  l.sid = :sid;

-- 4) Row-level wait location
SELECT o.owner, o.object_name,
       s.row_wait_file#, s.row_wait_block#, s.row_wait_row#,
       s.event, s.wait_class, s.seconds_in_wait
FROM   v$session s
LEFT   JOIN dba_objects o ON o.object_id = s.row_wait_obj#
WHERE  s.sid = :sid;

Final thoughts

Lock storms don’t have to be chaotic. With the views above, you can identify blockers in seconds, pull the exact SQL (even from “idle in transaction” sessions), and trace the locked objects down to row level. Pair these with basic hygiene—index your FKs, keep transactions short, and push DDL off-peak—and you’ll see far fewer firefights.