This discussion is archived
3 Replies Latest reply: Jul 2, 2013 5:10 PM by scott.wesley RSS

Oracle Deadlock When Updating Collection

MarkWagoner Newbie
Currently Being Moderated

I am trying to figure out why I am getting a deadlock under the following scenario.  If anyone can shed some light it would be greatly appreciated.

 

There is an application process that is called which truncates an existing collection and repopulates it.  The update is actually performed within a package that is called by the application process, if that makes any difference.

 

After the process completes a new Apex page is loaded.  This page contains a procedure in the "before header" section that then tries to update the same collection.  This process fails, however, with an Oracle deadlock.

 

I can see that, to the database these would appear as two different transactions because the application process is called using AJAX and probably gets a different connection from the pool than the page request.  But I would expect the transaction started by the application process would be commited when it completes, freeing up the collection for the page process.

 

Can anyone explain why this isn't the case?

 

Thanks

  • 1. Re: Oracle Deadlock When Updating Collection
    scott.wesley Guru
    Currently Being Moderated

    I'm curious to know the answer to this, but to give you a better understanding of that area - this question reminds me of dmcghan's post

    http://www.danielmcghan.us/2012/08/implicit-commits-in-apex.html

     

    You might be able to look into the sessions using v$session

    where username='APEX_PUBLIC_USER'

     

    client_identifier will be something like: nobody:6419832222161

    Action: Processes - point: AFTER_HEADER

    Module: APEX_040200/APEX:APP 4550:1

    and check out some of the other columns in regard to it's state - how do you know it's a deadlock?

     

    Scott

  • 2. Re: Oracle Deadlock When Updating Collection
    MarkWagoner Newbie
    Currently Being Moderated

    Thanks for the link, Scott.  That is a very interesting post.  I tried placing a commit in the application process think that would release any locks but it doesn't seem to make a difference.

     

    I can tell I am getting a deadlock because, when the second page tries to load I get the message "ORA-00060: deadlock detected while waiting for resource"  I can also see the lock information in the v$session view, I just need to figure out how to decipher it.

     

    I am also certain that it has something to do with the collections because, if I comment out the code that manipulates them in either the application or the page process I don't get the error.

     

    I also should mention that we are on Apex 4.0.  Unfortunately we tried upgrading to 4.2 about two months ago but it broke quite a few things so that project was put on hold.

  • 3. Re: Oracle Deadlock When Updating Collection
    scott.wesley Guru
    Currently Being Moderated

    If it's collections related, it may be a bug.

     

    Perhaps put an example on apex.oracle.com, maybe one of the apex dev team can spot something.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points