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