SQL與ORACLE處理IsolationLevel.Serializable的行為差異

接觸ORACLE之前,玩過好一陣子的SQL,對Serializable隔離等級的認知是:

在隔離範圍內,所有讀取資料的動作都會產生鎖定,將資料保護起來避免外界更動

雖然Serializable隔離等級的代價高昂,對效能殺傷力極大應盡量避免,但因其執行邏輯簡單不易出錯,還是會有其適用時機。

以下是在SQL上實現Serializable隔離的示範: 使用Visual Studio逐行偵錯功能,在兩次讀取間設定中斷點,中斷時用SSMS試圖更動同一筆資料,就可發現UPDATE指令會擋住,要等到.NET程式完成或放棄Transaction後才會繼續執行,驗證鎖定的存在。

    private void TestSqlLock()
    {
        string cnStr = 
            "Data Source=(local);Integrated Security=SSPI;Initial Catalog=Playground;";
 
        TransactionOptions txo = new TransactionOptions();
        txo.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
        txo.Timeout = new TimeSpan(0, 1, 0);
        using (TransactionScope tx = 
            new TransactionScope(TransactionScopeOption.Required, txo))
        {
            using (SqlConnection cn = new SqlConnection(cnStr))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(
                    "SELECT Enabled FROM PlayerRec WHERE Player='Jeffrey'", cn);
                SqlDataReader dr = cmd.ExecuteReader(); //第一次讀取
                dr.Read();
                string s1 = dr[0].ToString();
                dr.Close();
                //在此設Debug中斷點,此時另開SSMS下指令
                //UPDATE PlayerRec SET Enabled='Z' WHERE Player='Jeffrey',會呈現等待狀態
                dr = cmd.ExecuteReader(); //再次讀取
                dr.Read();
                string s2 = dr[0].ToString();
                Response.Write(string.Format("<li>Repeat Read: {0} => {1}", s1, s2));
                tx.Complete();
                //VS2010偵錯執行到這裡之後,SSMS裡的指令才會執行完成
            }
        }
    }

不管是System.Data.OracleClient或ODP.NET,也都提供OracleTransaction物件,並支援ReadCommitted及Serializable等隔離等級。只是實測後,才發現ORACLE處理Serializable的原則跟SQL大不同。

我用以下的程式測試了ODP.NET的Transaction功能。程式先將資料表某筆資料的Score設成100,讀取第一次後,開另一條Connection將該筆資料Score改成500,再讀取第二次,最後比對兩次的讀取結果。其中有個bEnableTrans設定是否啟動Transaction。

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="Oracle.DataAccess" %>
<%@ Import Namespace="Oracle.DataAccess.Client" %>
<script runat="server">
 
    string cnStr = "Data Source=XXX;User Id=YYY;Password=ZZZ";
 
    private void SetData(int v)
    {
        using (OracleConnection cn = new OracleConnection(cnStr))
        {
            cn.Open();
            OracleCommand cmd = new OracleCommand(
                "UPDATE TRNTEST SET Score = :p1 WHERE USERID='Jeffrey'", cn);
            cmd.Parameters.Add("p1", OracleDbType.Decimal).Value = v;
            cmd.ExecuteNonQuery();
            Response.Write("<li>Set Score=" + v.ToString());
        }
    }
 
    private void TestOracleTrans()
    {
        SetData(100); //一開始將Score設為100
        OracleTransaction trn = null;
        bool bEnableTrans = true; //設定是否要開Transaction
        using (OracleConnection cn = new OracleConnection(cnStr))
        {
            cn.Open();
            if (bEnableTrans) //由旗標決定是否要開啟Transaction, 且設Serializable
                trn = cn.BeginTransaction(System.Data.IsolationLevel.Serializable);
            OracleCommand cmd = cn.CreateCommand();
            cmd.CommandText = "SELECT Score FROM TRNTEST WHERE UserId='Jeffrey'";
            OracleDataReader dr = cmd.ExecuteReader(); //第一次讀取
            dr.Read();
            int s1 = Convert.ToInt32(dr[0]);
            SetData(500); //另開一條連線將Score改成500
            dr = cmd.ExecuteReader(); //再次讀取
            dr.Read();
            int s2 = Convert.ToInt32(dr[0]);
            Response.Write(string.Format("<li>Repeat Read: {0} => {1}", s1, s2));
 
            if (trn != null) trn.Commit();
        }
    }
    
    protected void Page_Load(object sender, EventArgs e)
    {
        TestOracleTrans();
    }    
</script>

實驗結果,未啟動Transaction時,兩次讀取結果是100, 500,待合預期! 但有趣的是啟動Serializable等級的Transaction後,兩次讀取結果是100, 100,但更改Score為500的動作沒有被擋下來,而實際上資料庫中的值在第二次讀取時已是500。換句話說,OracleTransaction的Serializable隔離只保障了前後讀取結果一致,並無保護資料不被外界更改的效果。

如果要做到如同SQL Serializable的效果,記得要在SELECT時加上FOR UPDATE子句。(提醒: 上述程式可改成SELECT Score FROM TRNTEST WHERE UserId='Jeffrey' FOR UPDATE,但SetData(500)部分得移除改成手動驗證,不然SetData(500)被會第一次讀取放的Lock擋住,搞出自己等自己的Deadlock,等到鬍子都白了都不會有結果滴。)

歡迎推文分享:
Published 27 January 2011 02:30 PM 由 Jeffrey
Filed under: , ,
Views: 8,942



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<January 2011>
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
303112345
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication