22 March 2007

How to Find Blocking Locks and Influence People

An oft-asked question of any DBA is "wtf is my query doing?" To translate this into a more civil tongue: "My DML statement seems to be taking extraordinarily long. Might you be a gentleman and obtain the reasons why?"

More often than not it will be because one or more of the objects they are trying to access has a blocking lock on it from another user session. This is usually the result of an uncommitted transaction, which can be a sign of poor application design. Transactions should be committed or rolled back when they are done. Sometimes this is not done, as when an error occurs in one of the statements in the transaction. In those cases, the individual statement will roll back, but the transaction will remain uncommitted. Applications need to catch this and (probably) roll back the transaction as well.

But back to the original task of detecting a blocking lock! There is a great OraFAQ article on the subject. I encourage you to read that, and I'll just cheat and post the meaty query they use to get the goods:
SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l1.id2 = l2.id2 ;


BLOCKING_STATUS
----------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.
As you can see, this will tell you the user/host of all blocking locks, and the user/host of who they are blocking. Now you can venture forth to see what the blocking session is doing and begin the process of fixing some bad transactional application code. Or you can just kill it.

1 comment:

  1. LOL... "Or you can just kill it." 4:30pm on a Friday and you get the phone call, I think this would be very tempting... :)

    ReplyDelete