接獲報案,同事欲將測試網站移至新主機,遇到Oracle無法進行分散式交易的情況,得到以下錯誤訊息:

    Oracle.DataAccess.Client.OracleException
    Unable to enlist in a distributed transaction /無法列於分散式交易中

該網站尚有其他SQL分散式交易正常,單獨連線Oracle不參與分散式交易也正常,主機Oracle Client版本為ODAC121024(12.1.0.2),並確認已安裝Oracle Services for Microsoft Transaction Server。

先寫一小段程式進行對照,建立TransactionScope包入SQL及Oracle查詢,通過測試:

 using (TransactionScope tx = new TransactionScope()) 
{
    using (var cnSql = new SqlConnection("data source=SqlSvAr;user id=someone;password=****")) 
    {
        cnSql.Open();
        var cmd = cnSql.CreateCommand();
        cmd.CommandText = "select getdate()";
        var dr = cmd.ExecuteReader();
        dr.Read();
        Console.WriteLine(dr[0]);
    }
    using (var cnOra = new OracleConnection("data source=OraSvrA;user id=someone;password=****"))
    {
        cnOra.Open();
        var cmd = cnOra.CreateCommand();
        cmd.CommandText = "select sysdate from dual";
        var dr = cmd.ExecuteReader();
        dr.Read();
        Console.WriteLine(dr[0]);
    }
    tx.Complete();
}

由此可確認該主機可支援Oralce分散式交易。下一步請同事修改測試範例,逐一換成問題程式所用的連線或物件,觀察到一個現象:將測試程式所連線的Oracle Server由OraSvrA換成OraSvrB,就會重現無法參與分散式分易錯誤。

比對後發現兩台Oracle Server版本不同,OraSvrA為11.2.0.2.0,有問題的OraSvrB為10.2.0.4.0,推測可能是新版Oracle Client 12.1.0.2搭配舊版Oracle Server之相容問題,依此方向爬文,找到一篇Oracle論壇討論提到極度類似狀況,依網友測試結果,問題出現在Oracle 12新版Client連線10.2.0.3及10.2.0.4等舊版Oracle Server,升級到10.2.0.5可解決:

…My team has been able to reproduce this problem with DB 10.2.0.3, but not with DB 10.2.0.5. If it's possible, I would recommend using 10.2.0.5 if you need an immediate solution. …

… the same issue to me in Oracle 10. @10.2.4. …

至此得到結論,OraSvrB為10.2.0.4,符合論壇所說的出錯情境。

不想糾結於舊版要不要升級,決定花時間將OraSvrB上的資料庫搬至OraSvrA,問題消失,收工!


Comments

# by Phoenix

看起來怪怪的,分散式交易commit的時候,connection已經解構,正常來說,connection不是應該等到commit後才解構

# by Jeffrey

to Phoenix, 「不用刻意調整Connection配合交易」也是TransactionScope神奇之處。請參考:https://goo.gl/O4ZvIu Transactions started through System.Transactions are controlled through the System.Transactions infrastructure, and are not affected by SqlConnection.Close.

Post a comment