7 Replies Latest reply: Feb 21, 2013 2:58 PM by 991402 RSS

    distributed locking issue

    JSebastian
      We have two databases.

      Database A is the local database
      Database B is the remote database

      We have a transaction that does some stuff locally on database A and fires a trigger that does some stuff on remote database B. Part of what it does on remote database B is fire another trigger that performs an insert over a database link back to local database A. My developer says that the insert is committed.

      We are seeing sometimes that the session coming over the database link from remote database B to local database A is holding an Exclusive lock on DX (which as I understand it is a Distributed Transaction Entry lock). The remote session that holds the Exclusive DX lock is sitting in a 'SQL*Net message from client' wait state. It is blocking local sessions on database A from obtaining the DX lock.

      Unfortunately, the remote session that holds the DX Exclusive lock never gets out of the 'SQL*Net message from client' wait state and thus blocks local sessions from obtaining the DX lock.

      I'm wondering if anyone knows what could cause the remote session coming from database B to hold an Exclusive DX lock in database A if it has issued a commit as my developer tells me it does.
        • 1. Re: distributed locking issue
          Mohamed Houri
          Dear,
          Database A is the local database
          Database B is the remote database
          
          We have a transaction that does some stuff locally on database A and fires a trigger that does some stuff on remote database B. Part of what it does on remote database B is fire another trigger that performs an insert over a database link back to local database A. My developer says that the insert is committed.
          I have never been faced with such a kind of situation but what can be said is that I beleive that transaction issued in A can't commit in B and vice versa. And here you seem to have a transaction which starts on database A then go to database B and do a DML there which triggers a trigger that came back to database A and do DML there.

          You should review your transaction.

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:456820211101

          Best Regards

          Mohamed Houri
          • 2. Re: distributed locking issue
            Hemant K Chitale
            The "client" for the remote session from database B is the session on database A that fired the trigger in the first place.

            This design is fraught with difficulties. Why not have the database A session do both the local updates and have the trigger in database B do only it's own "local" update ?


            Hemant K Chitale
            • 3. Re: distributed locking issue
              JSebastian
              I cannot disagree with you there. I've told the developer that this design is a mess yet he just ignores my warnings.
              • 4. Re: distributed locking issue
                991402
                100% my case.
                I see the same behavior (w/o trigger thouh, but how the remote and back updates are triggeres is irrelevant).
                I had obzerved this DX Exclusive lock held at times on some different SYS tables, like UNDO$, CON$, PROXY_ROLE_DATA$. or indexes like I_CCOL2...
                Have anyone find the resolution?
                THanks.
                • 5. Re: distributed locking issue
                  991402
                  To all the guys who had replied to the original post.

                  You are right that this setup is a bit beyond the plain vanilla...
                  But
                  1. the logic is NOT illegal from the distributed Trans perspective (pls correct me if I'm wrong),
                  2. the updates have to happend when and where they need to happen,
                  3. atomic nature of distributed transaction have to be preserved even if updates happen on different Db (so you cannot randomly commit for convenience or to avoid this problem).

                  ==> ORACLE have to handle that setup or at least exit gracefully without an infinite lock
                  • 6. Re: distributed locking issue
                    Mark D Powell
                    We have been running distribued queries since version 7.0 and do not have the issue you describe. One of our processes inserts to a local table which fires a trigger which in turn issues an insert to a remote table. The remote table also has an insert trigger which in turn inserts into another table with a trigger till the sixth trigger inserts back into the source database. At which point Oracle returns to the program from the initial insert and the program commits or rollback. As soon as the commit is issued the program looks for the data passed back via the final insert in the chain which is passed back to the scanner which started the whole process. We have been running this process for well over a decade so I think the issue you describe is more than likely due to some type of logic error.

                    Have the developer or somone reliable review the unit of work and verify it is being done in a single transaction. Check the error handling. Verify the commit is issued by the calling program immediately after completion of the unit of work.

                    Also make sure programs that issued distributed selects commit or rollback even if no DML is performed. A distributed query is a transaction and takes a rollback (undo) segment slot.

                    HTH -- Mark D Powell --
                    • 7. Re: distributed locking issue
                      991402
                      Hi, Mark and all:
                      Thanks for the reply.

                      To make sure I am not totally crazy, I reviewed the code and made sure it worked in our testing environment. Then moved it to QA and still observed the same problem. So I don't think the code is the problem at this point.
                      I cannot check/see/confirm any configuration differences between test and qa (except the ones mentioned in ORACLE docs, like timeout or commit strength, which are the same). I guess DBAs and I are missing something.

                      Do you ahve any suggestions?

                      Again in my case:
                      A Initiating Process runs a PKG on DB A, does an update and select(s) there, which starts the transaction.
                      Some selects are fired against DB B via DBLink1, which makes it distributed transaction.
                      A PKG is called via DBlink1 in DB B.
                      This PKG runs in DB B and selects and updates in DB A via another DBLink2.
                      It also updates and inserts and selects in DB B.
                      When it returns control to the initial procedure in DB A and issues COMMIT or ROLLBACK, which supposed to complete the whole distributed transaction by 2PC.
                      Instead - at this point process hangs:
                      - the first DBlink1 (A-->B) connection is inactive w/o any locks in DB B.
                      - the second DBLink2 (B-->A) connection in DB A holds an DX Exclusive lock on some SYS object. It holds no other locks. I had observed this DX Exclusive lock held at times on some different SYS tables, like UNDO$, CON$, PROXY_ROLE_DATA$. or indexes like I_CCOL2, C_COBJ# cluster...
                      - the Initiating Process is locked by DX Exclusive lock on the same SYS object (every time - different), and holds some locks on application table(s) updated from both sides.

                      Thanks for your help.
                      VB.