Deadlock detected while using pragma autonomous transaction

[mahesh736]

Hi,

I am trying to update a table in an error logging routine that uses PRAGMA AUTONOMOUS_TRANSACTION.
The procedure which calls this function is run in a multi-threaded mode.(2 programs run simultaneously)
When I try to update this table, I get the error
"ORA - 00060: deadlock detected while waiting for resource".

The row that is being updated in the 2 programs is different. So is it because of a table level lock ??

Could anyone help me to resolve this error? If more info. is needed, pl. let me know..

Thanks
Mahesh

 

[Wonboodoo]

A deadlock is not defined by two sessions updating the same row simultaneously.

A deadlock is defined by session A trying to lock row(s) X which program B has locked at the same time as program B is trying to lock row(s) Y which session A has locked. i.e. both sessions A & B are waiting on each other.

May be partly because of a table-level lock, but it may be other locks as well. A table-level lock should only block one session (the other will successfully get the lock). It takes multiple locks (at least one row in each session) for a deadlock to occur.

 

[mahesh736]

The problem was resolved by removing an UPDATE statement from the PRAGMA AUTONOMOUS_TRANSACTION and doing it in the main cursor loop.
So the conclusion I came to was that PRAGMA AUTONOMOUS transaction acquires table-level lock. Am I correct ??

The original code with the UPDATE statement included in an PRAGMA AUTONOMOUS_TRANSACTION worked when only 1 instance of the program was running.

Mahesh

 

[sem]

No table-level locks. If some statement is executed within autonomous transaction, it's really executed there and doesn't share locks with parent transaction. So if you try to lock the same resource (probably row rather than whole table) in both, you obtain deadlock, as your child transaction will wait for release forever.

Regards, Dima