Find blocking sessions

Blocking sessions occur when one session holds an exclusive lock on an object and doesn't release it before another session wants to update the same data. This will block the second session until the first one has done its work.

From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You'll often have to identify these sessions in order to improve your application to avoid as many blocking sessions as possible.

Recipe #1 - Find blocking sessions with v$session

SELECT s.blocking_session, s.sid, s.serial#, s.seconds_in_wait
FROM v$session s
WHERE blocking_session IS NOT NULL;

Recipe #2 - Find blocking sessions using v$lock

SELECT l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM v$lock l1, v$lock l2
WHERE l1.block = 1
  AND l2.request > 0
  AND l1.id1 = l2.id1
  AND l1.id2 = l2.id2;

Recipe #3 - Blocking sessions with all available information

The next query prints a few more details, it lets you quickly see who's blocking who. Run this query and you can immediately call the colleague who's locking your table:

SELECT s1.username || '@' || s1.machine
       || ' ( SID=' || s1.sid || ' ) is blocking '
       || s2.username || '@' || s2.machine
       || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid = l1.sid
  AND s2.sid = l2.sid
  AND l1.BLOCK = 1
  AND l2.request > 0
  AND l1.id1 = l2.id1
  AND l1.id2 = l2.id2;

The view v$lock we've already used in the queries above exposes even more information. There are different kinds of locks — check the Oracle documentation for a complete list.

If you encounter a TM lock it means that two sessions are trying to modify some data but blocking each other. Unless one session finishes (commit or rollback), you'll have to wait.

The following query shows you all the TM locks:

SELECT sid, id1 FROM v$lock WHERE TYPE = 'TM';

The ID you get from this query refers to the actual database object which can help you to identify the problem:

SELECT object_name FROM dba_objects WHERE object_id = 20127;

Recipe #4 - Full blocking session tree with SQL details (RAC)

This comprehensive query works on Oracle RAC environments and builds a full blocking tree, showing the blocking hierarchy, the objects being waited on, and both the current and previous SQL statements:

WITH allsessions AS
(SELECT /*+ MATERIALIZE */
        distinct sid || '@' || inst_id as sid, username,
                 blocking_session || decode (blocking_session,null,null, '@' || blocking_instance) as blocking_session,
                 seconds_in_wait, event, row_wait_obj#, sql_id, prev_sql_id
   FROM gv$session
),
mysessions as
(SELECT /*+ MATERIALIZE */ LEVEL as MyLevel, a.*
   FROM allsessions a
  WHERE sid in (select blocking_session FROM allsessions) or blocking_session is not null
CONNECT BY PRIOR sid = blocking_session
   START WITH blocking_session IS NULL
),
mysql AS
(SELECT /*+ MATERIALIZE */
        distinct sql_id, substr(regexp_replace(sql_text, '( [[:space:]]+)|([[:cntrl:]])',' '),1,300) as sql_text
   FROM gv$sqlarea
  WHERE sql_id in (SELECT sql_id FROM mysessions
                    UNION ALL
                   SELECT prev_sql_id FROM mysessions)
),
myobjects AS
(SELECT /*+ MATERIALIZE */ object_id, owner ||'.'||object_name || '('|| object_type ||')' as "OBJECT"
   FROM dba_objects
  WHERE object_id in (SELECT distinct row_wait_obj# FROM mysessions)
)
SELECT to_char(s.myLEVEL) as "Level",
       s.sid || ': ' || s.username "Session",
       s.blocking_session "Blocked by",
       o."OBJECT" as "Blocking Object",
       s.seconds_in_wait "Sec Waiting",
       s.event,
       s.sql_id as "current SQL_ID",
       s1.sql_text as "current SQL",
       s.prev_sql_id "previous SQL_ID",
       s2.sql_text as "previous SQL"
FROM mysessions s left outer join myobjects o on (s.row_wait_obj# = o.object_id )
                   left outer join mysql s1 on (s.sql_id = s1.sql_id)
                   left outer join mysql s2 on (s.prev_sql_id = s2.sql_id)
order by 1;

These queries should help you to identify the cause of your blocking sessions!