How to identify blocked and blocking sessions in Oracle ?

Problem:

You’d like to identify the blocking and the blocked sessions in your database.

Solution

When we see an enqueue wait event in an Oracle database, the chances are that there is some thing locking or holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that is held by a different session. We can issue the following command to view information about the blocked and the blocking sessions in ORacle.

Example

SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess,
  id1,
  id2,
  lmode,
  request,
  type
FROM v$lock
WHERE (id1, id2, type) IN
  (SELECT id1, id2, type FROM v$lock WHERE request > 0
  )
ORDER BY id1,
  request;

The V$LOCK view shows if there are any blocking locks in the instance. If there are blocking locks, it also shows the blocking session(s) and the blocked session(s).

A blocking session can block multiple sessions simultaneously, if all of them are wanting to use the same object that is being blocked.

You can use below SQL to fetch the information.

Example

select sid,type,lmode,request,ctime,block from v$lock;

Output

   SID           TY           LMODE             REQUEST       CTIME        BLOCK
--------------     --------     -----------    -----------   --------     -------    
       140           TX              4             6          11655          0
        38           TM              3             0            826          0
        38           TX              6             0            826          1

The key column to watch is the BLOCK column which will have the value 1 for the blocking session. In our example, session 38 is the blocking session, because it shows the value 1 under the BLOCK column. The blocking session, with a SID of 38, also shows a lock mode 6 under the LMODE column which mean it is holding this lock in the exclusive mode. Therefore session 140 is hanging for the same reson and unable to perform its update operation. The blocked session shows a value of 0 in the BLOCK column.

If you want to find out the wait class and for how long a blocking session has been blocking others, we can do so by querying the V$SESSION view.

Example

SELECT blocking_session,
  sid,
  wait_class,
  seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;

Output

BLOCKING_SESSION        SID           WAIT_CLASS        SECONDS_IN_WAIT
-----------------    --------       -------------    ------------------- 
       38                140          Application                  1237