我觉得会,但是一直找不到相关的技术细节。
在PL/SQL Developer的sql window中做distributed query时,commit 和rollback是高亮显示的,但是后来发现在command window中执行,commit 和rollback 却没有高亮。
现在终于发现有比较好的分析了:
http://dbsnake.com/2009/07/distributed-transaction.html
"带dblink的query会起一个transaction吗?"
在PL/SQL Developer的command window中执行一个带dblink的query后,PL/SQL Developer的工具栏上的标识transaction的按钮'commit'和'rollback'没有高亮显示呢?"。
----在PL/SQL Developer的command window中执行一个transaction的时候,如果没有commit或者rollback,其工具栏上的标识transaction的按钮'commit'和'rollback'是一直会高亮显示的。
可以试了一下,的却是这样的。
下面是一些实验步骤:
不用PL/SQL Developer的command window了,直接用sqlplus,开两个session,分别为session 1和session 2。
先在session 1中删除表uplbth中的一条记录:
Session 1:
SQL> delete from uplbth where ubtbth='HPCAN081200010';
已删除1行。
接着在session 2中也做同样的删除动作,只不过在执行删除前先执行一个带dblink的query语句:
Session 2:
SQL>select count(*) from uplbth@caipratest;
COUNT(*)
----------
36
SQL> delete from uplbth where ubtbth='HPCAN081200010';
delete from uplbth where ubtbth='HPCAN081200010'
*
第1行出现错误:
ORA-02049: timeout: distributed transaction waiting for lock
在session 2大概等待了一分钟后,oracle这里报错ORA-02049,这就是最好的证明,如若"select count(*) from uplbth@caipratest"不起一个distributed transaction的话,session 2是会一直等待下去的。
这里为什么会等待一分钟,是因为distributed_lock_timeout的值是60(这也是默认值)。
好了,我们这里再来看一下为什么在PL/SQL Developer的command window中做同样的事情,session 2就会一直等待session 1?
我感觉这是因为在session 2中执行"select count(*) from uplbth@caipratest"的时候PL/SQL Developer人为的commit了一下。
好了,我们来做一个10046验证一下。
打开一个PL/SQL Developer的command window,执行"select count(*) from uplbth@caipratest",执行完了看一下产生的trace文件:
XCTEND rlbk=1, rd_only=1
WAIT #0: nam='SQL*Net message to dblink' ela= 1 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=31020960699684
WAIT #0: nam='SQL*Net message from dblink' ela= 436 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=31020960700143
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=31020960700211
WAIT #0: nam='SQL*Net message from client' ela= 16592 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=31020960716827
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=56 ct=47 lid=56 tim=31020960716966 hv=356401299 ad='78651b38'
begin :id := sys.dbms_transaction.local_transaction_id; end;
在产生的trace文件里,我们看到了XCTEND rlbk=1, rd_only=1,这表示"select count(*) from uplbth@caipratest"已经用到了回滚段了,也就是产生了一个transaction,并且这个transaction是read only的。
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.SID=&sid)
ORDER BY piece ASC;
transaction with the introducton of automatic undo management mode in Oracle9i. And if we could still user the following query to get (rbs, slot, seq) and compare it with the
result (xidusn, xidslot, xidsqn) from v$transaction.
select username, v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq, lmode, request, block
from v$lock, v$session
where v$lock.type='TX'
and v$lock.sid=v$session.sid
and v$session.username=USER
LOCAL_TRANSACTION_ID
-----------------------
13.36.60919
ops$tkyte@ORA920> select XIDUSN, XIDSLOT,XIDSQN from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
13 36 60919
but yes, it would work with UNDO tablespaces since they are just rollback segments in disguise.
Session altered.
SQL> select * from t;
X
----------
1
SQL> SELECT dbms_transaction.local_transaction_id FROM dual;
LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
16.81.7410
/* So far so good -- I am in the transaction */
SQL> commit;
Commit complete.
SQL> alter session set isolation_level=read committed;
Session altered.
SQL> select * from t;
X
----------
1
SQL> SELECT dbms_transaction.local_transaction_id FROM dual;
LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
/* How come ? I am not in a transaction ? Or a transaction running in the READ COMMITTED mode is
not quite a transaction */
SQL> commit;
Commit complete.