SQL與ORACLE處理IsolationLevel.Serializable的行為差異
0 | 9,947 |
接觸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