Recently on our russian forum we discussed about distributed transaction: is “insert /*+ append */ into x select * from t@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@table; – we just have a single site transaction.
But v$global_transaction(but v$transaction), another part of documentation and v$lock(type=’DX’) assure that it is.
Distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database.
A remote statement accesses data on one remote node of a distributed database.
Who is right?
But it not so interesting as my another finding: commit after simple “select * from dblink” would be “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).
Tests below:
Test table for local and remote:
create table my_dual as select * from dual;
Test 1:
Check that there are no distributed transaction on local:
DB11G/XTENDER> select * from v$lock l where l.type='DX' and sid=sys_context('userenv','sid');
no rows selected
Elapsed: 00:00:00.25
DB11G/XTENDER> save show_dx
Created file show_dx.sql
DB11G/XTENDER> select
2 gt.FORMATID
3 ,gt.BRANCHID
4 ,gt.BRANCHES
5 ,gt.REFCOUNT
6 ,gt.PREPARECOUNT
7 ,gt.STATE
8 ,gt.FLAGS
9 ,gt.COUPLING
10 from v$global_transaction gt;
no rows selected
Elapsed: 00:00:00.08
DB11G/XTENDER> save show_global
Created file show_global.sql
Check the same on remote:
ORASQL/XTENDER> @show_global no rows selected Elapsed: 00:00:00.17 ORASQL/XTENDER> @show_dx no rows selected Elapsed: 00:00:00.19
Query to remote:
DB11G/XTENDER> select * from my_dual@orasql_trace;
D
-
X
1 row selected.
Elapsed: 00:00:03.30
DB11G/XTENDER> @show_dx
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EF4600C 2EF46038 68 DX 31 0 4 0 0 0
1 row selected.
Elapsed: 00:00:00.03
DB11G/XTENDER> @show_global
FORMATID BRANCHID BRANCHES REFCOUNT PREPARECOUNT STATE FLAGS COUPLING
---------- ---------- -------------------- ------------ ---------------------------- -------- ---------------
306206 0000 1 1 0 [ORACLE COORDINATED]ACTIVE 512 TIGHTLY COUPLED
1 row selected.
Elapsed: 00:00:00.00
Check same on remote:
ORASQL/XTENDER> @show_dx
no rows selected
Elapsed: 00:00:00.29
ORASQL/XTENDER> @show_global
FORMATID BRANCHID BRANCHES REFCOUNT PREPARECOUNT STATE FLAGS COUPLING
---------- ------------------------------------------- -------- -------- ------------ --------------------------- ------- ---------------
306206 44423131475B31382E33322E3137325D5B312E345D 1 1 0 [ORACLE COORDINATED]ACTIVE 512 TIGHTLY COUPLED
1 row selected.
Elapsed: 00:00:00.16
Now commit on local:
DB11G/XTENDER> commit; Commit complete. Elapsed: 00:00:00.11
Quote from this session trace on remote:
[xtender@orasql test1]# grep XCTEND orasql11_ora_3638.trc XCTEND rlbk=1, rd_only=1, tim=1350080181411311 XCTEND rlbk=0, rd_only=1, tim=1350080201621075
Rollback was when we executed commit, and “commit” was on exit from sql*plus.
Test 2:
DB11G/XTENDER> begin 2 for r in (select * from my_dual@orasql_trace connect by level<=200) loop 3 commit; 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.00 DB11G/XTENDER> rollback; Rollback complete. Elapsed: 00:00:00.00 DB11G/XTENDER> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Grep from trace:
[xtender@orasql]# grep XCTEND orasql11_ora_4436.trc XCTEND rlbk=1, rd_only=1, tim=1350087023168375 XCTEND rlbk=0, rd_only=1, tim=1350087031551392