在更新或删除父表主键时,如果外键上无索引,会导致子表上应用一个mode=4的表级锁(仅在语句执行期间存在,与事务无关);而如果外键上有索引,只会在子表上应用一个mode=2的表级锁。
下面通过10704事件跟踪验证。
下面通过10704事件跟踪验证。
1,测试数据准备
create table p(x int);
create table p(x int);
alter table p
add constraint pk_p primary key(x);
add constraint pk_p primary key(x);
insert into p values(1);
insert into p values(2);
insert into p values(3);
commit;
insert into p values(2);
insert into p values(3);
commit;
create table c(x int);
alter table c
add constraint fk_c_r_p foreign key(x) references p(x);
2,外键没有索引时锁情况
[oracle@bnet95 udump]$ sqlplus mh/mh
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 18 10:15:29 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select distinct sid from v$mystat;
SID
----------
777
----------
777
SQL>
SQL> alter session set events '10704 trace name context forever,level 10';
SQL> alter session set events '10704 trace name context forever,level 10';
Session altered.
SQL>
SQL> delete from p where x=1;
SQL> delete from p where x=1;
1 row deleted.
--在另外一个会话中查询锁情况:
SQL> select * from v$lock where sid=&mysid;
Enter value for mysid: 777
old 1: select * from v$lock where sid=&mysid
new 1: select * from v$lock where sid=777
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
3D673018 3D673030 777 TM 155076 0 3 0
3DB857A4 3DB858C0 777 TX 131084 60849 6 0
-------- -------- ---------- -- ---------- ---------- ---------- ----------
3D673018 3D673030 777 TM 155076 0 3 0
3DB857A4 3DB858C0 777 TX 131084 60849 6 0
SQL> select object_name from dba_objects where object_id=155076;
OBJECT_NAME
--------------------------------------------------------------------------------
P
SQL> select to_char(155076,'xxxxxxxx'), to_char(155078,'xxxxxxxx') from dual;
--------------------------------------------------------------------------------
P
SQL> select to_char(155076,'xxxxxxxx'), to_char(155078,'xxxxxxxx') from dual;
TO_CHAR(1 TO_CHAR(1
--------- ---------
25dc4 25dc6
--------- ---------
25dc4 25dc6
--回到测试会话
SQL> commit;
SQL> commit;
Commit complete.
SQL>
SQL> alter session set events '10704 trace name context off';
SQL> alter session set events '10704 trace name context off';
Session altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@bnet95 udump]$ ls -lrt
-rw-r----- 1 oracle oinstall 4504 Feb 18 10:24 bnet_ora_17754.trc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@bnet95 udump]$ ls -lrt
-rw-r----- 1 oracle oinstall 4504 Feb 18 10:24 bnet_ora_17754.trc
跟踪文件中:
*** 2013-02-18 10:23:37.197
ksqgtl *** TM-00025dc4-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db857a4, ktcdix=2147483647, topxcb=0x0x3db857a4
ktcipt(topxcb)=0x0
*** 2013-02-18 10:23:37.197
ksqgtl *** TM-00025dc6-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db857a4, ktcdix=2147483647, topxcb=0x0x3db857a4
ktcipt(topxcb)=0x0
*** 2013-02-18 10:23:37.198
ksqrcl: TM,25dc6,0
*** 2013-02-18 10:23:37.198
ksqcmi: TM,25dc6,0 mode=0 timeout=0
ksqcmi: returns 0
ksqrcl: returns 0
ksqgtl *** TM-00025dc6-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db857a4, ktcdix=2147483647, topxcb=0x0x3db857a4
ktcipt(topxcb)=0x0
*** 2013-02-18 10:23:37.198
ksqrcl: TM,25dc6,0
*** 2013-02-18 10:23:37.198
ksqcmi: TM,25dc6,0 mode=0 timeout=0
ksqcmi: returns 0
ksqrcl: returns 0
*** 2013-02-18 10:23:37.200
ksqgtl *** TM-00025dc6-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db857a4, ktcdix=2147483647, topxcb=0x0x3db857a4
ktcipt(topxcb)=0x0
*** 2013-02-18 10:23:37.200
ksqrcl: TM,25dc6,0
*** 2013-02-18 10:23:37.200
ksqcmi: TM,25dc6,0 mode=0 timeout=0
ksqcmi: returns 0
ksqrcl: returns 0
ksqgtl *** TM-00025dc6-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db857a4, ktcdix=2147483647, topxcb=0x0x3db857a4
ktcipt(topxcb)=0x0
*** 2013-02-18 10:23:37.200
ksqrcl: TM,25dc6,0
*** 2013-02-18 10:23:37.200
ksqcmi: TM,25dc6,0 mode=0 timeout=0
ksqcmi: returns 0
ksqrcl: returns 0
*** 2013-02-18 10:24:57.707
ksqrcl: TM,25dc4,0
ksqrcl: returns 0
ksqrcl: TM,25dc4,0
ksqrcl: returns 0
==>对父表p获取mode=3的TM锁;对子表c获取2次mode=4的TM锁,与该表上其他会话的dml操作冲突(由于子表无索引导致对子表全表扫描,增加了冲突发生的概率),但并未在v$lock中体现,该锁只在dml语句执行期间存在,与事务长短无关。
3,外键增加索引时锁情况
[oracle@bnet95 udump]$ sqlplus mh/mh
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 18 10:25:33 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> create index idx_c_x on c(x);
SQL> create index idx_c_x on c(x);
Index created.
SQL>
SQL> select distinct sid from v$mystat;
SQL> select distinct sid from v$mystat;
SID
----------
777
----------
777
SQL>
SQL> alter session set events '10704 trace name context forever,level 10';
SQL> alter session set events '10704 trace name context forever,level 10';
Session altered.
SQL>
SQL> delete from p where x=2;
SQL> delete from p where x=2;
1 row deleted.
--在另外一个会话中查询锁情况:
SQL> select * from v$lock where sid=&mysid;
Enter value for mysid: 777
old 1: select * from v$lock where sid=&mysid
new 1: select * from v$lock where sid=777
SQL> select * from v$lock where sid=&mysid;
Enter value for mysid: 777
old 1: select * from v$lock where sid=&mysid
new 1: select * from v$lock where sid=777
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
3D673018 3D673030 777 TM 155076 0 3 0
3D6730C4 3D6730DC 777 TM 155078 0 2 0
3DB857A4 3DB858C0 777 TX 851971 23186 6 0
-------- -------- ---------- -- ---------- ---------- ---------- ----------
3D673018 3D673030 777 TM 155076 0 3 0
3D6730C4 3D6730DC 777 TM 155078 0 2 0
3DB857A4 3DB858C0 777 TX 851971 23186 6 0
SQL> select object_name from dba_objects where object_id=155078;
OBJECT_NAME
--------------------------------------------------------------------------------
C
--------------------------------------------------------------------------------
C
--回到测试会话
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter session set events '10704 trace name context off';
SQL> alter session set events '10704 trace name context off';
Session altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@bnet95 udump]$ ls
bnet_ora_17754.trc bnet_ora_19066.trc
[oracle@bnet95 udump]$ ls -lrt
-rw-r----- 1 oracle oinstall 2726 Feb 18 10:27 bnet_ora_19066.trc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@bnet95 udump]$ ls
bnet_ora_17754.trc bnet_ora_19066.trc
[oracle@bnet95 udump]$ ls -lrt
-rw-r----- 1 oracle oinstall 2726 Feb 18 10:27 bnet_ora_19066.trc
跟踪文件:
*** 2013-02-18 10:26:44.126
ksqgtl *** TM-00025dc4-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db857a4, ktcdix=2147483647, topxcb=0x0x3db857a4
ktcipt(topxcb)=0x0
*** 2013-02-18 10:26:44.127
ksqgtl *** TM-00025dc6-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db857a4, ktcdix=2147483647, topxcb=0x0x3db857a4
ktcipt(topxcb)=0x0
==>对父表p获取mode=3的TM锁;对子表c获取mode=2的TM锁,该锁在v$lock中体现,不妨碍该表上其他会话的dml操作。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-754208/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-754208/