外键是我们经常使用的数据库约束方式。它保证了子表上的特定索引列取值一定与父表列（主键列）相一致对应。而且保证在子表没有对应子记录的情况下，父表数据才能删除。应该说，外键是实现数据库完整性、消除潜在脏数据风险的重要手段。
在使用外键的时候，我们经常会要求在子表外键列上建立索引。这个“江湖定则”有什么样的原因呢？本篇我们一起来进行探讨。还是使用组合实验的方式来进行研究。
1、
环境准备
我们在
Oracle 11gR2版本上进行试验，构建初步的实验环境。
首先建立父子表关系。
SQL> select * from v$version;
BANNER
-----------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE
11.2.0.1.0
Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--环境准备
SQL> create table master (id number, vname varchar2(10));
Table created
SQL> alter table master add constraint pk_master primary key (id);
Table altered
SQL> create table detail (did number, mid number, details varchar2(10));
Table created
SQL> alter table detail add constraint pk_detail primary key (did);
Table altered
SQL> alter table detail add constraint fk_detail_master_mid foreign key (mid) references master(id);
Table altered
上面建立了数据表
master和detail，两个数据表分别以id和did作为主键。同时，detail的mid列作为与master表id列的外键关系，建立外键fk_detail_master_mid。
两个数据表对象的基础信息对应为：
SQL> select object_name,object_id from dba_objects where object_name in ('MASTER','DETAIL','IDX_DETAIL_MID');
OBJECT_NAME
OBJECT_ID
-------------------- ----------
DETAIL
75191
MASTER
75189
基础数据准备：
SQL> select * from master;
ID VNAME
---------- ----------
2 dfdf
4
（后加入该条记录）
SQL> select * from detail;
DID
MID DETAILS
---------- ---------- ----------
1
1
2
1 dkff
3
2 fdkff
会话准备，作为并发操作实验，我们准备两个会话窗口。
sid1和sid2，其中sid1为主操作会话窗口。sid1会话编号为135，sid2会话编号为18。另外，使用监控语句监控并发锁状态。
select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
2、外键关联下，DML操作锁的特殊性
首先我们探讨一下
Oracle条件下外键关联影响DML操作锁的情况。在一般情况下，Oracle使用的是行锁机制，保证进行DML操作的数据行不会被其他会话影响，也不会影响到其他数据行的操作，更不会影响到其他数据表的问题。
在没有外键的情况下，我们观察一下
Oracle锁情况（额外实验，非Oracle11g环境）。
SQL> select sid from v$mystat where rownum<2;
----------
SQL> insert into t select * from dba_objects;
50367 rows inserted
SQL> select sid, type ,id1, id2, lmode, request, block from v$lock where sid=144;
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
144 TM
54206
0
3
0
0
144 TX
327716
1068
6
0
0
从这个案例中，可以看到
Oracle进行DML操作的基本规范。在进行DML操作的时候，Oracle首先会尝试在数据表添加一个TM锁，级别是共享锁（lmode=3）。这个TM锁的id1参数为添加对象的object_id。这个锁的作用是保证在进行DML事务的时候，数据表不会被修改或者独占，体现在被添加3级锁以上级别的锁级别。这样，如果有其他会话要进行DML操作，在加入lmode=3锁的时候，是不会发生阻塞的。这样也就保证了对同一个数据表，是可以同时并发DML操作的。
其次，
Oracle会独占使用数据行记录。就体现在添加的TX锁上，注意是lmode=6的级别锁。TX锁的参数信息，表示的是对应的事务段信息。
结论：在没有外键的作用下，
Oracle进行的是最小粒度的锁控制。不会影响到其他数据表。
如果添加了外键，我们对数据表的
DML操作后，将会有何种影响呢？
SQL> insert into master values (4,'dk');
1 row inserted
此时，监控结构。
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM
75189
0
3
0
0
135 TM
75191
0
3
0
0
135 TX
655390
1000
6
0
0
我们对主表进行操作，除了对主表的共享锁之外，还额外对
子表（
object_id=75191）进行加共享锁机制
。
在主表操作上，
delete主表操作不会带来对子表的连带锁操作。
SQL> delete master where id=3;
1 row deleted
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM
75189
0
3
0
0
135 TX
65564
963
6
0
0
update操作，也是不会影响到子表记录。
SQL> update master set vname='df' where id=3;
1 row updated
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM
75189
0
3
0
0
135 TX
262156
999
6
0
0
那么，我们对子表进行
DML操作的时候，会连带影响到父表锁吗？下面进行DML操作实验。
SQL> insert into detail values (4,2,'d');
1 row inserted
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM
75189
0
3
0
0
135 TM
75191
0
3
0
0
135 TX
262170
1001
6
0
0
SQL> update detail set details='dkl' where did=3;
1 row updated
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM
75191
0
3
0
0
135 TX
393236
1219
6
0
0
SQL> delete detail where did=3;
1 row deleted
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM
75189
0
3
0
0
135 TM
75191
0
3
0
0
135 TX
327713
1218
6
0
0
子表操作的效果上，对子表进行
insert和delete操作时候，也是会影响到父表锁结构的。
注意：我们上面的实验，是在子表无索引的情况下进行的。如果我们建立了索引结构，上面实验有什么不同的？
SQL> create index idx_detail_mid on detail(mid);
Index created
进行上述相同实验，由于篇幅原因，不加以累述，只是针对与无索引时候的差异进行阐述。
在对主表进行操作的时候，
delete操作会引起连带的锁结构。
SQL> delete master where id=3;
1 row deleted
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (135,18) and type not in ('AE','TO');
SID TYPE
ID1
ID2
LMODE
REQUEST
BLOCK
---------- ---- ---------- ---------- ---------- ---------- ----------
135 TM
75189
0
3
0
0
135 TM
75191
0
3
0
0
135 TX
524319
1418
6
0
0
对子表进行操作的时候，有无索引引起的锁结构相同。下表表示时候有连带锁效应。
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
