外键引发的异常

      2014 02 24 23：15接到公司电话，生产线出现无法运行现象、请一线支援ping 数据库服务器反馈说无法ping通。 立马起床穿衣、飞奔办公室。
      23：35抵达办公司，SA组成员还没有到；检查发现服务器没有问题（很怀疑一线支援啊！)；
      23：40联系生产部，反馈说异常时间段22：57--23：25出现工站无法扫描.....(抱怨一大堆！)
      
     检查监控系统，CPU/MEM/IO正常，主干网络正常...

     DB分析：
          活动事务达到100+。
       1.分析22:50--23:50之间DB出现大量TM/TX资源锁等待问题
               
         2.ASH报表


     3.对象"2063315"存在外键，但外键栏位没有建立索引。


关于外键：
     如果外键没有被索引时， 当在主键表上执行某些DML时， Oracle会在外键上加4级的TM lock。这种情况经常会导致TM的等待、死锁等。
如果外键上有索引时，它请求的是3级锁，3级锁与3级锁之间不会造成阻塞。

请参考下面的test case, 及下面的Note
Locking and Referential Integrity (Doc ID 33453.1)


Test case 1: 测试在外键没有被索引的情况下， TM锁的请求级别
0. 准备数据

SQL> drop table t2;
SQL> drop table t1;
SQL> create table t1(id number primary key);
SQL> begin
for i in 1..11 loop
       insert into t1 values(i);
end loop;
end;
SQL> commit;
SQL> create table t2(t1id number, id number primary key);
SQL> alter table t2 add constraint f_t1id foreign key(t1id) references t1(id);
SQL> begin
       for i in 1..10 loop
               insert into t2 values(i,i);
               insert into t2 values(i,i*11);
       end loop;
end;
SQL> commit;

开始测试TM lock
--------------------------
1. Session 1中删除外键表的t1id=10的数据:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL>  delete from t2 where t1id=10;
2 rows deleted.

2. 在Session 2中删除主键表中id=11的数据
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL>  delete from t1 where id=11;
这个session挂起。

3. 在session 1中查询v$lock:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select * from v$lock where type='TM';
  SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
------ -- ---------- ---------- ---------- ---------- ---------- ----------
  159 TM      59821          0          2          0         42          0
  159 TM      59827          0          3          0         42          1 <<===session 1以3级模式持有TM锁
  151 TM      59821          0          3          0          9          0
  151 TM      59827          0          0          4          9          0 <<===session 2以4级模式申请TM锁，被session 1阻塞

SQL> select owner,object_name,object_type from dba_objects where object_id=59827
OWNER OBJECT_NAM OBJECT_TYPE
------------------------------ ---------- -------------------
SYS T2 TABLE

4. 在session 3中对t2作DML语句，都会被session2阻塞。
SQL> update t2 set id=102 where id=10;


Test case 2: 在外键上创建索引时， TM锁的申请行为。
SQL> create index t2_fk on t2(t1id);

作与上例同样的实验：

1. Session 1中删除外键表的t1id=10的数据:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL>  delete from t2 where t1id=10;
2 rows deleted.
2. 在Session 2中删除主键表中id=11的数据
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL>  delete from t1 where id=11;
1 row deleted.
没有挂起。

SQL> select * from v$lock where type='TM';
      SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
      159 TM      59821          0          2          0         78          0
      159 TM      59827          0          3          0         78          0
      151 TM      59821          0          3          0         45          0 <<===session 2不会申请4级TM锁。
      151 TM      59827          0          2          0         45          0

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

转载于:http://blog.itpub.net/24867586/viewspace-1090742/