ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PL/SQL在执行过程中,对其依赖的表,函数,过程都加lock和pin吗?

PL/SQL在执行过程中,对其依赖的表,函数,过程都加lock和pin吗?

原创 Linux操作系统 作者:wei-xh 时间:2011-02-17 13:43:02 0 删除 编辑
-----------创建个表
sys@CRMG>create table wxh_tbd as select * from dba_objects;

Table created.


-----------创建两个过程,互相依赖,其中一个过程需要访问之前创建的表。
create or replace procedure pinning
is
begin
null;
end;
/
create or replace procedure calling
IS
c NUMBER;
BEGIN
SELECT object_id INTO c FROM wxh_tbd WHERE object_id=20;
pinning;
dbms_lock.sleep(3000);
end;
/

---------会话一执行CALLING,它依赖了PINNING和WXH_TBD
sys@CRMG>exec calling;



这个时候DUMP共享池。
--------查看表wxh_tbd的library cache内容。
发现LMD和PMD都是0,即没有任何的LOCK和PIN.

BUCKET#34765 mtx=0x86e7e4e8(0, 44, 0):
  LIBRARY HANDLE:0x7a12da90 bid=34765 hid=b3f287cd lmd=0 pmd=0 sta=VALD
  name=SYS.WXH_TBD  
  hash=fbcfe03c612dd530e44f397fb3f287cd idn=0
  tim=02-17-2011 12:57:07  kkkk-dddd-llll=0000-0705-0705
  exc=0 ivc=0 ldc=3 slc=0 lct=20 pct=22
  cbb=6 rpr=3 kdp=0 kep=0 bus=19 hus=19 dbg=0
  dmtx=0x7a12db38(0, 9, 0) mtx=0x7a12dba0(1583, 171, 0)
  nsp=TABL(01) typ=TABL(02) llm=0 flg=KGHP/TIM/[00006800]
  lwt=0x7a12db18[0x7a12db18,0x7a12db18]
  pwt=0x7a12daf8[0x7a12daf8,0x7a12daf8]
  ref=0x7a12db28[0x728cf978,0x706ec5a8]
    HANDLE REFERENCES:
    reference   handle   flags
    --------- ---------  -------------------
     728cf978  7ec7c580  DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
     78473e90  656369a0  DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
     6c373f20  741fdf08  DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
     ad258558  7e55a008  DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
     6733d810  659fc9d0  DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
     81bf99d8  741d6410  DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
     706ec5a8  7ab7b1f0  DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
    LIBRARY OBJECT: 0xadf36350
    flg=EXS/LOC[0005] pfl=[0000] ssta=VALD
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 7e774078 adf36430 adf36320 I/-/A/-/-    0 NONE   00
        2 6bd791a8 6a5e9f58 6a5e9f28 I/-/A/-/-    0 NONE   00
        8 6bd790e0 943634c8 943632b0 I/-/A/-/-    0 NONE   00

--------查看PINNING的library cache内容
根据LMD和PMD的内容可知道,加了NULL型的LOCK和S型的PIN
并且注意红色字体部分,清楚的显示了LOCK和PIN的实现跟enquence的区别,它是通过HANDLER部分来记载的。不过他们相似的地方是,都有排队机制。

BUCKET#42528 mtx=0x86eca1e0(0, 79, 0):
  LIBRARY HANDLE:0x74a4e598 bid=42528 hid=7feaa620 lmd=N pmd=S sta=VALD
  name=SYS.PINNING  
  hash=33438f7c4743cfb2654300177feaa620 idn=0
  tim=02-17-2011 12:46:59  kkkk-dddd-llll=0000-001d-001d
  exc=0 ivc=0 ldc=1 slc=1 lct=4 pct=4
  cbb=3 rpr=2 kdp=0 kep=0 bus=2 hus=2 dbg=0
  dmtx=0x74a4e640(0, 1, 0) mtx=0x74a4e6a8(859, 93, 0)
  nsp=TABL(01) typ=PRCD(07) llm=0 flg=KGHP/TIM/[00004800]
  lwt=0x74a4e620[0x74a4e620,0x74a4e620]
  pwt=0x74a4e600[0x74a4e600,0x74a4e600]
  ref=0x74a4e630[0x6c373ea8,0x6c373ea8]
    HANDLE REFERENCES:
    reference   handle   flags
    --------- ---------  -------------------
     6c373ea8  741fdf08  DEP[01]
timestamp=02-17-2011 12:46:59 whr=0
   LOCK OWNERS:
        lock     user  session count mode       flags
    -------- -------- -------- ----- ---- ------------------------
    66355668 9e39fdf0 9e39fdf0     1 N    PNC/[02]
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    746620d8 9e39fdf0 9e39fdf0 66355668     3 S    0011

    LIBRARY OBJECT: 0x67b6ffd0
    flg=EXS/LOC[0005] pfl=NST[0001] ssta=VALD
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 65b2c620 67b700b0 67b6ffa0 I/P/A/-/-    0 NONE   00
        2 67b702f8 94f4ee50 94f4ee20 I/-/A/-/-    0 NONE   00
        4 7bceb0f0 942515f0 942515c0 I/P/A/-/-    1 NONE   00

--------查看CALLING自身。
也是加了NULL的LOCK和S型的PIN。

BUCKET#26086 mtx=0x86e298d0(0, 17, 0):
  LIBRARY HANDLE:0x741fdf08 bid=26086 hid=eefe65e6 lmd=N pmd=S sta=VALD
  name=SYS.CALLING
  hash=8c2737be8eaa56203c151d0aeefe65e6 idn=0
  tim=02-17-2011 12:47:56  kkkk-dddd-llll=0000-001d-60bf
  exc=0 ivc=0 ldc=1 slc=1 lct=4 pct=6
  cbb=5 rpr=3 kdp=0 kep=0 bus=2 hus=2 dbg=0
  dmtx=0x741fdfb0(0, 1, 0) mtx=0x741fe018(859, 50, 0)
  nsp=TABL(01) typ=PRCD(07) llm=0 flg=KGHP/TIM/[00004800]
  lwt=0x741fdf90[0x741fdf90,0x741fdf90]
  pwt=0x741fdf70[0x741fdf70,0x741fdf70]
  ref=0x741fdfa0[0x70e7c838,0x70e7c838]
    HANDLE REFERENCES:
    reference   handle   flags
    --------- ---------  -------------------
     70e7c838  6539ae30  DEP[01]
timestamp=02-17-2011 12:47:56 whr=0
    LOCK OWNERS:
        lock     user  session count mode       flags
    -------- -------- -------- ----- ---- ------------------------
    744692d0 9e39fdf0 9e39fdf0     1 N    PNC/[02]
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    66355858 9e39fdf0 9e39fdf0 744692d0     3 S    0011
    LIBRARY OBJECT: 0x70ba08a0
    flg=EXS/LOC[0005] pfl=NST[0001] ssta=VALD
    DEPENDENCIES: count=4 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 6c374168  6c373ea8 74a4e598        0 DEP[01]
              1 6c374168  6c373f20 8c6eecc0        0 DEP[01]
              2 6c374168  6c373f60 7aae1bd8        0 DEP[01]
              3 6c374168  6c374040 745c4a58        0 DEP[01]
    ACCESSES: count=2 size=16
    dependency# types
    ----------- -----
              0 000c
              2 000c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 7aa4d430 70ba0980 70ba0870 I/P/A/-/-    0 NONE   00
        2 70ba0bc8 9435cec0 9435ce90 I/-/A/-/-    0 NONE   00
        4 6c373fb8 944d6a78 944d6a48 I/P/A/-/-    1 NONE   00




由以上内容可以知道,CALLING执行过程中
1)对其依赖的过程,加了NULL型的LOCK和S型的PIN
2)对其依赖的表,没有加LOCK和PIN。(其实在PL/SQL解析的时候,也是加S型的LOCK的)

按照上面,不难推断,CALLING执行过程中
1)表WXH_TBD可以被顺利删除(其他DDL也可以),因为删除表需要获取表上(HANDLER)的X型的LOCK跟NULL型的LOCK不冲突。
2)对其依赖的过程PINNING,由于PINNING上存在NULL型的LOCK和S型的PIN,删除PINNING需要X型的LOCK和X型的PIN,LOCK可以顺利获得,因为NULL与X不冲突,但是PIN却获得不了,S和X是冲突的。

看看实际是不是如此:
sysCRMG>drop table wxh_tbd;
Table dropped.


sysCRMG>drop procedure pinning;
会HANG在那。
查看后台等待事件,已经产生了pin等待。而且等待是发生在pinning上的。
sysCRMG>select event,p1raw from v$session_wait where wait_class<>'Idle';
EVENT                          P1RAW
------------------------------ ----------------
library cache pin              0000000074A4E598
sysCRMG>select KGLNAOBJ from x$kglob where kglhdadr = '0000000074A4E598';
KGLNAOBJ
------------------------------
PINNING

还有几个疑问:

1)那就是PL/SQL执行的时候,到底需要不需要获得LOCK(此LOCK是PL/SQL对象上的LOCK)。因为上面DUMP出来的LOCK都是NULL的。
答案是执行PL/SQL执行,在进行解析的时候,是需要对PL/SQL的HANDLER加S型的LOCK的。只是时间非常短。
通过如下方法可以验证:
session 1执行:
exec calling;
session 2 执行:
alter procedure calling compile;
session 3执行:
select sid from v$mystat where rownum=1;
    SID
-------
    958
exec calling;

这个时候查看等待。
select sid,event,p1raw from v$session_wait where wait_class<>'Idle';
    SID EVENT                          P1RAW
------- ------------------------------ 
   1291 SQL*Net message to client      0000000062657100


出现了LOCK。而且是会话3产生的。因此它需要获得S型的LOCK跟SESSION2的X型的LOCK冲突了。
select KGLNAOBJ from x$kglob where kglhdadr = '00000000741FDF08';
KGLNAOBJ
------------------------------
CALLING

2)PL/SQL执行的时候需要不需要获得依赖对象上的LOCK。

答案是需要,在进行解析的时候是需要获得S型的LOCK的。

 

SESSION 1:对一个大表的一个字段进行耗时的DDL操作。

SQL> alter table wxh_tbd modify gmt_create not null;

 

SESSION 2:执行CALLING过程(CALLING过程里调用了表WXH_TBD);

EXEC CALLING;

 

SESSION 3:查看等待

 

SQL> select sid,event,p1raw from v$session_wait where wait_class<>'Idle';
       SID EVENT                          P1RAW
---------- ------------------------------ ----------------
      3262 SQL*Net message to client      0000000062657100
      3263 library cache lock             000000009A36D448
      3266 db file scattered read         0000000000000070

SQL> select KGLNAOBJ from x$kglob where kglhdadr = '000000009A36D448';

KGLNAOBJ
--------------------------------------------------------------------------------
WXH_TBD

等待是WXH_TBD上的。



BUCKET#42528 mtx=0x86eca1e0(0, 1350, 0):
  LIBRARY HANDLE:0x8c80b288 bid=42528 hid=7feaa620 lmd=X pmd=S sta=VALD
  name=SYS.PINNING 
  hash=33438f7c4743cfb2654300177feaa620 idn=138331
  tim=02-17-2011 12:46:59  kkkk-dddd-llll=0000-0011-0011
  exc=0 ivc=0 ldc=1 slc=2 lct=2 pct=1
  cbb=1 rpr=1 kdp=0 kep=0 bus=1 hus=1 dbg=0
  dmtx=0x8c80b330(0, 1, 0) mtx=0x8c80b398(2940, 18, 0)
  nsp=TABL(01) typ=PRCD(07) llm=0 flg=KGHP/TIM/[00002800]
  lwt=0x8c80b310[0x8c80b310,0x8c80b310]
  pwt=0x8c80b2f0[0x7a8bdc18,0x7a8bdc18]
  ref=0x8c80b320[0x73b33128,0x73b33128]
    HANDLE REFERENCES:
    reference   handle   flags
    --------- ---------  -------------------
     73b33128  7eff6b48  DEP[01]
 timestamp=02-17-2011 12:46:59 whr=0
    LOCK OWNERS:
        lock     user  session count mode       flags
    -------- -------- -------- ----- ---- ------------------------
    7a8bdca0 b26369d8 b26369d8     1 X    CNB/[01]
    6687bd28 9e63ebb0 9e63ebb0     1 N    PNC/[02]
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    7a6a1c68 9e63ebb0 9e63ebb0 6687bd28     3 S    0011
    PIN WAITERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    7a8bdba8 b26369d8 b26369d8        0     0 X    0000

    LIBRARY OBJECT: 0x7ba19858
    flg=EXS/LOC[0005] pfl=NST[0001] ssta=VALD
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 74e427e8 7ba19978 7ba19828 I/P/A/-/-    0 NONE   00
        4 7ba199c0 98b48950 98b48920 I/P/A/-/-    1 NONE   00

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-687416/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2337679