模擬RI鎖定導致阻塞的場景，並分析v$lock相應的鎖定資訊，給出SQL演示。

> create table a (id int primary key);                   a是主表，定義了id欄位為主鍵
Table created.
> create table b (id references a(id));                   b是從表，id欄位是引用主表的id欄位
Table created.
> insert into a values(1);                                     往主表a中插入一條資料但沒有提交，事務沒有結束會產生鎖定
1 row created.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73465       0       3       0       0    insert由於有從屬關係因此會在2個表上都加3號共享鎖
   138 TM             73467       0       3       0       0
   138 TX          196640    940       6       0       0
> select object_name from dba_objects where object_id in (73465,73467);  ID1就是138會話操作的物件id，我們會在主表和從表上都加上表級鎖
OBJECT_NAME
--------------------------------------------------------------------------------
A          73465
B          73467
> commit;                                                 提交之後釋放鎖
Commit complete.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2; 鎖會隨著事務的結束而釋放
no rows selected
> select * from a;                                     a表中有一條記錄
      ID
----------
      1
> select * from b;
no rows selected
> update a set id=100 where id=1;                         主表a上更新了一條記錄
1 row updated.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73465       0       3       0       0       update現在只對主表有鎖定，從表沒有鎖定
   138 TX          196634    941       6       0       0
> commit;                                                 提交之後釋放鎖
Commit complete.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;    現在沒有鎖了
no rows selected
> select * from a;       主表裡的值已經更新了
      ID
----------
   100
> delete from a;
1 row deleted.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73465       0       3       0       0       delete也是隻對主表有鎖定，從表沒有鎖定
   138 TX          655375    705       6       0       0
> commit;                                                             提交釋放鎖
Commit complete.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
no rows selected

> insert into b values(2);
insert into b values(2)
*
ERROR at line 1:
ORA-02291: integrity constraint (LEO1.SYS_C0010831) violated - parent key not found 直接給從表插入記錄，如果主表沒有的話，會報錯違反引用完整性約束，沒有主表依據

> insert into a values(2);    我們只能先給主表插入
1 row created.
> insert into b values(2);    再給從表插入才可以，因為從表的資料必須在主表裡先存在，才能正常引用
1 row created.
> select * from a;             主表有了
      ID
----------
      2
> select * from b;             從表有了
      ID
----------
      2
> select * from a;             主表有3
      ID
----------
      2
      3
> insert into b values(3); 才能給從表插入
1 row created.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73465       0       3       0       0 從表的insert也會對主從表同時加鎖
   138 TM             73467       0       3       0       0
   138 TX          393246    939       6       0       0
> select * from b;
      ID
----------
      2
      3
> commit;             釋放鎖
Commit complete.
> delete from b;    刪除從表
2 rows deleted.

> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73465       0       3       0       0 從表delete也會對主從表同時加鎖
   138 TM             73467       0       3       0       0
   138 TX          196620    944       6       0       0

> commit;                               釋放鎖
Commit complete.
> select * from a;                   主表有2條記錄
      ID
----------
      2
      3
> select * from b;                   從表沒有記錄
no rows selected

> insert into a values(4);          向主表插入1條記錄，因為沒有提交所以是未決狀態
1 row created.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73465       0       3       0       0    此時對主從表都加了鎖定
   138 TM             73467       0       3       0       0
   138 TX          589834    937       6       0       0
> insert into leo1.b values(4);       此時向從表也插入1條記錄，由於從表的資料必須引用自主表，而主表資料現在是一種未決狀態，所以hang住不能前進
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73465       0       3       0       0
   138 TM             73467       0       3       0       0
   138 TX          589834    937       6       0       1    138會話阻塞156會話，這個6代表已經插入成功，但修改值還是未決狀態
   156 TM             73467       0       3       0       0
   156 TM             73465       0       3       0       0
   156 TX          393221    942       6       0       0    這個6代表也已經插入成功，但修改值還是未決狀態
   156 TX          589834    937       0       4       0    主從表插入後產生了2個TX鎖，這說明這是2條不同的記錄，2個獨立的記錄，不是爭用同一條記錄

小結：之所以還有一個TX鎖正在申請4號鎖，是因為2條記錄的修改值都是未決狀態違反了引用完整性約束從而產生阻塞。導致156會話hang住不能前進。

2012.11.28
天津&winter
分享~成就夢想
Blog：

Oracle LOCK內部機制及最佳實踐系列(二)模擬RI鎖定導致阻塞的場景，並分析v$lock

相關文章