ORACLE官方文档介绍：
Concurrency Control, Indexes, and Foreign Keys
You almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted.(你总是需要对 外键添加索引！ 唯一的例外就是：匹配的主键列 或是 唯一列 从不进行更新操作或者 删除操作)
Oracle maximizes the concurrency control of parent keys in relation to dependent foreign key values. You can control what concurrency mechanisms are used to maintain these relationships, and, depending on the situation, this can be highly beneficial. The following sections explain the possible situations and give recommendations for each.
No Index on the Foreign Key
Figure 21-8 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table.
(当子表外键上没有索引时，对父表做 删除、更新操作时，会锁定子表。插入操作不需要 锁定子表)
Unindexed foreign keys cause DML on the primary key to get a share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) on the foreign key table. This prevents DML on the table by other transactions. The SSX lock is released immediately after it is obtained. If multiple primary keys are updated or deleted, the lock is obtained and released once for each row.
Note:
See Also:
"DML Locks"
Figure 21-8 Locking Mechanisms When No Index Is Defined on the Foreign Key
Description of "Figure 21-8 Locking Mechanisms When No Index Is Defined on the Foreign Key"
Index on the Foreign Key
Figure 21-9 illustrates the locking mechanisms used when an index is defined on the foreign key, and new rows are inserted, updated, or deleted in the child table.
Indexed foreign keys cause a row share table lock (also sometimes called a subshare table lock, SS). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table.
This situation is preferable if there is any update or delete activity on the parent table while update activity is taking place on the child table. Inserts, updates, and deletes on the parent table do not require any locks on the child table, although updates and deletes will wait for row-level locks on the indexes of the child table to clear.
Figure 21-9 Locking Mechanisms When Index Is Defined on the Foreign Key
Description of "Figure 21-9 Locking
Mechanisms When Index Is Defined on the Foreign Key"
If the child
table specifies ON DELETE CASCADE, then deletes from the parent table can result in deletes from the
child table. In this case, waiting and locking rules are the same as if you
deleted yourself from the child table after performing the delete from the
parent table.
子表的外键 字段没有 索引！！！
创建测试环境
|
TEST @ prod >create table father as select * from scott.dept; Table created. TEST @ prod >create table childen as select * from scott.emp; Table created. TEST @ prod >alter table father add constraint pk_deptno primary key (deptno); Table altered. TEST @ prod >alter table childen add constraint fk_deptno foreign key (deptno) references father(deptno); Table altered. 此时子表外键 不加索引 |
查锁语句
|
创建脚本 chk_lock.sql,利用游标查询被锁定的对象及lock的模式 [oracle@cuug ~]$ vi chk_lock.sql declare v1 lock_more%rowtype; cursor c1 is select lk.sid,lk_obj.object_id,obj.object_name,DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive') lmode from v$locked_object lk_obj,dba_objects obj,v$lock lk where lk.sid=lk_obj.session_id and lk_obj.object_id=obj.object_id and lk.sid=154; begin open c1; loop fetch c1 into v1; exit when c1%notfound; insert into lock_more values(v1.locker_sid,v1.locked_objno,v1.locked_obj,v1.lock_mode); commit; end loop; end; / 创建表lock_more 存放 脚本chk_lock.sql 查询出的结果 SYS @ prod >create table lock_more (locker_sid number,locked_objno number,locked_obj varchar(10),lock_mode varchar(20));
Table created. |
父表插入数据，数据库中 锁的情况
|
SYS @ prod >insert into father values(50,'beijing','china'); 1 row created. SYS @ prod >@chk_lock.sql; PL/SQL procedure successfully completed. SYS @ prod >select * from lock_more; LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Exclusive 154 52514 FATHER Row-X (SX) 154 52514 FATHER Row-S (SS) 结论：INSERT操作会同时锁定 父表、子表 |
子表插入数据，观察该对象是否被锁
|
SYS @ prod >insert into childen values(1111,'test','test',8888,'28-SEP-81',8,0,50); 1 row created. SYS @ prod >@chk_lock.sql; PL/SQL procedure successfully completed. SYS @ prod >select * from lock_more; LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Exclusive 154 52514 FATHER Row-S (SS) 154 52514 FATHER Row-X (SX) 结论：子表插入数据，会同时锁定 父表、子表 |
父表更新数据，观察该对象 是否被锁
|
[oracle@cuug ~]$ vi ct_lock.sql declare begin for i in 4..10000 loop update father set deptno=deptno*1 ; end loop; end; /
SYS @ prod >@ct_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed. SYS @ prod >select * from lock_more;
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Row-X (SX) 154 52514 FATHER Exclusive 结论：对父表做Update操作，只会锁定父表不会锁定子表 |
子表更新数据，观察该对象 是否被锁
|
[oracle@cuug ~]$ vi ct_lock.sql declare begin for i in 4..10000 loop update childen set deptno=deptno*1 ; end loop; end; /
SYS @ prod >@ct_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed.
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Row-S (SS) 154 52514 FATHER Row-X (SX) 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Exclusive
结论：在子表执行 update操作，会同时锁定 父表、子表 |
父表删除数据,观察该对象 是否被锁
|
SYS @ prod >delete from father where deptno=50;
1 row deleted.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >select * from lock_more;
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Row-X (SX) 154 52514 FATHER Exclusive
结论：在父表执行Delete操作不提交，只会锁定父表不会锁定子表 |
子表删除数据，观察对象是否被锁
|
SYS @ prod >delete from childen where deptno=50;
1 row deleted.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >select * from lock_more;
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Exclusive 154 52514 FATHER Row-S (SS) 154 52514 FATHER Row-X (SX)
结论：在子表执行delete操作不提交，会同时锁定 父表、子表 |
子表的外键字段添加索引
|
SYS @ prod >create index ind_deptno on childen(deptno);
Index created.
SYS @ prod >analyze table childen compute statistics;
Table analyzed. |
http://www.itpub.net/forum.php?mod=viewthread&tid=1804873&page=1#pid21588601
http://www.itpub.net/forum.php?mod=viewthread&tid=1804873
http://www.itpub.net/forum.php?mod=viewthread&tid=1850815&page=2#pid22202575
http://www.itpub.net/forum.php?mod=viewthread&tid=1445975
父表插入数据，观察该对象 是否被锁
|
SYS @ prod >insert into father values(50,'beijing','china'); 1 row created. SYS @ prod >@chk_lock.sql; PL/SQL procedure successfully completed. SYS @ prod >select * from lock_more; LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Row-X (SX) 154 52514 FATHER Row-S (SS) 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Exclusive 结论：INSERT操作会同时锁定 父表、子表 |
子表插入数据，观察该对象是否被锁
|
SYS @ prod >insert into childen values(1111,'test','test',8888,'28-SEP-81',8,0,50); 1 row created. SYS @ prod >@chk_lock.sql; PL/SQL procedure successfully completed. SYS @ prod >select * from lock_more; LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Exclusive 154 52514 FATHER Row-S (SS) 154 52514 FATHER Row-X (SX) 结论：子表插入数据，会同时锁定 父表、子表 |
父表更新数据，观察该对象 是否被锁
|
[oracle@cuug ~]$ vi ct_lock.sql declare begin for i in 4..10000 loop update father set deptno=deptno*1 ; end loop; end; / . SYS @ prod >@ct_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >select * from lock_more;
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Row-X (SX) 154 52514 FATHER Row-X (SX) 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Exclusive 结论：Update操作不提交 ，会同时锁定父表、子表 |
子表更新数据，观察该对象 是否被锁
|
SYS @ prod >update childen set deptno=deptno*1 ;
14 rows updated.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >select * from lock_more;
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Exclusive 154 52514 FATHER Row-S (SS) 154 52514 FATHER Row-X (SX)
结论：在子表执行 update操作，会同时锁定 父表、子表 |
父表删除数据,观察该对象 是否被锁
|
SYS @ prod >delete from father where deptno=50;
1 row deleted.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >select * from lock_more;
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Row-S (SS) 154 52515 CHILDEN Exclusive 154 52514 FATHER Row-X (SX) 154 52514 FATHER Row-S (SS)
结论：父表删除数据 会同时锁定 父表、子表 |
子表删除数据，观察对象是否被锁
|
SYS @ prod >delete from childen where deptno=10;
3 rows deleted.
SYS @ prod >@chk_lock.sql;
PL/SQL procedure successfully completed.
SYS @ prod >select * from lock_more;
LOCKER_SID LOCKED_OBJNO LOCKED_OBJ LOCK_MODE ---------- ------------ ---------- -------------------- 154 52514 FATHER Exclusive 154 52515 CHILDEN Row-S (SS) 154 52514 FATHER Row-S (SS) 154 52514 FATHER Row-X (SX) 154 52515 CHILDEN Row-X (SX) 154 52515 CHILDEN Exclusive
结论：子表删除数据同时 锁定 主表、子表 |
结论：
|
子表外键未创建索引 |
|||
|
INSERT |
UPDATE |
DELETE |
|
|
父表 |
锁父表、子表 |
锁父表 |
锁父表、锁子表 |
|
子表 |
锁父表、子表 |
锁父表、子表 |
锁父表、子表 |
|
子表外键创建索引 |
|||
|
INSERT |
UPDATE |
DELETE |
|
|
父表 |
锁父表、子表 |
锁父表、子表 |
锁父表、子表 |
|
子表 |
锁父表、子表 |
锁父表、子表 |
锁父表、子表 |
|
外键上无索引 如果外键上没有定义索引，对父表的主键执行 DML 操作时必须获得子表上的共享行排他表级锁（share row exclusive table lock）（也称为
share-subexclusive table lock，SSX）。此锁能够阻止其他事务对子表执行 DML 操作。SSX 锁在获得后立即释放。如果父表中有多个主键值被更新或删除，对每行执行 DML 操作时都需要对子表进行一次加锁及解锁操作。 |