471,092 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

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
Jul 19 '05 #1
2 7733
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

Jul 19 '05 #2
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^)
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Dan Loomis | last post: by
3 posts views Thread by Saket Mundra | last post: by
4 posts views Thread by Daniel Daoust | last post: by
3 posts views Thread by Jason Huang | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
2 posts views Thread by Sam | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.