結論

1,oracle lock鎖機制非常複雜，v$lock.lmode之間的區別以及適用場景還要研究


2,本文基於3個併發會話進行測試，第1個會話操作基於子表DML操作，第2個會話操作基於父表的DML操作，第3個會話基於子表的DML操作（包含INSERT，UPDATE，DELETE）


3,需要3個併發會話，基於子表外來鍵列是否構建索引的行為區別
  對於子表的DML操作影響有所不同
  A,如構建索引，則第3個會話操作僅與操作父表DML對應記錄的子表記錄DML才會阻塞，其它子表的DML正常繼續
  B,如不構建索引，則第3個會話的任何DML操作皆會受到阻塞




4，再細化分解下，因為是父子表，透過外來鍵把父子表記錄關聯起來，以保障資料一致性，所以不管你是先更新父表或子表記錄不提交，而更新對應的子表或父表記錄對應的事務則會等待


5，未構建子表外來鍵索引時，V$LOCK會同時對父子表持行級排它鎖，並且會請求共享行級排它鎖ssx
   相反如果構建子表外來鍵索引時，v$lock也會同時對父子表持行級排它鎖，不會請求任何鎖


6, lmode值為0,即none鎖，此模式鎖將在另一文章進行測試與學習


7, 再直白一點講，如子表外來鍵列構建索引，到時對於子表的併發操作會更好，否則會更差，所以一定要為子表外來鍵列構建索引


8，而且如不為子表外來鍵列構建索引，刪除父表DML,會導致子表全表掃描，否則僅會對子表進行索引掃描即可


9，還有一個問題，某個會話因為DML持的鎖會不會因為並行會話的增加或變化，會導致某個會話DML持鎖模式發生變化呢，這個還沒有完全想明白，將在另一文章進行測試


10,v$lock.block的列含義及適用場景，同上，也要專門測試，加深理解   

測試

1，建立父表
SQL> create table t_parent(a int,b int,c int);


Table created.


2，建立子表
SQL> create table t_child(a int,b int);


Table created.


3，建立主鍵約束
SQL> alter table t_parent add constraint pk_t_parent primary key(a);


Table altered.


4，建立子鍵外來鍵約束
SQL> alter table t_child add constraint fk_t_child foreign key(a) references t_parent(a);


Table altered.


5,父子表記錄
SQL> select * from t_parent order by 1;


         A          B          C
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3


SQL> select * from t_child order by 1;


         A          B
---------- ----------
         1          1
         2          2




6，測試子表外來鍵列是否構建索引，對於OLTP DML並行的影響
未建子表外來鍵列索引


會話1


SQL> select sid from v$mystat where rownum=1;


       SID
----------
       234


刪除子表1條記錄
SQL> delete from t_child where a=1;


1 row deleted.






會話2


SQL> select sid from v$mystat where rownum=1;


       SID
----------
        55


插入主表1條記錄
SQL> insert into t_parent values(4,4,4);


1 row created.


刪除主表1條記錄
SQL> delete from t_parent where a=4;


HANG住




監控會話
SQL> select sid,type,id1,id2,
  2         decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,
       ctime,block   
       decode(request,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') request,
  4         ctime,block   
  5  from v$lock where  type in ('TM','TX') and sid in (234,55);


       SID TYPE        ID1        ID2 LMODE      REQUEST         CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       234 TM        74751          0 row-x      none              238          0
       234 TM        74752          0 row-x      none              240          1
        55 TM        74751          0 row-x      none              226          0
        55 TM        74752          0 row-x      ssx               226          1 
       234 TX     15007761       3602 x          none              239          0
        55 TX     13434883       6598 x          none              227          0


6 rows selected.


SQL> select type,name,id1_tag,id2_tag,is_user,description from v$lock_type where type in ('TM','TX');


TYPE                 NAME            ID1_TAG                   ID2_TAG                        IS_USE DESCRIPTION
-------------------- --------------- ------------------------- ------------------------------ ------ --------------------------------------------------
TM                   DML             object #                  table/partition                YES    Synchronizes accesses to an object
TX                   Transaction     usn<<16 | slot            sequence                       YES    Lock held by a transaction to allow other transact
                                                                                                     ions to wait for it


SQL> select object_id,object_name from dba_objects where object_id in (74751,74752);


 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     74751 T_PARENT
     74752 T_CHILD






會話3
SQL> insert into t_child values(1,1);


HANG住


SQL> insert into t_child values(3,3);


HANG住


SQL> delete from t_child where a=1;


HANG住


SQL> delete from t_child where a=2;


HANG住


SQL> update t_child set a=2 where a=1;


HANG住


SQL> update t_child set a=1 where a=2;


hang住


總結：
  1，子表外來鍵列未建索引時，如果1個會話刪除子表1條記錄時，且另一會話刪除主表1條記錄時，而隨後的會話任何對於子表的DML皆會HANG掉
     可見子表t_child不僅持行級排它鎖，且會請求共享行級排它鎖，而且t_child還是blocker就是它還是阻塞者，也就是說它佔有了別人
     需要的鎖資源；這樣主表操作1條記錄時，對子表任何的DML全會造成阻塞






建子表外來鍵列索引


SQL> create index idx_t_child on t_child(a);


Index created.


會話1
SQL> delete from t_child where a=1;


1 row deleted.


會話2
SQL> insert into t_parent values(4,4,4);


1 row created.


SQL> delete from t_parent where a=4;


1 row deleted.


未HANG住




監控會話
SQL> select sid,type,id1,id2,
  2         decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,
  3         decode(request,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') request,
  4         ctime,block   
  5  from v$lock where  type in ('TM','TX') and sid in (234,55);


       SID TYPE        ID1        ID2 LMODE      REQUEST         CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       234 TM        74751          0 row-x      none              233          0
       234 TM        74752          0 row-x      none              234          0
        55 TM        74751          0 row-x      none              224          0
        55 TM        74752          0 row-x      none              224          0
       234 TX     13697029       5850 x          none              234          0
        55 TX     18939931       3110 x          none              224          0


6 rows selected.


會話3
SQL> insert into t_child values(1,1);


1 row created.
未HANG住


SQL> insert into t_child values(3,3);


1 row created.


未hang住


SQL> delete from t_child where a=1;
HANG住


SQL> delete from t_child where a=2;


1 row deleted.
未HANG住


SQL> update t_child set a=2 where a=1;
hang掉


SQL> update t_child set a=1 where a=2;


1 row updated.
未hang住


總結：
  1，子表外來鍵列構建索引時，如果1個會話刪除子表1條記錄時，且另一會話刪除主表1條記錄時，而隨後的會話任何對於子表的DML僅會HANG住與會話1更新相同行的DML操作
     可見對於主表操作，只會對於主鍵列對應的子表記錄對應的DML造成陰塞

個人簡介：