PL/SQL在執行過程中,對其依賴的表,函式,過程都加lock和pin嗎?

wei-xh發表於2011-02-17
-----------建立個表
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章