Wednesday, August 06, 2008

PRAGMA => ORA-00060: deadlock

When We write PL/SQL and use "PRAGMA AUTONOMOUS_TRANSACTION".
try to warn about DELETE/UPDATE in PRAGMA + out PRAGMA:

Example:

SQL> connect scott

SQL> update test set DESCRIPTION='TEST' where id=1;

1 row updated.

SQL> update test set DESCRIPTION='TEST2' where id=1;

1 row updated.

SQL> rollback;

That's OK.

After that try to test "PRAGMA":

SQL> connect scott

SQL> update test set DESCRIPTION='TEST' where id=1;

1 row updated.

SQL>
SQL> DECLARE
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 update test set DESCRIPTION='TEST2' where id=1;
5 COMMIT;
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


...

2 comments:

Dario Palumbo said...

Hi dear.
I have a problem in a situation that ,i think, is described by your post.

I need to call, in a trigger, java stored procedure that try to execute DML on table of trigger.

I make trigger an AUTONOMOUS TRANSACTION, but now i have ORA-00060: deadlock error.

ideas?
Tnx from Italy

HunterX said...

AUTONOMOUS TRANSACTION + ORA-00060: deadlock error

I think this cause developer you should check and try to commit/rollback often.

we can protect this problem with wrong architecture program.


good luck.