All About Blocking Locks in Oracle

A  very  nice  post  has  been  written  by  Oracle  Ace Director  Arup Nanda  about the  Blocking  Locks . It  is  so simple  and  brilliantly explained . Check  the  below  link  and  enjoy  reading  about  basic  concepts behind locks . Before going to this link , read my previous post on ITL ( Interested Transaction List)  for better understading of this post .

http://arup.blogspot.in/2011/01/how-oracle-locking-works.html

Another  Brilliant  demo  on  Blocking  lock is  explained  by  "Natalka Roshak" .  She  has so wonderfully  explained .  Enjoy reading this link too. 

http://www.orafaq.com/node/854

Here,  i  have  added  few  more query on  locks along  with  it's output . For  demo purpose , i  have created  a  table "blck_tab"  and  locked this  table manually  and accessing this locked table from other sessions .Let's have a look .

SQL> create table blck_tab (id number , name varchar(22));
Table created.

SQL> insert into blck_tab values(1,'abc');
1 row created.

SQL> insert into blck_tab values(2,'xyz');
1 row created.

SQL> insert into blck_tab values(3,'pqr');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from blck_tab  for update ;             ----- Table Locked
        ID               NAME
----------    ----------------------
         1                   abc
         2                   xyz
         3                   pqr

Session 1 : 

SQL>conn test1/test1
SQL> update test.blck_tab set name='qwert' where id=3;
--->>  Waiter   <---- 

Session 2 : 

SQL> conn hr/hr
SQL> delete test.blck_tab where id=1;
--->>  Waiter   <---- 


Query to check locks :
(For Non-RAC)
SQL> SELECT  SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
  2  DECODE(REQUEST, 0, 'NO','YES' ) WAITER
  3  FROM   V$LOCK
  4  WHERE  REQUEST > 0 OR BLOCK > 0
  5  ORDER BY block DESC;
   SID     BLOCKER      WAITER
------     ------------       ----------
    37            YES                 NO
    34             NO                 YES
    31             NO                  YES
or 

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from gv$lock l1, gv$lock l2
  3  where l1.block >0 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2;
       SID    'ISBLOCKING'         SID
----------  ------------------      --------
        37      IS BLOCKING          31
        37      IS BLOCKING          34

For RAC 
SQL>select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK>0 and l2.request > 0and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

BLOCKING_STATUS

------------------------------------------------------------------------------------------------------------
TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 )  is blocking TEST1@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=34 )

TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 )  is blocking HR@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=31 )


Expanded Lock  Query
===============
SQL> SELECT vs.username,  vs.osuser,   vh.sid locking_sid,  vs.status status,
 vs.module module,   vs.program program_holding,   jrh.job_name,   vsw.username,
 vsw.osuser,   vw.sid waiter_sid,   vsw.program program_waiting,  jrw.job_name,
 'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command"
FROM  v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,
 dba_scheduler_running_jobs jrh,
 dba_scheduler_running_jobs jrw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid
 AND vh.sid = jrh.session_id(+)
 AND vw.sid = jrw.session_id(+);




Blocking locks with Sid and SQl 

SQL> set lines 200
column pu format a8 heading 'O/S|login|ID' justify left
column su format a15 heading 'Oracle/User ID' justify left
column prog format a15 heading 'Program' justify left
column machine format a15 heading 'machine' justify left
column stat format a8 heading 'Session|Status' justify left
column sser format 999999 heading 'Oracle|Serial|No' justify right
column txt format a28 word heading 'SQL TEXT' 
column RUNT format a15 word heading 'Run Time' 
set pagesize 1000
select
 s.username su,
 s.program prog,
 s.sid sid,
 lpad(p.spid,7) pid,
 substr(sa.sql_text,1,2000) txt,
 ltrim(to_char(floor(s.last_call_et/3600),'00009')) ||':'
 || ltrim(to_char(floor(mod(s.last_call_et,3600)/60),'09')) ||':'
 || ltrim(to_char(mod(s.last_call_et,60),'09')) RUNT
from v$process p,
 v$session s,
 v$sqlarea sa
where p.addr = s.paddr
 and s.username is not null
 and s.sql_address=sa.address(+)
 and s.sql_hash_value=sa.hash_value(+)
 and s.sid in (SELECT   SID FROM     V$LOCK WHERE    REQUEST > 0 OR BLOCK > 0)
order by 1,2
/

Oracle/User ID  Program           SID     PID          SQL TEXT                                  Run Time

--------------- ---------------          ------- -------   ----------------------------                ---------------

HR              sqlplus.exe             31        2812    delete test.blck_tab where         00001:32:11

                                                                                 id=1

TEST            sqlplus.exe             37    1928                                                          00000:06:35

TEST1           sqlplus.exe             34    1880       update test.blck_tab set            00002:11:34

                                                                            name='qwert' where id=3

Blocker/Waiter object details

SQL> col object_name for a28
SQL> col owner for a15
SQL> select do.owner,do.object_name , row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
  2  from
  3  v$session s,
  4  dba_objects do
  5  where sid in (select l2.sid blocking 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) and
  6  s.ROW_WAIT_OBJ# = do.OBJECT_ID;

OWNER           OBJECT_NAME    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- -------------- ------------- -------------- --------------- ------------- ------------------
TEST            BLCK_TAB        74697              4             524             2          AAASPJAAEAAAAIMAAC

TEST            BLCK_TAB        74697              4             524             0          AAASPJAAEAAAAIMAAA


Note for RAC  use  gv$ instead of v$ .


Enjoy    :-)