3 Replies Latest reply: Aug 20, 2013 10:11 AM by Iordan Iotzov RSS

    RA-00054: resource busy and acquire with NOWAIT specified or timeout expired

    SherrieK

      In development, there are two MVs that sometimes tromp on each other.  One MV is actively in development, the other is scheduled to refresh hourly.  The hourly refresh is used in the MV in development. 

       

      When the scheduled MV refreshes successfully, it's in less than a minute.  If there is contention, I see enq: JI - contention  as a wait event.  It waits for hours before failing with the ORA-54 error. 

       

      What I'd like is when the scheduled MV kicks off, if there is contention, fail right now.  I'm confused because I thought the default was NOWAIT, which would mean just that - don't wait.  I tried using DDL_LOCK_TIMEOUT but that's not what I want, and it doesn't make a difference.

       

      This MV has these characteristics:

      COMPRESS FOR DIRECT_LOAD OPERATIONS

      NOPARALLEL

      BUILD IMMEDIATE

      REFRESH FORCE ON DEMAND

      WITH PRIMARY KEY

      ENABLE QUERY REWRITE

       

      Does anyone know of a way to make this error immediately if it can't refresh?

       

      Sherrie

        • 1. Re: RA-00054: resource busy and acquire with NOWAIT specified or timeout expired
          Iordan Iotzov

          Here is an idea (never tried it in
          MV context though):

           

           

          Find out what objects are locked by
          the refresh. Those Metalink notes can help:

           

           

          MATERIALIZED VIEW REFRESH: Locking,
          Performance, Monitoring (Doc ID 258252.1)

          Monitoring Locks During Materialized View Refreshes (Doc ID 258258.1)

           

           

           

           

          Lock explicitly (LOCK TABLE <Tab> NOWAIT or DML type lock) with
          NOWAIT the objects that are going to be locked by the MV refresh.

           

           

          If all objects are locked successfully then the MV refresh should be able
          to proceed without locking issues.

           

           

          If not, you would know right away.

           

           

           

           

          Iordan Iotzov

          • 2. Re: RA-00054: resource busy and acquire with NOWAIT specified or timeout expired
            SherrieK

            Thanks for the suggest Iordan.  Maybe I wasn't clear on my question.

             

            I know what object are locked.  My problem is two MVs are refreshing at the same time - MV A is refreshing, he depends on MV B.  Then, MV B kicks off a refresh; he waits for 7 or more hours before the resource busy error returns.  I'd like for MV B to try to refresh, as soon as he sees a resource is busy, bail out.  I'd know there is an error in less than a minute. 

             

            Sherrie

            • 3. Re: RA-00054: resource busy and acquire with NOWAIT specified or timeout expired
              Iordan Iotzov

              Sherrie,

               

               

              I understood you correctly.

               

               

              First, I do not have a simple answer – I am not aware of a
              flag or a setting that can directly solve your problem.

               

               

              Currently, the refresh of MV B hangs because the session
              waits to get a lock of resource “X”. That resource “X” is held by the session
              that performs MV A refresh, so the MV B refresh session has to wait a long
              time.

               

               

              What I am suggesting is to lock (with NOWAIT) the resource “X”
              explicitly before you start the refresh of MV B. If you are able to do that,
              you can start MV B refresh. If the resource is held by other session (MV A
              session for instance) then the explicit lock should fail immediately, so you
              can “bail out” right away.

                  

              Iordan Iotzov