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