Re: How to get the statment sql that caused the lock

I tested with Oracle 11.2.0.4, the latest version that I administered. There was a distributed transaction containing a join with a remote table. Transaction failed because of the network glitch. Remote table remained locked. At no point was any update done to the remote table. The problem is that with remote tables there is something called "2PC" or 2 phased commit. If "prepare for commit" issued, but not followed by "commit",  the remote objects may remain locked. And that is your scenario. This has to be resolved by using DBMS_TRANSACTION package.

Regards

On 10/29/18 9:55 AM, Andy Sayer wrote:
> “Oh yes, queries in the distributed queries do lock tables because it is
> not possible to ensure that the query will return only the data
> committed before the start of the query without a lock.  You must have a
> join with a remote table, but yes, it does happen.”
>
> Maybe the last time you tested this was in a very old version which
> suffered from this problem but this is simply not what happens now.
> Regular MVRC works fine over a DB link.
>
> Or maybe the remote database was a different RDBMS.
>
> If you have a test case then I’d be very interested in seeing it.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l