外來鍵沒有索引哪些DML操作會被阻塞
通過主外來鍵關聯的兩張表，除非永遠不對主表的主鍵欄位作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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 無索引的外來鍵之主表子表DML操作實驗及結論索引
- Oracle 指令碼 線上哪些索引從來沒有被使用過Oracle指令碼索引
- SQLServer DML操作阻塞SELECT查詢SQLServer
- 10.30 索引，外來鍵索引
- Redis內部阻塞式操作有哪些？Redis
- oracle全文索引之commit與DML操作Oracle索引MIT
- oracle點陣圖索引對DML操作的影響Oracle索引
- css動畫是否會被js阻塞CSS動畫JS
- 外來鍵欄位未建索引引發的死鎖索引
- 會引起Reflow和Repaint的操作有哪些？AI
- ORACLE 分割槽索引UNUSABLE導致的DML操作失敗引起的血案Oracle索引
- 主鍵和外來鍵
- sqlserver外來鍵SQLServer
- 網站被CC攻擊會有哪些影響網站
- [番外]-練習MySQL沒素材？來一波字串操作MySql字串
- indexedDB 內鍵與外來鍵Index
- 程式設計師高薪盛宴背後：未來有哪些程式設計師會被淘汰？程式設計師高薪
- enq: TM - contention解決之道——外來鍵無索引導致鎖爭用ENQ索引
- 外來鍵約束
- MYSQL的外來鍵MySql
- 通過外來鍵找主鍵
- Python快捷鍵有哪些?Python
- 聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項Oracle
- 外來鍵的變種
- Mysql系列第五講 DML操作彙總，確定你都會？MySql
- 好未來有沒有“好未來”？
- 用Golang做一個永久阻塞，有哪些小技巧 ？Golang
- DDL、DML、DCL、DQL相關操作
- Sysbench-0.5改成只有DML操作
- HarmonyOS 應用生命週期有哪些？ 按返回鍵會呼叫哪些生命週期？
- mysql的新建索引會導致insert被lockedMySql索引
- mysql有哪些資料操作MySql
- OpenStack並沒有被Kubernetes替代 - mirantis
- mysql 外來鍵索引入門介紹，為什麼工作中很少有人使用？MySql索引
- 關於外來鍵約束
- mysql建立外來鍵語句MySql
- 為什麼不用外來鍵
- 約束外來鍵筆記筆記