Lock Watching – What is Wrong with this SQL Statement?

June 3, 2010

I came across an interesting SQL statement that is described as identifying blocking transactions:

select s1.username blkg_user, s1.machine blkg_ws, s1.sid blkg_sid,
       s2.username wait_user, s2.machine wait_ws, s2.sid wait_sid,
       lo.object_id blkd_obj_id, do.owner, do.object_name
from v$lock l1, v$session s1, v$lock l2, v$session s2,
     v$locked_object lo, dba_objects do
where s1.sid = l1.sid
  and s2.sid = l2.sid
  and l1.id1 = l2.id1
  and s1.sid = lo.session_id
  and lo.object_id = do.object_id
  and l1.block = 1
  and l2.request > 0;

The SQL statement is a bit different from the one that I typically use for determining enqueues.  The documentation also includes a SQL statement for determining enqueues.

What, if anything, is wrong with the above SQL statement?  If you need a test case, try the one found in this article.  I suspect that there may be more than one answer.