Find 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.


Recipe #1 - find blocking sessions with v$session

   v$session s
   blocking_session IS NOT NULL

Recipe #2 - find blocking sessions using v$lock

   l1.sid || ' is blocking ' || l2.sid blocking_sessions
   v$lock l1, v$lock l2
   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 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 l1.id2 = l2.id2

Recipe #4 - 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 - check this site for a complete list:

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'
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!



5 years ago

same content in this page is also available at :

Leave a Comment

Please sign-in to post a comment