首先给出来自TOM<>P202中的一些结论:
在外键未加索引的情况下,可能带来的问题:
1 如果更新了父表的主键,由于外键上没有索引,所以子表会锁住.
2 如果删除了父表中的一行,整个子表也会被锁住.
3 如果有DELETE CASCADE,而且没有对子表加索引.那么删除父表中的每一行时都会对子表做一次全表扫描.这个全表扫描可能是不必要的,而且
如果从父表中删除多行,父表中每删除一行就要扫描一次子表.
4 从父表查询子表时,速度可能会很慢.以employee/depart为例,
select * from depart,employee where employee.deptno = depart.deptno and depart.deptno=10;
那么什么情况下不用对外键加索引呢?
1 没有从父表删除行
2 没有更新父表的唯一键或主键
3 没有从父表联结子表
测试
1 数据准备:
drop table employee;
drop table depart;
create table depart(deptno number primary key, departname varchar2(20));
insert into depart values(10, 'sales');
insert into depart values(11, 'hr');
commit;
create table employee(
id number, name varchar2(20),deptno number references depart(deptno)
on delete cascade);
insert into employee values(1,'lin',10);
insert into employee values(2,'li',10);
insert into employee values(3,'fang',11);
insert into employee values(4,'bo',11);
commit;
子表外键没有加索引的情况:
2 Delete父表数据.
delete from depart where deptno=10;
查询当前的锁情况:
select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
7 TX 262162 168 X None non-block
7 TM 29311 0 RX None non-block
7 TM 29309 0 RX None non-block
select object_name,object_id from user_objects where object_id in (29311,29309);
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------- ----------
DEPART 29309
EMPLOYEE 29311
两个TM锁,分别是对父表depart和子表employee加的表锁.锁的模式是RX.表示其它会话可以对depart的其它行做DML操作,但不能对锁定的行
deptno=10;可以对employee的其它行做DML操作,但不能对锁定的行deptno=10;
SQL> update depart set departname=departname ||'a' where deptno <> 10;
1 row updated
SQL> update employee set name=name ||'a' where deptno <> 10;
2 rows updated
另一个会话:
SQL> update employee set name=name ||'a' where deptno = 10;
hangup....
rollback;
3 Insert 的情况
SQL> insert into depart values(12,'executive');
1 row inserted
查询当前的锁情况.
select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
7 TX 589858 165 X None non-block
7 TM 29311 0 RS None non-block
7 TM 29309 0 RX None non-block
在子表上加的是RS模式的TM锁
4 Update 父表的情况
SQL> update depart set departname=departname ||'a' where deptno=10;
1 row updated
SQL>select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
7 TX 65565 187 X None non-block
7 TM 29309 0 RX None non-block
结论 1:在Employee表的deptno即外键没有索引的时候.
对父表的DML操作都会在父表上加X模式的TX锁和RX模式的TM锁
对父表的Delete操作会对子表加RX的TM锁
对父表的Insert操作会对子表加RS的TM锁
对父表的Update操作不会对子表任何的锁
只有父表Delete,即对子表加了RX锁时,才会锁住子表中对应的行.
(Insert 操作不可能对应的行,所以不会锁住)
SQL> delete from depart where deptno=10;
1 row deleted
另一个 session
SQL> delete from employee where deptno=10;
Hangup....
这与<>中的描述不一致.
对子表Employee的Delete操作
SQL> delete from employee where deptno=10;
2 rows deleted
SQL>select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
9 TX 393263 172 X None non-block
9 TM 29311 0 RX None non-block
9 TM 29309 0 RS None non-block
对子表的Insert操作
SQL> insert into employee values(5,'bo',11);
1 row inserted
SQL>select sid, type, id1,id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock
where sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- ---------- --------- ------------ ---------
9 TX 458763 166 X None non-block
9 TM 29311 0 RX None non-block
9 TM 29309 0 RS None non-block
与Delete操作一样的锁
对子表的Update操作
SQL> update employee set name=name ||'a' where deptno=10;
2 rows updated
select sid, type, id1, b.object_name, id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock, user_objects b
where b.object_id(+) = id1
and sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 OBJECT_NAME ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- --------------- ---------- --------- ------------ ---------
9 TM 29309 DEPART 0 RS None non-block
9 TM 29311 EMPLOYEE 0 RX None non-block
9 TX 458763 166 X None non-block
与Delete操作一样的锁
结论 2 :
对子表的Update/insert/delete操作,在父表和子表上加的锁都一致.
#########################################################################
现在测试对子表外键加索引的情况:
加什么索引呢? 普通索引
SQL> create index ind_deptno on employee(deptno);
Index created
delete from depart where departno=10;
结论 3:
加索引后,对父表的DML操作所加的锁与没有加索引的情况下的操作是完全一样的.
这里明显与TOM的结论不一致,TOM说加索引后,如果删除父表中的一行,不会锁住子表,实际看到的,同样会对子表加RX模式的TM锁.
这是怎么回事呢?
加索引后,对子表的Delete, Insert操作所加的锁与没有加索引的情况下的操作是完全一样的.
只有在Update的操作下有差别:
select sid, type, id1, b.object_name, id2,
decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock,
decode(block,0,'non-block',1,'blocker') block
from v$lock, user_objects b
where b.object_id(+) = id1
and sid = (select sid from v$mystat where rownum =1);
SID TYPE ID1 OBJECT_NAME ID2 HOLD_LOCK REQUEST_LOCK BLOCK
---------- ---- ---------- --------------- ---------- --------- ------------ ---------
9 TM 29311 EMPLOYEE 0 RX None non-block
9 TX 65573 187 X None non-block
没有对父表的锁
结论:
以上测试的结果与<>中Data Integrity -> types of Integrity Constraint
->Referential Integrity Constraints ->Concurrency Control, Indexes, and Foreign Keys中描述的不一致?
是哪里出问题了?
1 加的索引不对.唯一索引是不能加的.因为子表中肯定会存在多行相同的deptno.
2 没能真正理解阻塞的意思?
Notes:
1 查询没有加索引的外键
select table_name, constraint_name,
cname1 || nvl2(cname2,','|| cname2,null) ||
nvl2(cname3,','|| cname3,null) || nvl2(cname4,','|| cname4,null) ||
nvl2(cname5,','|| cname5,null) || nvl2(cname6,','|| cname6,null) ||
nvl2(cname7,','|| cname7,null) || nvl2(cname8,','|| cname8,null)
columns
from (
select b.table_name,b.constraint_name,
max(decode(position,1,column_name,null)) cname1,
max(decode(position,2,column_name,null)) cname2,
max(decode(position,3,column_name,null)) cname3,
max(decode(position,4,column_name,null)) cname4,
max(decode(position,5,column_name,null)) cname5,
max(decode(position,6,column_name,null)) cname6,
max(decode(position,7,column_name,null)) cname7,
max(decode(position,8,column_name,null)) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name, 1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name ) cons
where col_cnt > all (
select count(*) from user_ind_columns i where i.table_name = cons.table_name
and i.column_name in( cname1, cname2,cname3,cname4,cname5,cname6,cname7,cname8)
and i.column_position < cons.col_cnt
group by i.index_name
);
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13824386/viewspace-701888/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13824386/viewspace-701888/
6367
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
