父表、子表主外键关系

子表的外键字段没有索引！！！

外键上无索引

如果外键上没有定义索引，对父表的主键执行 DML 操作时必须获得子表上的共享行排他表级锁（share row exclusive table lock）（也称为 share-subexclusive table lock，SSX）。此锁能够阻止其他事务对子表执行 DML 操作。SSX 锁在获得后立即释放。如果父表中有多个主键值被更新或删除，对每行执行 DML 操作时都需要对子表进行一次加锁及解锁操作。实验结果 只对子表加 S 锁

如果外键上定义了索引，则对父表的主键执行 DML 操作时只需获得子表上的行共享表级锁（row share table lock）（也称为 subshare table lock，SS）。此锁不允许其他事务排他地对子表加锁，但允许对父表及子表执行 DML 操作。已确认

源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1472599>

创建测试环境

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.

此时子表外键不加索引

脚本1，对测试表进行 10000000 的UPDATE 操作，模拟并发事务

[oracle@cuug ~]$ vi test_sql.sql

declare

begin

for i in 4..10000000 loop

update t1 set n1=n1*1 where n1=1;

end loop;

end;

/

脚本2，专门对于SSX锁(锁获得以后就释放，不容易捕捉，所以使用脚本)

[oracle@cuug ~]$ vi test_sql1.sql

declare

v1 char(10);

begin

for i in 1..1000000 loop

select locked_mode into v1 from v$locked_object where object_id=52886;

insert into t3 values(v1);

commit;

end loop;

end;

/

查锁语句

SELECT LK.SID,SE.USERNAME,SE.OSUSER,SE.MACHINE,

DECODE(LK.TYPE,'TX','Transaction','TM','DML','UL','PL/SQL User Lock',LK.TYPE) LOCK_TYPE,

DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(LK.LMODE)) MODE_HELD,

DECODE(LK.REQUEST,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',

TO_CHAR(LK.REQUEST)) MODE_REQUESTED,TO_CHAR(LK.ID1) LOCK_ID1,TO_CHAR(LK.ID2) LOCK_ID2,

OB.OWNER,OB.OBJECT_TYPE,OB.OBJECT_NAME,LK.BLOCK,SE.LOCKWAIT

FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE

WHERE LK.TYPE IN ('TM', 'UL')

AND LK.SID = SE.SID

AND LK.ID1 = OB.OBJECT_ID(+);

父表插入数据，观察该对象 是否被锁

TEST @ prod >insert into father values(50,'beijing','china');

1 row created.

TEST @ prod >col object_id for 99999

TEST @ prod >col object_name for a10

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52874 CHILDEN 159 2

52873 FATHER 159 3

结论：INSERT操作会同时锁定父表、子表

结果二：

159 TEST oracle cuug DML Row-X (SX) None 52873 0 TEST TABLE FATHER 0

159 TEST oracle cuug DML Row-S (SS) None 52874 0 TEST TABLE CHILDEN 0

子表插入数据，观察该对象是否被锁

TEST @ prod >insert into childen values(1111,'test','test',8888,'28-SEP-81',8,0,50);

1 row created.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 3

52785 FATHER 159 2

结论：子表插入数据，会同时锁定父表、子表

159 TEST oracle cuug DML Row-S (SS) None 52873 0 TEST TABLE FATHER 0

159 TEST oracle cuug DML Row-X (SX) None 52874 0 TEST TABLE CHILDEN 0

父表更新数据，观察该对象 是否被锁

TEST @ prod >update father set deptno=deptno*1 ;

5 rows updated.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52785 FATHER 159 3

结论：Update操作，只会锁定父表不会锁定子表

子表更新数据，观察该对象 是否被锁

TEST @ prod >update childen set deptno=deptno*1 ;

14 rows updated.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 3

52785 FATHER 159 2

结论：在子表执行 update操作，会同时锁定父表、子表

父表删除数据,观察该对象 是否被锁

TEST @ prod >delete from father where deptno=50;

1 row deleted.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52785 FATHER 159 3

结论：在父表执行Delete操作不提交，只会锁定父表不会锁定子表

子表删除数据，观察对象是否被锁

TEST @ prod >delete from childen where deptno=50;

0 rows deleted.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 3

52785 FATHER 159 2

结论：在子表执行delete操作不提交，会同时锁定父表、子表

子表的外键字段添加索引

TEST @ prod >create index ind_deptno on childen(deptno);

Index created.

TEST @ 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

父表插入数据，观察该对象 是否被锁？？？

TEST @ prod >insert into father values(50,'beijing','china');

1 row created.

TEST @ prod >col object_id for 99999

TEST @ prod >col object_name for a10

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 2

52785 FATHER 159 3

结论：INSERT操作会同时锁定父表、子表

子表插入数据，观察该对象是否被锁

TEST @ prod >insert into childen values(1111,'test','test',8888,'28-SEP-81',8,0,50);

1 row created.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 3

52785 FATHER 159 2

结论：子表插入数据，会同时锁定父表、子表

父表更新数据，观察该对象 是否被锁？？？？

TEST @ prod >update father set deptno=deptno*1 ;

5 rows updated.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52785 FATHER 159 3

结论：Update操作不提交，只会锁定父表不会锁定子表

子表更新数据，观察该对象 是否被锁

TEST @ prod >update childen set deptno=deptno*1 ;

14 rows updated.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 3

52785 FATHER 159 2

结论：在子表执行 update操作，会同时锁定父表、子表

父表删除数据,观察该对象 是否被锁

TEST @ prod >delete from father where deptno=50;

1 row deleted.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 2

52785 FATHER 159 3

结论：

子表删除数据，观察对象是否被锁

TEST @ prod >delete from childen where deptno=50;

0 rows deleted.

TEST @ prod >select lock1.object_id,obj.object_name,lock1.session_id,lock1.locked_mode from v$locked_object lock1 ,dba_objects obj where lock1.session_id=159 and lock1.object_id=obj.object_id;

OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE

--------- ---------- ---------- -----------

52786 CHILDEN 159 3

52785 FATHER 159 2

结论：

结论：

子表外键未创建索引
	INSERT	UPDATE	DELETE
父表	锁父表、子表	锁父表	锁父表
子表	锁父表、子表	锁父表、子表	锁父表、子表

子表外键创建索引
	INSERT	UPDATE	DELETE
父表	锁父表、子表	锁父表、子表	锁父表、子表
子表	锁父表、子表	锁父表、子表	锁父表、子表

外键上无索引

如果外键（foreign key）上没有定义索引，当父表（parent table）中的数据行被更新或删除时，Oracle 采用的锁机制（locking mechanism）如图 21-8 所示。而向父表中插入数据时无需对子表（child table）加锁。

如果外键上没有定义索引，对父表的主键执行 DML 操作时必须获得子表上的共享行排他表级锁（share row exclusive table lock）（也称为 share-subexclusive table lock，SSX）。此锁能够阻止其他事务对子表执行 DML 操作。SSX 锁在获得后立即释放。如果父表中有多个主键值被更新或删除，对每行执行 DML 操作时都需要对子表进行一次加锁及解锁操作。

如果外键上定义了索引，则对父表的主键执行 DML 操作时只需获得子表上的行共享表级锁（row share table lock）（也称为 subshare table lock，SS）。此锁不允许其他事务排他地对子表加锁，但允许对父表及子表执行 DML 操作。

有索引

TEST @ prod >select * from v$lock where sid=142;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- -- ---------- ---------- ---------- ----------

2D1C3F90 2D1C3FA8 142 TM 52883 0 3 0 143 0

2D1C403C 2D1C4054 142 TM 52886 0 2 0 143 0

2D231408 2D231524 142 TX 131095 4971 6 0 143 0

没索引

TEST @ prod >select * from v$lock where sid=142;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- -- ---------- ---------- ---------- ----------

2D1C3F90 2D1C3FA8 142 TM 52883 0 3 0 275 0

2D1C403C 2D1C4054 142 TM 52886 0 4 0 0 0

2D231408 2D231524 142 TX 393235 4991 6 0 275 0

ꄴ前一个：无

ꄲ后一个：无

父表、子表 主外键关系

全部评论 (0)

父表、子表主外键关系