Blocking sessions

find blocking sessions

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second 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.

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

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

 Blocking sessions with all available information

The next query prints a few more information, it let's 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 l2.id2 = l2.id2 ;

Identifying blocked objects

The view v$lock we've already used in the queries above exposes even more information. There are differnet kind of locks.
If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you'll never have to wait forever.
The following queries shows you all the TM locks: 

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

SID	ID1
92	20127
51	20127

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

SELECT object_name FROM dba_objects WHERE object_id=20127

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

How to find blocking session and kill the session from database

During adpatch ,deadlock will happen when some jobs depends on resource where another job its holding the resource.Using adctrl ,you can find two jobs in running state there will be no log updates.

To find blocking session jobs below query will useful. It will return two rows. select process,sid, blocking_session from v$session where blocking_session is not null;
E.g.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365

Second step to find the serial number for the Blocking Session to kill select SERIAL# from v$session where SID=<SID number>
E.g.
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130


Final step to kill the blocking session alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session ’365,130′;
System altered.