using Autonomous transaction

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 Mon Aug 11 2003 - 17:55:26 CEST