外键没有索引哪些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 操作持有的锁类型对照表：

表二： 主子表各类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