外來鍵沒有索引哪些DML操作會被阻塞

kakaxi9521發表於2018-03-15
原文網址 : http://blog.itpub.net/21374452/viewspace-2151877/
索引

通過主外來鍵關聯的兩張表，除非永遠不對主表的主鍵欄位作update、delete操作，否則oracle會推薦在子表的外來鍵上建索引。為何要在外來鍵上建索引，不建索引會有產生哪些負面影響，我們就來測試一下


###準備好測試用的表
create table t1010_pk1 (id number,pcol varchar2(1)) tablespace test;
alter table t1010_pk1 add constraint pk_t1010_pk1_id primary key(id) using index tablespace test;
insert into t1010_pk1 values(1,'A');
insert into t1010_pk1 values(2,'B');
insert into t1010_pk1 values(3,'C');
insert into t1010_pk1 values(4,'D');
insert into t1010_pk1 values(5,'E');
commit;


create table t1010_fk1 (id number,fcol varchar2(1)) tablespace test;
alter table t1010_fk1 add constraint fk_t1010_fk1_id foreign key(id) references t1010_pk1(id);
insert into t1010_fk1 values(1,'a');
insert into t1010_fk1 values(2,'a');
commit;


col object_name format a30
set linesize 60
select object_name,object_id from dba_objects where object_name in ('T1010_PK1','T1010_FK1');


OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
T1010_PK1                           18316
T1010_FK1                           18319


select * from t1010_pk1;
        ID P
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E


select * from t1010_fk1;
        ID F
---------- -
         1 a
         2 b


我們聚焦以下四個場景：
1、子表t1010_fk1上的DML執行後不提交，對之後在主表t1010_pk1上發起的DML操作是否會形成阻塞
2、主表t1010_pk1上的DML執行後不提交，對之後在主表t1010_pk1上發起的DML操作是否會形成阻塞
3、子表t1010_fk1上的DML執行後不提交，對之後在子表t1010_fk1上發起的DML操作是否會形成阻塞
4、主表t1010_pk1上的DML執行後不提交，對之後在子表t1010_fk1上發起的DML操作是否會形成阻塞


需要說明的的是，我們的測試中只關注表級鎖，即型別為TM的鎖

///////////////////
// 場景1；
// 子表執行insert操作後不提交
// 另一個事務中在主表t1010_pk1上發起update、insert、delete操作
///////////////////
---session 1: sid=162 insert子表
insert into t1010_fk1 values(3,'c');


---session 2: sid=18  update主表
update t1010_pk1 set id=14 where id=4; <---Hung住


---session 3: 查鎖
SQL> select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       5313
        18 TM          3          0      18316          0          0         17
        18 TM          0          4      18319          0          0         17   <---申請在子表上持有S鎖
       162 AE          4          0        100          0          0       5380
       162 TM          3          0      18316          0          0         22
       162 TM          3          0      18319          0          1         22   <---已在子表上持有RX鎖
       162 TX          6          0     393226       1979          0         22


因為S與RX不相容所以session 2的update被阻塞


---session 2: sid=18  中斷update操作後，發起insert主表操作
insert into t1010_pk1 values(6,'F');


1 row created.


---session 3: 查鎖
SQL> select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       5889
        18 TM          3          0      18316          0          0          8
        18 TM          3          0      18319          0          0          8   <---已在子表上持有RX鎖
        18 TX          6          0     131105       2003          0          8
       162 AE          4          0        100          0          0       5956
       162 TM          3          0      18316          0          0        598
       162 TM          3          0      18319          0          0        598   <---已在子表上持有RX鎖
       162 TX          6          0     393226       1979          0        598


因為RX與RX相相容，所以session 2的insert成功執行


---session 2: sid=18  回滾update操作後，發起delete主表操作
rollback;


delete t1010_pk1 where id=4;   <---Hung住


---session 3: 查鎖
SQL> select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       6207
        18 TM          3          0      18316          0          0         14   
        18 TM          0          4      18319          0          0         14   <---申請在子表上持有S鎖
       162 AE          4          0        100          0          0       6274
       162 TM          3          0      18316          0          0        916
       162 TM          3          0      18319          0          1        916   <---已在子表上持有RX鎖
       162 TX          6          0     393226       1979          0        916


因為S與RX不相容所以session 2的delete被阻塞


場景1-結論：
子表上的insert操作執行後不提交，會阻塞後續主表上的update(針對主鍵的update)、delete操作，主表上的insert操作不受影響。


阻塞原因分析：
主表上delete操作、涉及主鍵的update操作進行前必須要掃描子表，確保主表上的DML操作完成後，子表的外來鍵依然能與主表的主鍵關聯上，凡是任何破壞這一資料完整性的操作都會收到ORA-02292: integrity constraint的提示。當子表的外來鍵沒有索引時，對子表掃描就必須先申請持有S鎖，S鎖與insert子表時已經持有的RX鎖是不相容的，必須等RX鎖釋放後才能申請到，由此產生了阻塞。


如果對子表進行update(無論是外來鍵還是非外來鍵欄位的update)、delete操作後不提交，也會對主表形成相同的阻塞後果，驗證過程類似，不再贅述


///////////////////
// 場景2；
// 主表t1010_pk1上的DML執行後不提交
// 另一個事務中在主表t1010_pk1上發起update、insert、delete操作
///////////////////
======> A. 主表發起的是insert操作 <======


---session 1: sid=162: 主表發起insert操作
insert into t1010_pk1 values(6,'F');


1 row created.


---session 2: sid=18: update主表
update t1010_pk1 set id=15 where id=5;   <---Hung住


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       8050
        18 TM          3          0      18316          0          0          2
        18 TM          0          4      18319          0          0          2   <---子表上申請持有S鎖
       162 AE          4          0        100          0          0       8117
       162 TM          3          0      18316          0          0         29
       162 TM          3          0      18319          0          1         29   <---已在子表上持有RX鎖
       162 TX          6          0     196614       2011          0         29


因S與RX鎖不相容，所以session 2的update操作阻塞


---session 2: sid=18: insert主表
insert into t1010_pk1 values(7,'G');   


1 row created.


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       8799
        18 TM          3          0      18316          0          0        117
        18 TM          3          0      18319          0          0        117   <---已在子表上持有RX鎖
        18 TX          6          0     393235       1982          0        117
       162 AE          4          0        100          0          0       8866
       162 TM          3          0      18316          0          0        778
       162 TM          3          0      18319          0          0        778   <---已在子表上持有RX鎖
       162 TX          6          0     196614       2011          0        778


RX與RX相容，所以session 2上的insert操作未被阻塞


---session 2: sid=18: delete主表
rollback;


delete t1010_pk1 where id=3;   <---Hung住


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       8914
        18 TM          3          0      18316          0          0         15
        18 TM          0          4      18319          0          0         15    <---申請在子表上持有S鎖
       162 AE          4          0        100          0          0       8981
       162 TM          3          0      18316          0          0        893
       162 TM          3          0      18319          0          1        893    <---已在子表上持有RX鎖
       162 TX          6          0     196614       2011          0        893


因S鎖與RX鎖不相容，所以session 2的delete操作阻塞


======> B. 主表發起的是update操作 <======
---session 1: sid=162: 主表發起update操作
rollback;


update t1010_pk1 set id=15 where id=5;


1 row created.


---session 2: sid=18: update主表
update t1010_pk1 set id=14 where id=4;   


1 row created.


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       9052
        18 TM          3          0      18316          0          0         31   
        18 TX          6          0      65542       1515          0         31
       162 AE          4          0        100          0          0       9119
       162 TM          3          0      18316          0          0         43
       162 TX          6          0     262150       1501          0         43


子表上沒有發現表級鎖
       
---session 2: sid=18: insert主表
rollback;


insert into t1010_pk1 values(7,'G'); 


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       9254
        18 TM          3          0      18316          0          0         11
        18 TM          3          0      18319          0          0         11   <---已在子表上持有RX鎖
        18 TX          6          0     393249       1982          0         11
       162 AE          4          0        100          0          0       9321
       162 TM          3          0      18316          0          0        245
       162 TX          6          0     262150       1501          0        245


---session 2: sid=18: delete主表
rollback;


delete t1010_pk1 where id=3; 


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0       9449
        18 TM          3          0      18316          0          0          3
        18 TX          6          0     196634       2010          0          3
       162 AE          4          0        100          0          0       9516
       162 TM          3          0      18316          0          0        440
       162 TX          6          0     262150       1501          0        440


子表上沒有發現表級鎖       


======> C. 主表發起的是delete操作 <======
結果同B，不再贅述


場景2-結論：
主表發起insert後不提交，在另一個事務中對主表發起的update、delete操作會被阻塞、insert操作不會被阻塞；
主表發起update、delete後不提交，在另一個事務中對主表發起的DML操作不會被阻塞;


阻塞原因分析：
主表insert操作執行時會在子表上持有RX鎖，另一個事務中發起針對主表的update、delete操作會申請子表上的S鎖，S鎖與RX鎖不相容，故導致阻塞。
如果主表先發起update、delete操作，接著在另一個事務中對主表發起DML操作雖然不會被阻塞。但並不意味著先發起的update、delete操作不會在子表上加鎖，只不過這個鎖持有的時間較短，我們比較難觀測到，下面的補充測試可以證明這點：


---session 1: sid=187：主表發起update
update t1010_pk1 set id=14 where id=4;


---session 2: sid=178：主表發起update
update t1010_pk1 set id=15 where id=5;


---session 3: 鎖的情況顯示只在父表上持有RX鎖，子表上沒有任何鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (178,187) order by sid,id1;
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       178 AE          4          0        100          0          0        174
       178 TM          3          0      18316          0          0         11
       178 TX          6          0     327712       2037          0         11
       187 AE          4          0        100          0          0       1893
       187 TM          3          0      18316          0          0         17
       187 TX          6          0     196639       2034          0         17


---session 2：回滾
rollback;


---session 4：以exclusive模式鎖住子表
lock table t1010_fk1 in exclusive mode;


Table(s) Locked.


---session 2：update主表
update t1010_pk1 set id=15 where id=5; <---Hung住


---session 3: 鎖的情況顯示只在父表上持有RX鎖，子表上沒有任何鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (178,187) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       178 AE          4          0        100          0          0        738
       178 TM          3          0      18316          0          0         22
       178 TM          0          4      18319          0          0         22  <---正在申請子表上的S鎖
       187 AE          4          0        100          0          0       2457
       187 TM          3          0      18316          0          0        581
       187 TX          6          0     196639       2034          0        581


---session 4：回滾
rollback;


---session 2:   


1 row updated.    <---更新成功


---session 3: 鎖資訊顯示裡關於子表的鎖又消失了
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       178 AE          4          0        100          0          0        977
       178 TM          3          0      18316          0          0        261
       178 TX          6          0     589850       2034          0         30
       187 AE          4          0        100          0          0       2696
       187 TM          3          0      18316          0          0        820
       187 TX          6          0     196639       2034          0        820


以上可以證明在update主表主鍵欄位的過程中會短暫持有子表上的S鎖，update成功後隨即釋放


///////////////////
// 場景3；
// 子表t1010_fk1上的DML執行後不提交
// 另一個事務中在子表t1010_fk1上發起的DML操作是否會形成阻塞
///////////////////             
---session 1: sid=162：子表發起insert操作
insert into t1010_fk1 values(3,'c');


1 row created.


---session 2: sid=18: 子表執行insert操作
insert into t1010_fk1 values(4,'d');        


1 row created.


---session 3: 觀察鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0      16428
        18 TM          3          0      18316          0          0         46   <---已持有主表上的RX鎖
        18 TM          3          0      18319          0          0         46   
        18 TX          6          0     458762       1533          0         46
       162 AE          4          0        100          0          0      16495
       162 TM          3          0      18316          0          0        741   <---已持有主表上的RX鎖
       162 TM          3          0      18319          0          0        741   
       162 TX          6          0     655382       1497          0        741 


---session 2: sid=18: 子表執行delete操作
rollback;


delete t1010_fk1 where id=1;


1 row deleted.


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0      16598
        18 TM          3          0      18316          0          0         17   <---已持有主表上的RX鎖
        18 TM          3          0      18319          0          0         17
        18 TX          6          0     589834       2011          0         17
       162 AE          4          0        100          0          0      16665
       162 TM          3          0      18316          0          0        911   <---已持有主表上的RX鎖
       162 TM          3          0      18319          0          0        911
       162 TX          6          0     655382       1497          0        911


---session 2: sid=18: 子表執行update操作
rollback;


update t1010_fk1 set id=4 where id=2;


1 row updated.


---session 3: 查鎖
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (18,162) order by sid,id1;


       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        18 AE          4          0        100          0          0      16880
        18 TM          3          0      18316          0          0         17   <---已持有主表上的RX鎖
        18 TM          3          0      18319          0          0         17
        18 TX          6          0     458761       1539          0         17
       162 AE          4          0        100          0          0      16947
       162 TM          3          0      18316          0          0       1193   <---已持有主表上的RX鎖
       162 TM          3          0      18319          0          0       1193
       162 TX          6          0     655382       1497          0       1193


場景3-結論：
子表上的insert操作執行後不提交，不會阻塞另一個事務中對子表的insert、update、delete操作
子表上若先進行的是update、delete操作，也不會對另一個事務中子表上的DML操作形成阻塞，驗證過程類似，不再贅述


///////////////////
// 場景4；
// 主表t1010_fk1上的DML執行後不提交
// 另一個事務中在子表t1010_fk1上發起的DML操作是否會形成阻塞
///////////////////       
---session 1: sid=27：主表發起update操作
update t1010_pk1 set id=15 where id=5;


---session 2: sid=23：子表發起insert操作
insert into t1010_fk1 values(3,'c');


---session 3: 觀察鎖情況
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       3915
        27 AE          4          0        100          0          0       3923
        27 TM          3          0      18316          0          0         18
        23 TM          3          0      18316          0          0         12  <---已經持有主表上的RX鎖
        23 TM          3          0      18319          0          0         12  <---已經持有子表上的RX鎖
        27 TX          6          0     655374       1544          0         18
        23 TX          6          0     458753       1584          0         12       


---session 2: sid=23：子表發起update操作
rollback;


update t1010_fk1 set id=1 where id=2;


---session 3: 觀察鎖情況
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       4407
        27 AE          4          0        100          0          0       4415
        27 TM          3          0      18316          0          0        510   
        23 TM          3          0      18316          0          0          2   <---已經持有主表上的RX鎖
        23 TM          3          0      18319          0          0          2   <---已經持有子表上的RX鎖
        27 TX          6          0     655374       1544          0        510
        23 TX          6          0     262160       1577          0          2


---session 2: sid=23：子表發起delete操作
rollback;


delete t1010_fk1 where id=2;


---session 3: 觀察鎖情況        
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
      SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       4486
        27 AE          4          0        100          0          0       4494
        27 TM          3          0      18316          0          0        589  
        23 TM          3          0      18316          0          0          2  <---已經持有主表上的RX鎖
        23 TM          3          0      18319          0          0          2  <---已經持有子表上的RX鎖
        27 TX          6          0     655374       1544          0        589
        23 TX          6          0     393216       2099          0          2


場景4-結論：
主表上的update操作執行後不提交，不會阻塞另一個事務中對子表的DML操作
子表上若先進行的是insert、delete操作，也不會對另一個事務中子表上的DML操作形成阻塞，驗證過程類似，不再贅述




外來鍵沒有索引的情況，主表與子表上可能產生的鎖總結為下面兩張表：

表一：DML操作持有的鎖型別對照表：

 

Emp

Dept

Insert into t1010_fk1

RX

Null

Update t1010_fk1 (fcol)

RX

Null

Update t1010_fk1 (id)

RX

RX

Delete from t1010_fk1

RX

RX

Insert into t1010_pk1

RX

RX

Update t1010_pk1 (id)

S(瞬間持有)

RX

Update t1010_pk1 (pcol)

null

RX

Delete from t1010_pk1

S(瞬間持有)

RX

 

Emp

Dept

Insert into t1010_fk1

RX

Null

Update t1010_fk1 (fcol)

RX

Null

Update t1010_fk1 (id)

RX

RX

Delete from t1010_fk1

RX

RX

Insert into t1010_pk1

RX

RX

Update t1010_pk1 (id)

S(瞬間持有)

RX

Update t1010_pk1 (pcol)

null

RX

Delete from t1010_pk1

S(瞬間持有)

RX






































表二：主子表各類DML操作互斥關係對照表(紅色的單元格會產生阻塞)：





外來鍵欄位建了索引之後，子表上的S鎖會被RX鎖替代，RX與RX之間是完全相容的，引用官方文件的說法，避免了在子表上加全表鎖，轉而在外來鍵索引上申請了行級鎖
---session 1：sid=27：update子表
create index fk_t1010_fk1_id on t1010_fk1(id) tablespace test;
update t1010_fk1 set id=3 where id=2;


1 row updated.


---session 2：sid=23: update主表
update t1010_pk1 set id=15 where id=5;


1 row updated.


---session 3: 觀察鎖資訊
select sid,type,lmode,request,id1,id2,block,ctime from v$lock where sid in (23,27);
       SID TY      LMODE    REQUEST        ID1        ID2      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        23 AE          4          0        100          0          0       6883
        27 AE          4          0        100          0          0       6891
        27 TM          3          0      18316          0          0         25   
        27 TM          3          0      18319          0          0         25
        23 TM          3          0      18316          0          0         21
        23 TM          3          0      18319          0          0         21   <----子表上持有RX鎖
        27 TX          6          0     393224       2098          0         25
        23 TX          6          0     262156       1577          0         21


        
        

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21374452/viewspace-2151877/，如需轉載，請註明出處，否則將追究法律責任。

相關文章