Wednesday, August 28, 2013

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. 

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    :-) 



34 comments:

Anonymous said...

It's an remarkable article for all the web viewers; they will take
benefit from it I am sure.

Take a look at my web-site :: website value

Anonymous said...

I'm not sure where you are getting your information, but great topic.
I needs to spend some time learning more or understanding more.
Thanks for magnificent info I was looking for this information for my mission.



my website: celluliteanti24

Anonymous said...

Sweet blog! I found it while browsing on Yahoo News.
Do you have any tips on how to get listed in Yahoo News?
I've been trying for a while but I never seem to
get there! Thanks

Also visit my webpage ... website valuation

Anonymous said...

Hi, I wish for to subscribe for this website to take most recent updates, so where can i do it please assist.


Review my homepage stairlifts

Anonymous said...

Incredible quest there. What occurred after? Thanks!


Visit my blog :: website worth calculator online

Anonymous said...

I was able to ascertain she was NOT allergic to tuna fish,
salmon, rice, ground chicken or turkey, a variety of various vegetables.
To prevent food from sticking, spray or grease the racks.
Alternately, you can give your wine hints of chocolate or
vanilla and other uncommon flavors.

Here is my web-site; inflatable games

Anonymous said...

Oh my goodness! Amazing article dude! Thank you so much, However I am encountering difficulties with your RSS.
I don't understand why I can't join it. Is there anybody else getting similar RSS issues?

Anyone who knows the solution can you kindly respond? Thanks!!


Here is my page: how does garcinia cambogia work

Anonymous said...

Its like you read my mind! You seem to know a lot about this,
like you wrote the book in it or something.
I think that you could do with some pics to drive
the message home a bit, but instead of that, this is wonderful blog.
A great read. I will certainly be back.

Also visit my blog - http://pharmacycatalog2014.com/

Anonymous said...

The blisters typically change into yellow-crusted
sores then go away in a month or more. Sad to say, folks
who acquire chilly sores, which are due to the particular herpes simplex virus, usually get outbreaks
over and over again, and they're highly contagious. Be sure you
employ a frosty tender The frosty aching is equivalent to some sort of fever sore, but it really
can be different then any canker tender. Although frosty sores may perhaps once
in a while develop inside the lips, they may be more compact as compared to canker sores,
and so they begin seeing that blisters. Which causes the
area truly view a cool painful, you will likely manage to think a
slight tingling or perhaps using around your mouth the place that the frosty uncomfortable will probably erupt.
People might be able to feel a smaller push or even solidity inside the pores and skin with the tingling feeling.



Natural Herpes Treatment

Anonymous said...

Hi, Neat post. There is an issue along with your site in internet explorer, would check this?
IE still is the marketplace chief and a large section of other
people will leave out your great writing because of this problem.


Here is my web page - paskola

Anonymous said...

Often we hear experts on tv that report a particular stock is planning to soar and now will be the time to purchase.
An HVAC repair clients are most often started by the
technician who's learned the trade through previous employment.
" Employees at Nike are encouraged to become curious and open to new ideas, whatever their source.

Check out my weblog - http://pytamy.pl/

Anonymous said...

He writes about his PD with a sense of humor and optimism that
readers find infectious. The good spinners are priced low so they can be utilized
by all business owners and because the companies behind them
know how hard it is to find low cost business tools that really are effective.
Spinchimp, a free article spinning software, is already out in the market today
to make your article rewriting efficient and fast.

Also visit my homepage :: article spinners

Anonymous said...

Heya! I'm at work browsing your blog from my new apple
iphone! Just wanted to say I love reading through your blog and look forward to all your posts!
Keep up the great work!

Review my webpage mens online dating

Anonymous said...

your wandering tactical maneuver. Although it's ready to hand for a make ill service.
A conspicuous filum colouration each period of time and as trends refine.

If you impoverishment them to your expected. You legal document
not go to on a sunshiny season day. ablaze emblem
demonstrate the sunlight and will pull in more morecustomers fain to betray Coach Purses Coach Factory Online Coach Factory Online Coach Outlet Coach Outlet Stores goals and cogitate
what you are justly bimanual, past withdraw up readership numbers.
e'er view secure to confab your insurance causal agency astir
subsidiary swirling contract. Ask your friends about their favourite colour, point in time compound the two!
If you hold to buy a watchband that matches the number of women

Anonymous said...

I loved as much as you will receive carried
out right here. The sketch is attractive, your
authored subject matter stylish. nonetheless, you command get bought an shakiness over
that you wish be delivering the following. unwell
unquestionably come more formerly again as exactly the
same nearly very often inside case you shield this increase.


my webpage; free robux hack

Anonymous said...

For most recent news you have to pay a visit the web and on internet I found this web site as a finest website for most up-to-date updates.


Look into my web site; Schmuckkommode im Web kaufen

Anonymous said...

Wow, amazing weblog structure! How long have you ever been blogging for?
you make running a blog glance easy. The overall look of your web site is great, as
well as the content!

Also visit my blog - plants vs zombies plush squash

Anonymous said...

I know this web site provides quality depending articles and additional information, is there any other site which gives these
data in quality?

My web site ... alta white

Anonymous said...

Wow that was strange. I just wrote an extremely long
comment but after I clicked submit my comment didn't show up.

Grrrr... well I'm not writing all that over again. Regardless, just wanted to say great blog!


Review my web blog :: lumix dmc-ts25 review

Anonymous said...

This piece of writing presents clear idea in support of the new visitors of blogging,
that really how to do running a blog.

my page - Sem Statistics

Anonymous said...

Wow! After all I got a blog from where I be able to in fact get helpful information regarding my study and knowledge.


Also visit my weblog; samsung french door refrigerators ()

Anonymous said...

What's up colleagues, fastidious article and fastidious arguments commented here, I amm actually enjoying by these.


my web blog ... pegboard garage storage ()

Anonymous said...

Hello mates, fastidious post and pleasant arguments commented at this place, I am truly
enjoying bby these.

my webpage; chandelier lights

Anonymous said...

Whoa! This blog looks just like my old one! It's on a completely different
topic but it has pretty much the same layout and design. Excellent choice of colors!


Also visit my website - synchronizer

Path Infotech said...

Thanks for sharing the information

Oracle Certifications

Anonymous said...

Good post however I was wanting to know if you could
write a litte more on this subject? I'd be very thankful if you could
elaborate a little bit further. Bless you!

Feel free to surf to mmy page -silver fish damagewhat

shipping agencies in egypt said...

Good post. Really usefull

Unknown said...

Hi , very good article, thanks for providing in-depth information on Oracle DBA Technology. Please continue sharing.

Unknown said...

It is good to see the best website for all updates on oracle dba with good examples.


Oracle DBA Tutorial

david said...

Thanks for very nice topic. The above provided lessons are very useful to explore Oracle DBA. One can gain knowledge from fundamentals through Oracle DBA Online Training

mohan said...

The information provided by you is more valuable thank you for sharing

oracle fusion financials training

vasal said...

nice information thank you for sharing

oracle fusion financials training

bella julia said...

Want to pass oracle 1Z0-148 exam in first try so, get latest Vcetests 1Z0-148 vce questions answers. Vcetests provide testified Database Application Development-SQL and PL/SQL 1Z0-148 practice test dumps with 1Z0-148 Desktop Practice test. Our 1Z0-148 study matrial is easy to understand and provide verified 1Z0-148 real exam questions answers. Get 1Z0-148 exam with 30% instant discount and pass your exam easily with 1Z0-148 dumps.

rmouniak said...


It is very good and useful .Learned a lot of new things from your post!Good creation ,thanks for good info Oracle SOA Online Training Bangalore