Re: Oracle trigger causing a deadlock
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: mhdmehraj@xxxxxxxxx
- Date: Wed, 8 Sep 2010 08:53:09 +0100
What is that second statement trying to achieve, and why should it not
update the same row as your update statement? It looks as if you might be
trying to abuse triggers here. (Why the autonomous transaction, as well?)
On 8 Sep 2010 08:33, "Mohammed Mehraj Hussain" <mhdmehraj@xxxxxxxxx> wrote:
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:
