ITPub博客

首页 > 数据库 > Oracle > 外键引发的异常

外键引发的异常

原创 Oracle 作者:ygzhou518 时间:2014-02-26 11:01:31 0 删除 编辑

      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中对t2DML语句,都会被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
不会申请4TM锁。
      151 TM      59827          0          2          0         45          0


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

请登录后发表评论 登录
全部评论

注册时间:2011-02-11

  • 博文量
    167
  • 访问量
    366813