Deceptive commit after select from dblink.

Recently on our russian forum we discussed about distributed transaction: is �insert /*+ append */ into x select * from t_at_dblink� a distributed transaction or not?
According to the documentation � no: "A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database"

And Tom Kyte said the same: "In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote_at_table; � we just have a single site transaction."

But v$global_transaction(but v$transaction), v$lock(type=�DX�) and another part of documentation assure that it is:

1. "Distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database."
2. "A remote statement accesses data on one remote node of a distributed database."

Who is right?

But more interesting another finding: commit after simple �select * from dblink� would be real �read-only� commit on local db, but on remote it became �read-only� rollback! Except cases when there was any dml in that transaction(with no difference local or not). But how Oracle determining: read-only or not, execute commit or rollback?

Yet another interesting thing: If we do a few times �commit� after �select from dblink�, then, as expected, rollback will be executed only once on remote. But when we closing our session, there is another commit on remote(real read-only commit).

Test cases: http://orasql.org/2012/10/13/deceptive-commit-after-select-from-dblink/ Original discussion(in russian):
http://www.sql.ru/forum/actualthread.aspx?tid=970810&pg=-1

--

Best regards,
Sayan Malakshinov
http://orasql.org
--

http://www.freelists.org/webpage/oracle-l Received on Sat Oct 13 2012 - 02:42:25 CEST