Oracle外键要建立索引的原理和实验(2)

只有当唯一键或主键不被更新或删除的情况下，才不需要为外键创建索引。为子表外键创建索引可以有两个好处：
(1) 避免子表上有表锁，取而代之的是，数据库会获取索引上的行锁。
(2) 避免子表上的全表扫描。假设删除departments主表id=10的记录，如果employees子表的department_id外键没有索引，那么就会全表扫描employees子表，以确认是否存在department id=10的记录。

当满足以下两个条件时，会获取子表的表锁：
(1) 子表外键不存在索引。
(2) 修改主表的主键(例如，删除一行记录或者修改主键值)或者合并主表的多行记录。向主表插入记录不会获取子表的表锁。
只有当主键值修改完成，子表的表锁才会被放开。

效果是这样么？我们是用实验来验证。

创建测试表

SQL> create table t1 2 (id number, 3 name varchar2(1) 4 ); Table created. SQL> alter table t1 add constraint pk_t1 primary key (id); Table altered. SQL> create table t2 2 (id number, 3 t1_id number, 4 name varchar2(1) 5 ); Table created. SQL> alter table t2 add constraint pk_t2 primary key (id); Table altered. SQL> alter table t2 add constraint fk_t2 foreign key (t1_id) references t1(id); Table altered. SQL> insert into t1 values(1, 'a'); 1 row created. SQL> insert into t1 values(2, 'b'); 1 row created. SQL> insert into t2 values(1, 1, 'c'); 1 row created. SQL> commit; Commit complete. SQL> select * from t1; ID N ---------- - 1 a 2 b SQL> select * from t2; ID T1_ID N ---------- ---------- - 1 1 c

t1是主表，t2是子表，t2的t1_id列是外键，参考t1表的id主键列。

实验1

session 1删除主表id=2的记录：

SQL> delete from t1 where id = 2; 1 row deleted.

session 2删除子表id=1的记录：

SQL> delete from t2 where id = 1; 1 row deleted.

发现好像并没有像文档中描述的，删除主表一行记录，就会锁住子表整张表，这是为什么？我们先继续看实验2。

实验2

session 1删除子表id=1的记录(sid是150)：

SQL> delete from t2 where id = 1; 1 row deleted. 或update t2 set name = 'c' where id = 1;

session 2删除主表id=2的记录(sid是144)：

SQL> delete from t1 where id = 2;

这个session处于hang的状态。

SQL> select object_name from dba_objects where object_id = 76828; OBJECT_NAME T1 SQL> select object_name from dba_objects where object_id = 76830; OBJECT_NAME T2

我们可以看出session 1有两个TM锁，一个TX锁，session 2有两个TM锁。

这里隐含的知识点就是v$lock视图中ID1和ID2列的含义，Reference手册中有介绍，但基本没什么用。

参考secooler老师的文章()，参阅MOS：29787.1，得知了ID1和ID2更详细的说明：

对于TM锁来说，ID1表示表对象，ID2一般是0。

对于TX锁来说，ID1表示Decimal RBS & slot，当前事务的回滚段编号和槽位号(十进制，RBS和slot的组合，根据0xRRRRSSSS RRRR = RBS number, SSSS = slot的定义，高16位表示RBS值，对应于VTRANSACTION中的XIDUSN字段，低16位表示slot值，对应于VTRANSACTION中的XIDSLOT字段)，ID2表示Decimal WRAP number，序列号。

因此两个TM锁，其中object_id=76828对应的是T1表，object_id=76830对应的是T2表。