模擬RI鎖定導致阻塞的場景

1.建表並驗證對和表DML操作時的鎖定狀態
a是主表，定義了id欄位為主鍵
 b是從表，id欄位是引用主表的id欄位
 結論：
 
BYS@dg2>select distinct sid from v$mystat;

       SID
----------
        17
BYS@dg2>create table a(id int primary key);

Table created.
BYS@dg2>create table b(id references a(id));

Table created.
BYS@dg2>insert into a values(1);  

1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        17 TM      75054          0          3          0          0 insert由於有從屬關係因此會在2個表上都加3號共享鎖
        17 TM      75056          0          3          0          0
        17 TX     458758        888          6          0          0
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);

       SID EVENT
---------- ----------------------------------------------------------------
        17 SQL*Net message to client
        49 SQL*Net message from client
BYS@dg2>col object_name for a20
BYS@dg2>select object_name from dba_objects where object_id in(75054,75056);

OBJECT_NAME
--------------------
A
B
BYS@dg2>commit;    鎖會隨著事務的結束而釋放

Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

no rows selected
BYS@dg2>select * from a;

        ID
----------
         1
BYS@dg2>select * from b;

no rows selected
BYS@dg2>update a set id=111 where id=1;

1 row updated.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        17 TM      75054          0          3          0          0  update現在只對主表有鎖定，從表沒有鎖定
        17 TX     393219       1031          6          0          0
BYS@dg2>commit;

Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

no rows selected
BYS@dg2>select * from a;

        ID
----------
       111
BYS@dg2>delete a;

1 row deleted.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        17 TM      75054          0          3          0          0 delete也是隻對主表有鎖定，從表沒有鎖定
        17 TX     524310       1244          6          0          0
BYS@dg2>commit;

Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

no rows selected
#########################################################################


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

BYS@dg2>insert into a values(2);

1 row created.
BYS@dg2>commit;

Commit complete.
BYS@dg2>select * from a;

        ID
----------
         2
BYS@dg2>select * from b;

no rows selected
BYS@dg2>insert into b values(2);

1 row created.
BYS@dg2>select * from b;

        ID
----------
         2
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        17 TM      75054          0          3          0          0   
        17 TM      75056          0          3          0          0  從表的insert也會對主從表同時加鎖
        17 TX     196634       1043          6          0          0
BYS@dg2>commit;

Commit complete.


BYS@dg2>delete b;

1 row deleted.

BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        17 TM      75054          0          3          0          0
        17 TM      75056          0          3          0          0 從表delete也會對主從表同時加鎖
        17 TX     393245       1031          6          0          0
#############################################


BYS@dg2>select distinct sid from v$mystat;

       SID
----------
        46
BYS@dg2>select * from a;

        ID
----------
         2
BYS@dg2>select * from b;

no rows selected
BYS@dg2>insert into a values(33);

1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        46 TM      75054          0          3          0          0 insert由於有從屬關係因此會在2個表上都加3號共享鎖
        46 TM      75056          0          3          0          0
        46 TX     458758        888          6          0          0
此時在會話2上也執行向從表插入的動作會hang住
BYS@dg2>insert into b values(33);

BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        49 TX     589856       1025          0          4          0 主從表插入後產生了2個TX鎖，這說明這是2條不同的獨立的記錄，不是爭用同一條記錄
        49 TM      75054          0          3          0          0
        49 TM      75056          0          3          0          0
        46 TM      75054          0          3          0          0
        46 TM      75056          0          3          0          0
        49 TX     524298       1245          6          0          0 插入成功，修改值未決
        46 TX     589856       1025          6          0          1 46阻塞49，6代表插入成功，修改值未提交是未決狀態
有一個TX鎖正在申請4號鎖，是因為2條記錄的修改值都是未決狀態違反了引用完整性約束從而產生阻塞。導致156會話hang住不能前進。