3 Replies Latest reply on Jul 3, 2013 12:10 AM by swesley_perth

    Oracle Deadlock When Updating Collection

    Mark Wagoner

      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
          swesley_perth

          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
            Mark Wagoner

            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
              swesley_perth

              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.