Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00060: deadlock detected while waiting for resource ON INSERT

MPLNov 7 2011 — edited Nov 8 2011
I am running into a deadlock while INSERTING records into a table. This seems strange to me. I've tracked it down to the following, and it feels like an Oracle bug to me, so I wanted to get another opinion:

I have a procedure that inserts records into a table.
That table has a Before INSERT OR UPDATE OR DELETE FOR EACH ROW trigger on it.
The trigger has an IF UPDATING section in it; this section updates records in other tables related to this table.
I have confirmed that the IF UPDATING section is not firing (which makes sense because I am inserting, but since I was getting a deadlock, it seemed to make sense that somehow the updating section was firing).

Here's the part that makes this feel buggy:
I have been able to reproduce the deadlock pretty much on command.
I commented out the IF UPDATING section of the trigger, compiled the trigger, and then put the IF UPDATING section of the trigger back in, and compiled it. Note there was no net code change.
I ran my procedure that inserts records and experienced NO DEADLOCK.

However, if I compile the original version of the trigger (before I commented code out and put it back in), the deadlock reappears. To make the deadlock go away, I have to re-comment out the code, compile, put the code back, and compile again.

I know this sounds crazy. I'm running Oracle Database 11g Release 11.2.0.2.0.

Has anyone ever seen something like this before?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 6 2011
Added on Nov 7 2011
5 comments
3,138 views