今天遇到一个比较特殊的死锁现象，记录下来
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;
