ITPub博客

首页 > 数据库 > Oracle > oracle ora-60 deadlock发生在多个会话的情况

oracle ora-60 deadlock发生在多个会话的情况

原创 Oracle 作者:selectshen 时间:2017-05-26 15:15:19 0 删除 编辑

    ora-60错误可以发生在两个会话之间,即两个会话之间互相持有一个对方等待的资源(A deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). )。如果几个会话之间的资源争用是环形,这也是死锁,并且oracle同样会监测到并处理。

 

以下演示:

DB Version:11.2.0.4

建测试表:

create table scott.tb_60

(

  num NUMBER,

  txt VARCHAR2(10)

);

 

insert into scott.tb_60 values ( 1, 'First' );

insert into scott.tb_60 values ( 2, 'Second' );

insert into scott.tb_60 values ( 3, 'Third' );

 

commit;

 

会话1

更新num=1的记录

update scott.tb_60

set txt='a'

where num=1;

 

会话2

更新num=2的记录

update scott.tb_60

set txt='b'

where num=2;

更新num=1的记录,此时会话2等待会话1提交或回滚来释放num=1上的tx

update scott.tb_60

set txt='a'

where num=1;

 

会话3

更新num=3的记录

update scott.tb_60

set txt='c'

where num=3;

更新num=2的记录,此时会话3等待会话2提交或回滚来释放num=2上的tx

update scott.tb_60

set txt='b'

where num=2;

 

会话1

更新num=3的记录,此时会话1等待会话3提交或回滚来释放num=3上的tx锁,死锁产生

update scott.tb_60

set txt='c'

where num=3;

报错信息:

ORA-00060: deadlock detected while waiting for resource

 

数据库alert.log中记录错误

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/ct6601z3/ct6601z3/trace/ct6601z3_ora_6528.trc.

 

对应的trace file中记录死锁的相关信息

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-000a0005-00000c72        27     197     X             28       8           X

TX-0001001a-00000ca7        28       8     X             26     132           X

TX-00030001-00000d4e        26     132     X             27     197           X

 

session 197: DID 0001-001B-00000004     session 8: DID 0001-001C-00000002

session 8: DID 0001-001C-00000002       session 132: DID 0001-001A-00000004

session 132: DID 0001-001A-00000004     session 197: DID 0001-001B-00000004

 

Rows waited on:

  Session 197: obj - rowid = 00038F6D - AAA49tAAEAAABlkAAA

  (dictionary objn - 233325, file - 4, block - 6500, slot - 0)

  Session 8: obj - rowid = 00038F6D - AAA49tAAEAAABlkAAB

  (dictionary objn - 233325, file - 4, block - 6500, slot - 1)

  Session 132: obj - rowid = 00038F6D - AAA49tAAEAAABlkAAC

  (dictionary objn - 233325, file - 4, block - 6500, slot - 2)

...

 

 

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

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

注册时间:2014-01-05

  • 博文量
    169
  • 访问量
    1474789