ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验记录】外键加不加索引的差别

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

原创 Linux操作系统 作者:ljm0211 时间:2012-06-13 23:46:39 0 删除 编辑

实验一:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    436869