Oracle trigger causing a deadlock
- From: Mohammed Mehraj Hussain <mhdmehraj@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Sep 2010 13:01:17 +0530
i am executing an update statement
*SQL> update test set Fin_chk=0;
updatet est set Fin_chk=0;
*
ERROR at line 1:
ORA-20000: ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "Bobb.BF_AU_TEST", line 98
ORA-04088: error during execution of trigger'Bobb.BF_AU_TEST'*
i found the following messages in the trace file.
this BF_AU_TEST is a after update autonomous trigger on the test table
*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-0001001f-00007741 15 199 X 15 199 X
session 199: DID 0001-000F-00000047 session 199: DID 0001-000F-00000047
Rows waited on:
Session 199: obj - rowid = 00002E69 - AAAC5pAAEAAACcHAAH
(dictionary objn - 11881, file - 4, block - 9991, slot - 7)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE TEST SET TIME=SYSDATE WHERE FIN_CHK=-1 AND NAME=:B1 AND DT_TM
=(SELECT DT_TM FROM (SELECT DT_TM FROM TEST WHERE FIN_CHK=-1 AND
NAME=:B1 AND DT_TM< :B2 ORDER BY DT_TM DESC) WHERE ROWNUM=1)
----- PL/SQL Call Stack -----
object line object
handle number name
1F9E2DEC 377 Bobb.BF_AU_TEST*
I have this statement in the after update trigger
UPDATE TEST SET TIME=SYSDATE WHERE FIN_CHK=-1 AND NAME=:B1 AND DT_TM
=(SELECT DT_TM FROM (SELECT DT_TM FROM TEST WHERE FIN_CHK=-1 AND NAME=:B1
AND DT_TM< :B2 ORDER BY DT_TM DESC) WHERE ROWNUM=1)
when i comment this statement in the after update BF_AU_TEST Trigger, My
update is successful.
Other related posts:
