oracle死锁原因分析

oracle死锁原因分析

 

分析原因：最近在做个一个小项目，要做百万级的测试数据，开始想想很简单，让写个循环插入程序就OK了，真正实战发现，程序执行插入语句时

不论是用jdbc还是hibernate的方法，都无法插入数据，问题见 http://topic.youkuaiyun.com/u/20100317/23/826ba624-4e0e-4cd4-b74a-f12db2dd1d05.html

真是奇怪。只要一做插入或者删除操作后，就会发生程序执行宕机的情况，一直执行，也不报错，直到超时。虽然问题目前(2010-03-17)没有解决,但还是把相关资料贴下来学习。

 

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

 

--查询表是否被锁定
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode
from v$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
--
--alter system kill session 'sid,serial#'; 解锁命令