oracle锁等待/死锁原因分析

1。模拟死锁

1。1。主表

-- Create table
create table WDZ1
(
  WDZ1ID NUMBER not null,
  MEMO   VARCHAR2(20)
)
;
alter table WDZ1
  add constraint XXXXXX primary key (WDZ1ID);

 

1。2。从表（没有外健的索引）

-- Create table
create table WDZ2
(
  WDZ2ID NUMBER not null,
  WDZ1ID NUMBER,
  MEMO   VARCHAR2(20)
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table WDZ2
  add constraint XXXXX primary key (WDZ2ID)
;
alter table WDZ2
  add constraint XXX foreign key (WDZ1ID)
  references WDZ1 (WDZ1ID);

 
1。3。插入数据表到住表

begin
insert  into wdz1 values (1,'aa');
insert  into wdz1 values(2,'aa2');
insert  into wdz1 values (3,'aa3');

insert into wdz2 values(10,3,'wdz3--1');
commit;
end;

 

1。4。在一个数据库seeesion里面插入数到从表，但是不提交事务

begin
  update wdz2  set memo='update wdz2 momo'

   where wdz2id=10;

insert into wdz2 values(20,2,'wdz2--1');

end;

 


对从表进行插入/修改记录，施加的锁也就是行级锁 

1。5。在另外一个数据库seeesion里面删除 主表数据

delete from wdz1 where wdz1id=1

 

这时候 程序会死锁，除非 上面的 对从表的 数据操作提交事务或者回滚事务。

2。具体原因分析

一个数据表的外键主要有3种方式来维护它自己和主表数据的一致性。

(1)delete cascade

例子如下：

alter table WDZ2
  add constraint XXX foreign key (WDZ1ID)
  references WDZ1 (WDZ1ID) on delete cascade;

 

(2)Set null

例子如下：

alter table WDZ2
  add constraint XXX foreign key (WDZ1ID)
  references WDZ1 (WDZ1ID) on delete set null;

 

(3)No action

注意，这是oracle外键使用时候的默认选项。 

例子如下：

alter table WDZ2
  add constraint XXX foreign key (WDZ1ID)
  references WDZ1 (WDZ1ID);

 

以前出现死锁主要是我们认为，在 1。4。会对数据表wdz2进行施加行级锁，但是从表(wdz2)的外健是No action，删除主表(wdz1)不会去访问从表，更不会去锁定 wdz2表的记录或者对整个数据从表(wdz2)施加表级锁。事实上 oralce的No action 选项的字面意思欺骗了我们，oracle在删除主表的时候

会去寻找所有以主表的主键作为外键的数据表，然后看去看从表

是否有该外键的索引，如果没有则会对整个从表施加表级锁，然后

对从表进行全表扫描。当然如果从表存在外键的索引，会去访问

对应的索引，而不会对从表本身进行加锁。

3。解决办法
为从表的外键建立索引，这也是在使用外键的推荐方式。
这样就能避免上面出现的问题了。