Re: using Autonomous transaction

I doubt that the AUTONOMOUS TRANSACTION has anything to do with the deadlock error. AUTONOMOUS TRANSACTION can cause deadlock error if it tries to modify the same record its parent transaction is modifying. If you are simply doing message logging in the AUTONOMOUS TRANSACTION routine, deadlock should not occurr. Most likely the deadlock is caused by the DML statements in the parent transaction. If you have the following situation, deadlock will occurr:

Session 1     Session 2
---------     --------------
delete row 1
             delete row 2
delete row 2
             delete row 1

• Jusung Yang

shyamp73_at_yahoo.com (Sam) wrote in message news:<564714ef.0308110755.31576624_at_posting.google.com>...
> Guys,
> I have a question regarding oracle.
>
> I have a stored procedure executing a number of statements including
> delete , select and lots of inserts in one big procedure and main
> transaction
>
> I have a stored procedure sp_log_action with a PRAGMA AUTONOMOUS
> TRANSACTION defined which is called after each statement in the main
> stored procedure(this writes the event to a table).
>
> When i run with one session, the main procedure, it runs as expected
> i.e even if there is a error in the main stored Proc, it logs an
> error.
>
> But when i run from multiple sessions , it returns me a "deadlock
> detected while waiting for resource" error.
>
> It seems to me a locking problem. can u suggest me a way around this.
> Or am I doing the right thing to log a event . ???
> Any suggestions will be helpful!!
>
> thanks,
> Shyam Pillai
Received on Tue Aug 12 2003 - 19:11:04 CEST