主外键和锁

主外键表时， 当从表有索引和没有索引时，

删除主表记录对从表加锁的模式

1 当有索引时，对从表加mode=3的rx锁

2 当无索引时，对从表加mode=4的share锁，

（从性能上看，子表外键列没有索引时，对主表update,delete操作时，需要对子表进行全表扫描，也是慢。

正常情况下来说，不应该存在对父表进行delete或update操作。）

create table t_parent ( id number);

create table t_children ( id number,name varchar2(10),p_id number);

alter table t_parent add  primary key (id);

alter table t_children add constraints fk_t_children foreign key (p_id) references t_parent(id);

insert into t_parent values (1);

insert into t_parent values (2);

insert into t_parent values (3);

insert into t_children values(1,'1',1);

insert into t_children values(2,'2',2);

commit;

session 1:

SQL> update t_children set id=3 where p_id=2;

1 row updated.

sessoin 2:

SQL> update t_parent set id=4 where id=3;              -- 当子表的外键列上没有索引时，这里会阻塞

sesion 3:

SQL> select * from v$lock where sid in (1,39) order by sid;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

00002B6383FB2200 00002B6383FB2260          1 TM      77203          0          3  0        272          1

00000000695128F0 0000000069512948          1 AE        100          0          4  0       3205          0

0000000068FCE8D0 0000000068FCE948          1 TX     327695       1321          6  0        272          0

00002B6383FB2200 00002B6383FB2260         39 TM      77203          0          0  4        224          0     --请求子表的共享锁

0000000069512E30 0000000069512E88         39 AE        100          0          4  0        257          0

00002B6383FB2200 00002B6383FB2260         39 TM      77202          0          3  0        224          0

6 rows selected.

create index idx_t_chilren on t_children(p_id); --建索引之后，都可以进行

SQL> select * from v$lock where sid in (1,39) order by sid;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

00000000695128F0 0000000069512948          1 AE        100          0          4  0       3422          0

00002B6383FB11C8 00002B6383FB1228          1 TM      77203          0          3  0         38          0

0000000068FCE8D0 0000000068FCE948          1 TX      65557       1124          6  0         38          0

0000000068F36580 0000000068F365F8         39 TX     655372       1151          6  0         34          0 

00002B6383FB11C8 00002B6383FB1228         39 TM      77202          0          3  0         34          0

0000000069512E30 0000000069512E88         39 AE        100          0          4  0        474          0

00002B6383FB11C8 00002B6383FB1228         39 TM      77203          0          3  0         34          0    --需要的是子表的rx锁

7 rows selected.

找以前的笔记，发现可以通过 10704 跟踪得到：（看来记性不是好）

测试过程：

--建表，插入数据

SQL> create table t1 (t1id number primary key );

Table created.

SQL> create table t2 (t2id number references t1(t1id));

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t2 values (1);

1 row created.

SQL> commit;

当无索引时：

SQL> conn song/song

Connected.

SQL> ALTER SESSION SET EVENTS '10704 trace name context forever, level 12';

Session altered.

SQL> delete from t1 where t1id=2;

1 row deleted.

SQL> alter session set events '10704 trace name context off';

Session altered.

产生的trace文件中：

[oracle@localhost trace]$ grep ksqgtl ogg11_ora_7618.trc|grep mode

ksqgtl *** CU-726cb0d8-00000000 mode=6 flags=0x10000 timeout=300 ***

ksqgtl *** TM-00012ce0-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-00012ce2-00000000 mode=4 flags=0x401 timeout=21474836 ***

ksqgtl *** TX-0004001d-0000042f mode=6 flags=0x401 timeout=0 ***

ksqgtl *** CU-726a0528-00000000 mode=6 flags=0x10000 timeout=300 ***

ksqgtl *** TM-00012ce2-00000000 mode=4 flags=0x401 timeout=21474836 ***

ksqgtl *** CU-726a0528-00000000 mode=6 flags=0x10000 timeout=300 ***

ksqgtl *** CU-730cea80-00000000 mode=6 flags=0x10000 timeout=300 ***

2 当有索引时：

--建索引

create index idx_t2 on t2(t2id);

别的过程一致

产生的trace文件中

[oracle@localhost trace]$ grep ksqgtl ogg11_ora_7457.trc|grep mode

ksqgtl *** CU-726cb0d8-00000000 mode=6 flags=0x10000 timeout=300 ***

ksqgtl *** TM-00012ce0-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TM-00012ce2-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqgtl *** TX-000a0010-000003e4 mode=6 flags=0x401 timeout=0 ***

SQL> select object_name from dba_objects where object_id=77024; 

OBJECT_NAME

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

T1

SQL> select object_name from dba_objects where object_id=77026;

OBJECT_NAME

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

T2

by song

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

转载于:http://blog.itpub.net/25099483/viewspace-772458/