← Sessions & Locks
Oracle · Sessions & Locks

How to find the root blocker in a lock chain in Oracle

Walk an Oracle lock chain to the ultimate blocker at the head using FINAL_BLOCKING_SESSION.

Tested on11g12c18c19c21c23ai26ai

V$SESSION.FINAL_BLOCKING_SESSION (11.2+) points straight to the head of the lock chain — the session everyone else is ultimately waiting for. Kill this one and the chain unwinds.

SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program,
       s.blocking_session       AS immediate_blocker,
       s.final_blocking_session AS root_blocker,
       s.event, s.seconds_in_wait, s.sql_id
FROM   v$session s
WHERE  s.final_blocking_session IS NOT NULL
ORDER BY s.final_blocking_session, s.seconds_in_wait DESC;
locksblockingfinal_blocking_session