關於oracle database link,使用database link相關的查詢語句是否會開啟事務呢?我們知道,在資料庫中一個簡單的SELECT查詢語句不會產生事務(select for update會產生事務)。如下測試所示: 我們首先準備測試環境,創建了一個database link: L... ...
關於oracle database link,使用database link相關的查詢語句是否會開啟事務呢?我們知道,在資料庫中一個簡單的SELECT查詢語句不會產生事務(select for update會產生事務)。如下測試所示:
我們首先準備測試環境,創建了一個database link: LINK_NODEFINE_TEST,然後我們開始測試
CREATE PUBLIC DATABASE LINK LINK_NODEFINE_TEST
CONNECT TO TEST IDENTIFIED BY "t123$%^"
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.57.24)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gsp.localdomain)
)
)';
下麵開始演示一下database link相關的查詢是否會開啟事務:
SQL> show user;
USER is "SYS"
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
939
SQL> select xidusn, xidslot, xidsqn
2 from v$transaction, v$session
3 where saddr=ses_addr;
no rows selected
SQL> select * from kerry@link_nodefine_test;
ID NAME
---------- --------------------------------
100 kerry
SQL> select xidusn, xidslot, xidsqn
2 from v$transaction, v$session
3 where saddr=ses_addr;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 14 4122050
SQL> alter session close database link link_nodefine_test;
ERROR:
ORA-02080: database link is in use
SQL> commit; --必須要先commit,才能關閉鏈接
Commit complete.
SQL> alter session close database link link_nodefine_test;
Session altered.
下麵我們創建一個賬號TEST,測試驗證database link所指向遠程資料庫中會話的生存周期,簡單測試,你會發現即使一個簡單查詢(包含database link),會在遠程資料庫生成一個會話。而且如果不執行alter session close database link xxx關閉對應的database link的話,該會話不會銷毀,而是變成INACTVIE狀態。直到其觸發了TCP keepalive相關機制後才會被資料庫清理。
一旦你執行了database link相關的查詢, 那麼在遠程資料庫(10.20.57.24)這個測試伺服器的資料庫實例中,就會生成對應的會話,而且只有在原資料庫執行了“alter session close database link link_nodefine_test"後,對應的會話才會銷毀(當然,觸發了TCP keepalive相關機制後也會被資料庫清理)。有興趣可以自行測試。
SQL> select count(*) from v$session where username='TEST';
COUNT(*)
----------
1
SQL> select count(*) from v$session where username='TEST';
COUNT(*)
----------
0
SQL>
那麼問題來了,如果我在會話當中多次使用select * from kerry@link_nodefine_test這類包含database link的語句,是否會在10.20.57.24生成多個會話呢? 還是說這個database link相關的會話會復用呢? 下麵我們測試驗證一下:
如下所示,同一個會話當中多次使用database link查詢,不會在10.20.57.24生成多個會話。 但是如果多個不同會話中都使用database link link_nodefine_test的話,那麼就會在(10.20.57.24)中生成多個會話。
那麼如果在同一個會話中,使用不同的database link,但是這兩個database link使用相同的賬號,指向相同的伺服器,那麼這個是否也共用一個會話呢?答案是不會,而是會生成新的會話。如下測試所示
CREATE PUBLIC DATABASE LINK LINK_DEDIATED_TEST
CONNECT TO TEST IDENTIFIED BY "t123$%^"
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.57.24)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gsp.localdomain)
)
)';
關於dblink的查詢為什麼產生事務的原理分析,參考官方文檔Transaction Processing in a Distributed System
Two-Phase Commit Mechanism
A database must guarantee that all statements in a transaction, distributed or non-distributed, either commit or roll back as a unit. The effects of an ongoing transaction should be invisible to all other transactions at all nodes; this transparency should be true for transactions that include any type of operation, including queries, updates, or remote procedure calls.
The general mechanisms of transaction control in a non-distributed database are discussed in the Oracle Database Concepts. In a distributed database, the database must coordinate transaction control with the same characteristics over a network and maintain data consistency, even if a network or system failure occurs.
The database two-phase commit mechanism guarantees that all database servers participating in a distributed transaction either all commit or all roll back the statements in the transaction. A two-phase commit mechanism also protects implicit DML operations performed by integrity constraints, remot