sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

学习笔记:深入研究oracle死锁 自制oracle表中更新数据死锁状态

时间:2016-05-03 18:39   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

oracle某表自制死锁并触发事件引起死锁

今天遇到一个比较特殊的死锁现象,记录下来

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090022-000002ba        15     145     X             15     145           X
session 145: DID 0001-000F-00000019     session 145: DID 0001-000F-00000019
Rows waited on:
Session 145: obj - rowid = 0000E0A3 - AAAOCjAAFAAAAA8AAA
  (dictionary objn - 57507, file - 5, block - 60, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE T SET Y = Y WHERE X = :B1 
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x67987910         4  CHF.T
===================================================

问题原因:自制事件导致(重现)

drop table t;                                                                                                        
create table t ( x int, y int );
                                                                                                                 
create or replace trigger t before update on t
for each row
declare
    pragma autonomous_transaction;
begin
    update t set y = y where x = :new.x;
    commit;
end;
/
                                                                                                              
insert into t values ( 1, 1 );
commit;
update t set y = y where x = 1;


-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享,转载请尽量保留本站网址


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:深入研究oracle死锁 自制oracle表中更新数据死锁状态

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/125.html

Oracle研究中心

关键词:

自制oracle表中更新数据导致死锁状态

ORA-00060分析过程与最终解决办法