0

接觸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，等到鬍子都白了都不會有結果滴。)


Comments

Be the first to post a comment

Post a comment