This content has been marked as final. Show 7 replies
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.
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.
You should review your transaction.
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
I cannot disagree with you there. I've told the developer that this design is a mess yet he just ignores my warnings.
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?
To all the guys who had replied to the original post.
You are right that this setup is a bit beyond the plain vanilla...
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
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 --
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.