ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 主外键和锁

主外键和锁

原创 Linux操作系统 作者:psufnxk2000 时间:2013-09-10 11:25:13 16777215 删除 编辑
主外键表时, 当从表有索引和没有索引时,
删除主表记录对从表加锁的模式
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 异步提交
请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    616229