外键是我们经常使用的数据库约束方式。它保证了子表上的特定索引列取值一定与父表列（主键列）相一致对应。
而且保证在子表没有对应子记录的情况下，父表数据才能删除。应该说，外键是实现数据库完整性、消除潜在脏数据风险的重要手段。
在使用外键的时候，我们经常会要求在子表外键列上建立索引。这是为什么呢？
下面经过试验来进行研究。
1、先查看在没有外键的情况下，Oracle进行DML操作的基本规范。
创建子表emp1
SQL> create table emp1 as select * from emp; Table created.复制
创建主表dept1
SQL> create table dept1 as select * from dept; Table created.复制
查看两个表的基础信息
SQL> select object_name,object_id from dba_objects where object_name in('EMP1','DEPT1'); OBJECT_NAME OBJECT_ID -------------------- ---------- DEPT1 75209 EMP1 75208复制
查看当前会话的sid
SQL> select sid from v$mystat where rownum<2; SID ---------- 71复制
对主表进行DML操作查看这个会话中的锁的情况。
SQL> insert into dept1(deptno) values(50); 1 row created. SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=71 and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 71 TM 75209 0 3 0 0 71 TX 524318 918 6 0 0复制
在进行DML操作的时候，Oracle首先会尝试在数据表添加一个TM锁，级别是共享锁（lmode=3）。
这个TM锁的id1参数为添加对象的object_id。这个锁的作用是保证在进行DML事务的时候，数据表不会被修改或者独占，
体现在被添加3级锁以上级别的锁级别。这样，如果有其他会话要进行DML操作，在加入lmode=3锁的时候，是不会发生阻塞的。
这样也就保证了对同一个数据表，是可以同时并发DML操作的。其次，Oracle会独占使用数据行记录。就体现在添加的TX锁上，注意是lmode=6的级别锁。
TX锁的参数信息，表示的是对应的事务段信息
2、当存在外键关系的时候，oracle在进行DML时如何进行锁的操作。
在主表的deptno列上创建主键约束
SQL> alter table dept1 add constraint pk_deptno primary key (deptno); Table altered.复制
在子表的deptno列上创建外键约束
SQL> alter table emp1 add constraint fk_deptno1 foreign key (deptno) references dept1 (deptno); Table altered.复制
对主表进行DML操作，查看锁信息
SQL> insert into dept1(deptno) values(70); 1 row created. SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=71 and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 71 TM 75208 0 3 0 0 71 TM 75209 0 3 0 0 71 TX 196611 967 6 0 0复制
我们对主表进行操作，除了对主表的共享锁之外，还额外对子表（object_id=75208）进行加共享锁机制。在主表操作上，update和delete不会对子表加锁。
SQL> update dept1 set deptno=80 where deptno=70; 1 row updated. SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=71 and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 71 TM 75209 0 3 0 0 71 TX 458784 717 6 0 0 SQL> delete from dept1 where deptno=80; 1 row deleted. SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=71 and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 71 TM 75209 0 3 0 0 71 TX 458784 717 6 0 0复制
那么对子表进行DEML操作会影响主表吗？
SQL> insert into emp1(empno) values(9999); 1 row created. SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=71 and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 71 TM 75209 0 3 0 0 71 TM 75208 0 3 0 0 71 TX 458784 717 6 0 0 SQL> delete from emp1 where empno=9999; 1 row deleted. SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=71 and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 71 TM 75209 0 3 0 0 71 TM 75208 0 3 0 0 71 TX 458784 717 6 0 0 SQL> update emp1 set empno=7652 where deptno=10; 3 rows updated. SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=71 and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 71 TM 75208 0 3 0 0 71 TX 655360 747 6 0 0复制
子表操作的效果上，对子表进行insert和delete操作时候，也是会影响到父表锁结构的。
3、在外键列无索引导致的并发阻塞
为了测试并发情况，需要打开两个会话，现在使用的两个会话的sid分别是sid1=71和sid2=141；
第一个阻塞情况，sid1进行主表insert操作，同时sid2进行主表的delete操作。
--sid1 SQL> insert into dept1(deptno) values(90); 1 row created. --sid2 SQL> delete from dept1 where deptno=30; sid2处于等待状态 SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 0 4 0 71 TM 75208 0 3 0 1 71 TM 75209 0 3 0 0 71 TX 524291 919 6 0 0复制
此时，不涉及到对detail表的操作。但是sid2的delete操作却被阻塞。我们从锁lock视图中，可以看到情况：
会话1（sid=71）在insert主表的时候，在主子表上加入了lmode=3的共享锁。同时加入了对数据行的独占锁。
当会话2进行操作的时候，会话2会尝试对子表emp1表加入lmode=4的高级别锁。这个与会话1加入的共享锁冲突，所以被阻塞。
第二个阻塞情况，sid1进行子表insert操作，同时sid2进行主表的delete操作。
--sid1 SQL> insert into emp1(empno,deptno) values(1111,20); 1 row created. --sid2 SQL> delete from dept1 where deptno=70; sid2处于等待状态 SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 0 4 0 71 TM 75208 0 3 0 1 71 TM 75209 0 3 0 0 71 TX 65560 757 6 0 0复制
与上面的实验情况相似，sid2在进行主表删除的时候，额外希望在子表上添加lmode=4级别的锁结构。与sid1原先加入的共享锁不兼容，所以被阻塞。
第三个阻塞情况，sid1进行子表update操作的时候，sid2尝试对主表进行删除操作。
--sid1 SQL> update emp1 set empno=7999 where deptno=10; 3 rows updated. --sid2 SQL> delete from dept1 where deptno=10; sid2处于等待状态 SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 0 4 0 71 TM 75208 0 3 0 1 71 TX 262171 746 6 0 0复制
第四个阻塞情况，sid1进行子表删除操作，sid2进行主表delete操作。
--sid1 SQL> delete from emp1 where deptno=10; 3 rows deleted. --sid2 SQL> delete from dept1 where deptno=10; SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 0 4 0 71 TM 75208 0 3 0 1 71 TM 75209 0 3 0 0 71 TX 327711 1107 6 0 0复制
观察总结四个阻塞情况，我们发现了问题的所在：对主表删除delete操作时，Oracle的行为问题。
当Oracle对主表进行delete操作的时候，在子表索引列上没有索引时，会检查子表当前的锁情况，如果没有其他锁结构，就尝试加入一个lmode=3的锁结构。
如果有其他锁，就升级请求锁的级别，引起操作阻塞。
我们知道，在Oracle并发情况下，多会话情况众多，而且有外键的情况会引起连带的主子表同时锁定的场景。
所以，在没有外键列索引的时候，对表的并发DML操作非常容易引起阻塞现象，进而影响系统整体的并行度。
4、在外键列上创建索引之后进行上面相同情况的测试
SQL> create index ind_emp1_deptno on emp1(deptno); Index created.复制
一、外键列创建索引后再测试第一个阻塞情况，sid1进行主表insert操作，同时sid2进行主表的delete操作。
--sid1 SQL> insert into dept1(deptno) values(90); 1 row created. --sid2 SQL> delete from dept1 where deptno=70; 1 row deleted. SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 3 0 0 141 TM 75209 0 3 0 0 71 TM 75208 0 3 0 0 71 TM 75209 0 3 0 0 141 TX 327690 1108 6 0 0 71 TX 655389 750 6 0 0 6 rows selected.复制
阻塞现象消失
二、外键列创建索引后再测试第二个阻塞情况，sid1进行子表insert操作，同时sid2进行主表的delete操作。
--sid1 SQL> insert into emp1(empno,deptno) values(1111,20); 1 row created. --sid2 SQL> delete from dept1 where deptno=70; 1 row deleted. SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 3 0 0 141 TM 75209 0 3 0 0 71 TM 75208 0 3 0 0 71 TM 75209 0 3 0 0 141 TX 65545 761 6 0 0 71 TX 131083 999 6 0 0 6 rows selected.复制
阻塞现象消失
三、外键列创建索引后再测试第三个阻塞情况，sid1进行子表update操作的时候，sid2尝试对主表进行删除操作。
--sid1 SQL> update emp1 set empno=7999 where deptno=10; 3 rows updated. --sid2 SQL> delete from dept1 where deptno=70; 1 row deleted. SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 3 0 0 141 TM 75209 0 3 0 0 71 TM 75208 0 3 0 0 141 TX 393233 1009 6 0 0 71 TX 524304 922 6 0 0 6 rows selected.复制
阻塞现象消失
四、外键列创建索引后再测试第四个阻塞情况，sid1进行子表删除操作，sid2进行主表delete操作。
--sid1 SQL> delete from emp1 where deptno=10; 3 rows deleted. --sid2 SQL> delete from dept1 where deptno=70; 1 row deleted. SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (71,141) and type not in ('AE','TO'); SID TY ID1 ID2 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- 141 TM 75208 0 3 0 0 141 TM 75209 0 3 0 0 71 TM 75208 0 3 0 0 71 TM 75209 0 3 0 0 141 TX 393227 1010 6 0 0 71 TX 65558 760 6 0 0 6 rows selected.复制
阻塞现象消失
5、结论
经过上面的实验，我们证明了子表外键列索引的重要性。
(1)当建立主外键关联关系之后，对两者之一进行DML操作，非常容易形成连带锁结构，形成对两个表的锁定。所以，在DML操作很多的系统中，主子表形成锁是非常频繁的；
(2)在子表外键列上没有建立索引的时候，在对主表进行DML操作时，容易并发引起锁请求升级的情况，进而造成阻塞；
(3)如果确定建立主外键关联，一定要连带建立索引结构。除了避免阻塞之外，还有进行关联查询提高效率的好处。
所以，要不就不建立外键，建立外键一定要建立索引。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
770次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
652次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
578次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
536次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
524次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
500次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
486次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
456次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
381次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
377次阅读
2025-05-05 19:28:36
