using Autonomous transaction

[Sam]

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

[Jusung Yang]

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
sh******@yahoo.com (Sam) wrote in message news:<56**************************@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

[Ed prochak]

sh******@yahoo.com (Sam) wrote in message news:<56**************************@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).
After every statement? You really need that detailed of a trace?
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

Is it the called procedure or something else? have you tried removing
the calls to that Proc? What is the specific error message?

Details, Man, we want details! 8^)