【实验记录】外键加不加索引的差别

实验一：

1、创建实验用表以及数据的脚本，不创建外键索引

create table DEPT
(
  DEPTNO NUMBER(2) not null,
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13)
);

ALTER TABLE dept ADD CONSTRAINT PK_DEPT PRIMARY KEY (deptno);

create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
);
alter table EMP
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references DEPT (DEPTNO) ;

INSERT INTO dept VALUES(10,'aaa','bbb');
INSERT INTO dept VALUES(11,'aaa','bbb');
INSERT INTO dept VALUES(12,'aaa','bbb');

INSERT INTO emp(empno,deptno) values(1111,10);
INSERT INTO emp(empno,deptno) values(2222,10);
INSERT INTO emp(empno,deptno) values(3333,10);
INSERT INTO emp(empno,deptno) values(1111,11);
INSERT INTO emp(empno,deptno) values(2222,11);
INSERT INTO emp(empno,deptno) values(3333,11);
INSERT INTO emp(empno,deptno) values(4444,11);

commit;

2、查看一下数据，确认一下

select * from dept;
select * from emp;

3、会话1执行下面语句，不提交（下面语句需要对主表加RS锁）
INSERT INTO emp(empno,deptno) values(4444,12);

4、会话2执行下面语句，观察是否阻塞（没有外键索引，下面语句需要先对子表加S锁，然后再去申请主表RX锁，由于子表的S锁与子表上已有的RX锁不能共存，所以都会被阻塞）

update dept set deptno=16 where deptno=12; --阻塞
update dept set deptno=16 where deptno=11; --阻塞

终止会话2，回滚会话1；

5、创建外键索引

create index ix_emp on emp(deptno);

6、重新执行3、4步操作（有外键索引，下面语句需要先对子表加RS锁，然后再去申请主表RX锁，是否会被阻塞取决于子表需要被加RS锁的记录上是否已有RX锁，主表需要被加RX锁的记录是否有RS锁）

update dept set deptno=16 where deptno=12; --阻塞
update dept set deptno=16 where deptno=11; --没有阻塞，报主外键冲突错误

======================================================

实验二：

1、接实验一环境，执行以下语句drop index

drop index ix_emp;

2、会话1执行下面语句，不要提交，此时会话1给emp表加了RX锁

select * from emp where empno=1111 and deptno=11 for update;

3、会话2执行下面语句，查看是否阻塞

update dept set deptno=16 where deptno=12; --阻塞
update dept set deptno=16 where deptno=11; --阻塞

4、使用《【转】查看Oracle数据库阻塞》，查看数据库阻塞，sid:18 为会话1，sid:76 为会话2，可以看出此时会话2更新主表dept外键关联字段时，需要申请子表的4级锁（Share），这是一个表级共享锁，与会话1中RX锁不能共存，所以被会话1阻塞

终止会话2，回滚会话1；

5、创建外键索引

create index ix_emp on emp(deptno);

6、重新执行2、3步操作，不要提交

update dept set deptno=16 where deptno=12; --执行成功
update dept set deptno=16 where deptno=11; --没有阻塞，报主外键冲突错误

7、在第6步中第一个SQL执行成功后，不要提交，使用《【转】查看Oracle数据库阻塞》，查看数据库阻塞，sid:18 为会话1，sid:76 为会话2，可以看出此时会话2更新主表dept外键关联字段时，只需要申请子表的2级锁（RS）锁，这是一个行级共享锁，与会话1中RX锁可以共存，所以没有呗会话1阻塞

8、总结一下：外键在有无索引的情况下，更新主表外键关联字段时，需要为子表加的锁分别为2级锁（RS）和4级锁（S），在更新主表的操作不被阻塞的情况下（没有提交），如果外键没有索引，4级锁（S）是“瞬间”加上，然后就释放的，不易观察到；而在有索引的情况下，给子表加的2级锁（RS）会一直存在，直到更新主表的会话回滚或提交。

9、回过头解释一下实验一，无论在有无外键索引的情况下，子表插入数据，需要给主表加的是2级锁（RS），这个操作是否会被阻塞，要看主表中对应的记录是否存在RX锁。

 

类别： Oracle  查看评论

Link URL: http://hi.baidu.com/ljm0211/blog/item/972be1fe551ba0115d600859.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11411056/viewspace-732750/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11411056/viewspace-732750/