A blocking session holds a lock that other sessions need, so they stop and wait until it commits or rolls back. Often the blocker is not slow at all: it is an open transaction someone forgot, an application waiting for user input, or a session stuck elsewhere. Blocks can also form chains, where a waiting session itself blocks others, and only the session at the root can release them. This guide builds a chain of three sessions and finds the blockers, the root of the chain, the locks, and the exact row being waited for.
What You Need
The queries use V$SESSION, V$LOCK, V$SQL, and V$TRANSACTION, which need the SELECT_CATALOG_ROLE role or SELECT privileges on the views.
Build a Blocking Chain
Example:
create table demo_seats (seat varchar2(4) primary key, passenger varchar2(30));
insert into demo_seats values ('12A', null);
insert into demo_seats values ('12B', null);
commit;Output:
Table DEMO_SEATS created. 1 row inserted. 1 row inserted. Commit complete.
Three sessions start a few seconds apart. Session 1 locks seat 12A and leaves its transaction open:
Example:
-- session 1: locks seat 12A, then leaves the transaction open
set serveroutput off
exec dbms_application_info.set_module('DEMO_SESSION_1', null)
update demo_seats set passenger = 'Ana' where seat = '12A';
exec dbms_session.sleep(50)
rollback;Session 2 locks seat 12B, then waits for 12A:
Example:
-- session 2: locks seat 12B, then needs 12A
exec dbms_application_info.set_module('DEMO_SESSION_2', null)
update demo_seats set passenger = 'Leo' where seat = '12B';
update demo_seats set passenger = 'Leo' where seat = '12A';
rollback;Session 3 waits for 12B, which session 2 holds:
Example:
-- session 3: needs seat 12B
exec dbms_application_info.set_module('DEMO_SESSION_3', null)
update demo_seats set passenger = 'Mia' where seat = '12B';
rollback;Find Blocked Sessions and Their Blockers
From a fourth session:
Example:
select sid, module, blocking_session as blocker, final_blocking_session as final_blocker,
event, round(wait_time_micro / 1e6) as seconds
from v$session
where module like 'DEMO_SESSION_%'
order by module;Output:
SID MODULE BLOCKER FINAL_BLOCKER EVENT SECONDS
______ _________________ __________ ________________ ________________________________ __________
221 DEMO_SESSION_1 PL/SQL lock timer 18
60 DEMO_SESSION_2 221 221 enq: TX - row lock contention 13
70 DEMO_SESSION_3 60 221 enq: TX - row lock contention 8- BLOCKING_SESSION is the session directly in the way: session 3 waits for session 2, and session 2 for session 1.
- FINAL_BLOCKING_SESSION is the root of the chain: SID 221 for both waiters. Releasing it resolves the whole chain.
- The root blocker itself is not waiting for any lock; here it sleeps, in a real system it is often idle on SQL*Net message from client.
Show the Chain as a Tree
A hierarchical query starting from sessions that block others but are not blocked themselves:
Example:
-- the blocking chain as a tree, from the root blocker down
select lpad(' ', 2 * (level - 1)) || sid as sid, module, event
from v$session
where module like 'DEMO_SESSION_%'
start with blocking_session is null
and sid in (select blocking_session from v$session
where blocking_session is not null)
connect by prior sid = blocking_session;Output:
SID MODULE EVENT
_________ _________________ ________________________________
221 DEMO_SESSION_1 PL/SQL lock timer
60 DEMO_SESSION_2 enq: TX - row lock contention
70 DEMO_SESSION_3 enq: TX - row lock contentionThe Locks Behind the Chain
Example:
select l.sid, s.module, l.type, l.lmode, l.request, l.block
from v$lock l join v$session s on s.sid = l.sid
where s.module like 'DEMO_SESSION_%'
and l.type in ('TX', 'TM')
order by s.module, l.type;Output:
SID MODULE TYPE LMODE REQUEST BLOCK
______ _________________ _______ ________ __________ ________
221 DEMO_SESSION_1 TM 3 0 0
221 DEMO_SESSION_1 TX 6 0 1
60 DEMO_SESSION_2 TM 3 0 0
60 DEMO_SESSION_2 TX 0 6 0
60 DEMO_SESSION_2 TX 6 0 1
70 DEMO_SESSION_3 TM 3 0 0
70 DEMO_SESSION_3 TX 0 6 0
7 rows selected.- TX is a transaction lock, held in exclusive mode 6 by each session that changed rows. BLOCK = 1 marks a lock that others are waiting for.
- A row with LMODE 0 and REQUEST 6 is a session waiting for another transaction's TX lock: session 2 waits while holding its own TX lock, which blocks session 3.
- TM in mode 3 is the table-level lock every DML statement takes; it does not block other DML.
Details of the Root Blocker
Example:
-- the root blocker: what it ran last, and since when its transaction is open
select s.sid, s.serial#, s.username, s.status, s.event,
substr(q.sql_text, 1, 50) as last_sql, t.start_time as transaction_start
from v$session s
left join v$sql q on q.sql_id = s.prev_sql_id and q.child_number = s.prev_child_number
left join v$transaction t on t.addr = s.taddr
where s.module = 'DEMO_SESSION_1';Output:
SID SERIAL# USERNAME STATUS EVENT LAST_SQL TRANSACTION_START ______ __________ ___________ _________ ____________________ _____________________________________________________ ____________________ 221 27573 NIMBUS ACTIVE PL/SQL lock timer update demo_seats set passenger = 'Ana' where seat 10/12/26 04:33:50
PREV_SQL_ID shows the statement the session ran before its current one, here the UPDATE that took the lock, and V$TRANSACTION shows when the transaction began. The previous statement is not always the locking one, since the session may have run others after it, but the transaction start time tells how long the lock has been held.
The Row Being Waited For
The ROW_WAIT columns of a waiting session identify the locked row, and DBMS_ROWID builds its ROWID:
Example:
-- the row session 3 is waiting for
select s.sid, o.object_name, p.seat, p.passenger
from v$session s
join user_objects o on o.object_id = s.row_wait_obj#
join demo_seats p
on p.rowid = dbms_rowid.rowid_create(1, s.row_wait_obj#, s.row_wait_file#,
s.row_wait_block#, s.row_wait_row#)
where s.module = 'DEMO_SESSION_3';Output:
SID OBJECT_NAME SEAT PASSENGER
______ ______________ _______ ____________
70 DEMO_SEATS 12BSession 3 is waiting for seat 12B in DEMO_SEATS, the row session 2 updated.
Resolve the Block
The right fix is for the root blocker to commit or roll back: find the user or program behind it with the columns USERNAME, MACHINE, PROGRAM, and MODULE of V$SESSION. If that is not possible, a DBA can end the session, which rolls back its transaction:
Syntax:
alter system kill session 'sid,serial#' immediate;
This needs the ALTER SYSTEM privilege. All three sessions in this example finished on their own: when session 1 rolled back after its sleep, session 2 got 12A and rolled back, and session 3 then got 12B.
Things to Know
- Most blocking comes from transactions left open: commit promptly, and never wait for user input inside a transaction.
- SELECT ... FOR UPDATE NOWAIT or WAIT n lets an application fail fast instead of waiting indefinitely.
- Two sessions that block each other form a deadlock, which Oracle resolves itself with ORA-00060; chains like this one are not deadlocks and wait until the root releases.
Related Guides
Conclusion
V$SESSION shows each blocked session's BLOCKING_SESSION and, for chains, the FINAL_BLOCKING_SESSION at the root. Check the locks in V$LOCK, the root blocker's previous statement and transaction start, and the row being waited for, then have the root blocker commit or roll back.
