没有索引的外键

在更新或删除父表主键时，如果外键上无索引，会导致子表上应用一个mode=4的表级锁（仅在语句执行期间存在，与事务无关）；而如果外键上有索引，只会在子表上应用一个mode=2的表级锁。
下面通过10704事件跟踪验证。

1,测试数据准备
create table p(x int);

alter table p
  add constraint pk_p primary key(x);
 

insert into p values(1);
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

SQL>
SQL> alter session set events '10704 trace name context forever,level 10';

Session altered.

SQL>
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

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;

TO_CHAR(1 TO_CHAR(1
--------- ---------
    25dc4     25dc6

 

--回到测试会话
SQL> commit;

Commit complete.

SQL>
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

跟踪文件中:
*** 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

*** 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

*** 2013-02-18 10:24:57.707
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);

Index created.

SQL>
SQL> select distinct sid from v$mystat;

       SID
----------
       777

SQL> 
SQL> alter session set events '10704 trace name context forever,level 10';

Session altered.

SQL>
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

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

SQL> select object_name from dba_objects where object_id=155078;

OBJECT_NAME
--------------------------------------------------------------------------------
C

 

--回到测试会话
SQL>
SQL> commit;

Commit complete.

SQL>
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

跟踪文件:
*** 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/