Problem – 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.
Recipie #1 – find blocking sessions with v$session
Recipie #2 – find blocking sessions using v$lock
Recipie #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 l2.id2 = l2.id2 ;
Recipie #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: http://download.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1123.htm#sthref3198
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:
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: