外键是我们经常使用的数据库约束方式。它保证了子表上的特定索引列取值一定与父表列（主键列）相一致对应。
而且保证在子表没有对应子记录的情况下，父表数据才能删除。应该说，外键是实现数据库完整性、消除潜在脏数据风险的重要手段。
在使用外键的时候，我们经常会要求在子表外键列上建立索引。这是为什么呢？
下面经过试验来进行研究。
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; ----------对主表进行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.阻塞现象消失
经过上面的实验，我们证明了子表外键列索引的重要性。
(1)当建立主外键关联关系之后，对两者之一进行DML操作，非常容易形成连带锁结构，形成对两个表的锁定。所以，在DML操作很多的系统中，主子表形成锁是非常频繁的；