[SQL SERVER]小心使用With NoLock(續)

2014-05-04

61833
0
SQL_SERVER

很久以前我有簡單寫過使用with nolock將帶來更麻煩問題(如頁面分割)，

基本會讓效能低落和資源競爭(concurrency)擴大，

當然資料不正確性和不精確性是必然的，

query使用nolock避開block，就是告訴SQL Server不在意資料準確性，

但如果其他交易rollback可能會導致query讀取到未commited資料(dirty read)，

這一連串過程頻率過高嚴重可能會出現資料毀損狀況(雖然機率很低)，

但也不是說nolock不能使用，而是開發人員需知道如何時情況下使用nolock，

因為query with nolock不單單只有表面資料不準確缺點這麼簡單而已，

但是否有其他方法不使用nolock，又可讓query不必等待呢?答案是有的，

我會推薦query with nowait並且返回訊息告知前端使用者稍後讀取(假如有交易進行)，

在程式方面就需要我們額外加工處理，

如設定最大查詢計數，每一次requery都+1(也可搭配SET LOCK_TIMEOUT)，

下面我簡單模擬整個處理過程。

1.query with nolock

前端要取一段發票日期範圍AMT加總

select sum(amt)
from NLEdiTax

where IDATE>='2013.03.01' and IDATE <='2013.03.15'

我模擬現實世界中，開發人員要避開block，都會把上面查詢加上nolock，

但大多數開發人員都沒有考慮到如果交易rollback呢?

假設有其他交易進行中

begin tran
update NLEdiTax set amt='12578'
where IDATE='2013.03.14' and COUNTRY='GR'

query

select sum(amt)
from NLEdiTax with(nolock)
where IDATE>='2013.03.01' and IDATE <='2013.03.15'

這時前端會返回 2990313。

但該交易發生了一些問題，所以並沒有commit而是rollback了

所以我們前端AP應該要顯示下面AMT加總，而不是(withnolock)結果 2990313

上面是最常見加上nolock不讓query等待，好讓前端AP可立即反應，

但我個人認為利用nowait 來取代nolock可能會是比較好的方式，下面我實際用asp.net mvc測試。

2.query with nowait

controller

private string Query()
        {
            DataTable dt = new DataTable();
            string sqlstatement = @"
select sum(amt) as 'AMT'
from NLEdiTax with( nowait )
where IDATE>='2013.03.01' and IDATE <='2013.03.15' ";
            try
            {
                using (SqlConnection conn = new SqlConnection(conns))
                {
                    conn.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);
                    conn.FireInfoMessageEventOnUserErrors = true;
                    using (SqlCommand cmd = new SqlCommand(sqlstatement, conn))
                    {
                        if (conn.State == System.Data.ConnectionState.Closed)
                            conn.Open();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(dt);
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            return dt.Rows[0]["AMT"].ToString();
                        }
                    }
                }
                return "";
            }
            catch (SqlException sqlex)
            {
                int retries = 5;
                if (sqlex.Number == 1205)//deadlock
                {
                    Thread.Sleep(500);
                    return sqlex.Message;
                }
                else if (sqlex.Number == 1222)//lock
                {
                    Thread.Sleep(500);
                    //requery
                    //while (retries > 0)
                    //{

                    //}
                    return "1222";
                }
                else
                    return sqlex.Message;
            }
            catch(Exception ex)
            {
                return ex.Message;
            }
        }

private void InfoMessageHandler(object sender, SqlInfoMessageEventArgs e)
       {
           TempData["amt"] = e.Errors[0].Number.ToString();
       }

View