知方可補不足~Sqlserver中的幾把鎖和.net中的事務級別

回到目錄

當資料表被事務鎖定後，我們再進行select查詢時，需要為with(鎖選項)來查詢資訊，如果不加，select將會被阻塞，直到鎖被釋放，下面介紹幾種ＳＱＬ的鎖選項

SQL的幾把鎖

NOLOCK（不加鎖）

此選項被選中時，SQL Server 在讀取或修改資料時不加任何鎖。 在這種情況下，使用者有可能讀取到未完成事務（Uncommited Transaction）或回滾(Roll Back)中的資料, 即所謂的“髒資料”。 

HOLDLOCK（保持鎖）

此選項被選中時，SQL Server 會將此共享鎖保持至整個事務結束，而不會在途中釋放。 

UPDLOCK（修改鎖）

此選項被選中時，SQL Server 在讀取資料時使用修改鎖來代替共享鎖，並將此鎖保持至整個事務或命令結束。使用此選項能夠保證多個程式能同時讀取資料但只有該程式能修改資料。 

TABLOCK（表鎖）

此選項被選中時，SQL Server 將在整個表上置共享鎖直至該命令結束。 這個選項保證其他程式只能讀取而不能修改資料。 

PAGLOCK（頁鎖）

此選項為預設選項， 當被選中時，SQL Server 使用共享頁鎖。 

TABLOCKX（排它表鎖）

此選項被選中時，SQL Server 將在整個表上置排它鎖直至該命令或事務結束。這將防止其他程式讀取或修改表中的資料。

下面看一下.net frameworks平臺關於事務級別的列舉，它對應於sql的事件級別

 

namespace System.Transactions
{
    // 摘要:
    //     Specifies the isolation level of a transaction.
    public enum IsolationLevel
    {
        // 摘要:序列化隔離級別，約束力最高，在資料集上放置一個範圍鎖，以防止其他使用者在事務完成之前更新資料集或將行插入資料集內。
　　　　 // 這是四個隔離級別中限制最大的級別。因為併發級別較低，所以應只在必要時才使用該選項。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 HOLDLOCK 相同。
        //     Volatile data can be read but not modified, and no new data can be added
        //     during the transaction.
        Serializable = 0,
        //
        // 摘要:可重複讀的隔離級別，可能出現幻讀，鎖定查詢中使用的所有資料以防止其他使用者更新資料，但是其他使用者可以將新的幻像行插入資料集，
        // 且幻像行包括在當前事務的後續讀取中。因為併發低於預設隔離級別，所以應只在必要時才使用該選項。
        //     Volatile data can be read but not modified during the transaction.New data
        //     can be added during the transaction.
        RepeatableRead = 1,
        //
        // 摘要:不能讀但可修改，可能出現不可重複讀,指定在讀取資料時控制共享鎖以避免髒讀，但資料可在事務結束前更改，
        // 從而產生不可重複讀取或幻像資料。該選項是 SQL Server 的預設值。
        //     Volatile data cannot be read during the transaction, but can be modified.
        ReadCommitted = 2,
        //
        // 摘要:可以讀也可以修改，可能出現髒資料,執行髒讀或 0 級隔離鎖定，這表示不發出共享鎖，也不接受排它鎖。
        // 當設定該選項時，可以對資料執行未提交讀或髒讀；在事務結束前可以更改資料內的數值，行也可以出現在資料集中或從資料集消失。這是四個隔離級別中限制最小的級別。
        //     Volatile data can be read and modified during the transaction.
        ReadUncommitted = 3,
        //
        // 摘要:忽略資料的修改,得到修改前的資料
        //     Volatile data can be read.Before a transaction modifies data, it verifies
        //     if another transaction has changed the data after it was initially read.If
        //     the data has been updated, an error is raised.This allows a transaction to
        //     get to the previously committed value of the data.
        Snapshot = 4,
        //
        // 摘要:
        //     The pending changes from more highly isolated transactions cannot be overwritten.
        Chaos = 5,
        //
        // 摘要:
        //     A different isolation level than the one specified is being used, but the
        //     level cannot be determined.An exception is thrown if this value is set.
        Unspecified = 6,
    }
}

 

對於事務級別的總結

下面是在巢狀事務中經常遇到的情況

　　１　 髒讀：一個事務會讀進還沒有被另一個事務提交的資料，所以你會看到一些最後被另一個事務回滾掉的資料。

　　２　 讀值不可復現：一個事務讀進一條記錄，另一個事務更改了這條記錄並提交完畢，這時候第一個事務再次讀這條記錄時，它已經改變了。

　　３ 　幻影讀：一個事務用select子句來檢索一個表的資料，另一個事務insert一條新的記錄，並且符合select條件，這樣，第一個事務用同一個select條件來檢索資料後，就會多出一條記錄。

 

下面是IsolationLevel級別在使用過程中的一些說明（來自博文：http://www.cnblogs.com/CN5135/archive/2011/10/24/2222350.html）

ReadCommitted：
假設A事務對正在讀取資料Data放置了共享鎖，那麼Data不能被其它事務改寫，所以當B事務對Data進行讀取時總和A讀取的Data資料是一致的，所以避免了髒讀。由於在A沒有提交之前可以對Data進行改寫，那麼B讀取到的某個值可能會在其讀取後被A更改從而導致了該值不能被重複取得；或者當B再次用相同的where字句時得到了和前一次不一樣資料的結果集，也就是幻像資料。

ReadUncommitted：
假設A事務即不釋出共享鎖，也不接受獨佔鎖，那麼併發的B或者其它事務可以改寫A事務讀取的資料，那麼併發的C事務讀取到的資料的狀態和A的或者B的資料都可能不一致，那麼。髒讀、不可重複讀、幻象資料都可能存在。

RepeatableRead：
（注意MSDN原文中的第一句話：在查詢中使用的所有資料上放置鎖，所以不存在髒讀的情況）。
假設A事務對讀取的所有資料Data放置了鎖，以阻止其它事務對Data的更改，在A沒有提交之前，新的併發事務讀取到的資料如果存在於Data中，那麼該資料的狀態和A事務中的資料是一致的，從而避免了不可重複的讀取。但在A事務沒有結束之前，B事務可以插入新記錄到Data所在的表中，那麼其它事務再次用相同的where字句查詢時，得到的結果數可能上一次的不一致，也就是幻像資料。

Serializable：
 在資料表上放置了排他鎖，以防止在事務完成之前由其他使用者更新行或向資料集中插入行，這是最嚴格的鎖。它防止了髒讀、不可重複讀取和幻象資料。

 

它的對應表如下：

隔離級別	髒讀（Dirty Read）	不可重複讀（NonRepeatable Read）	幻讀（Phantom Read）
讀未提交（Read uncommitted）	可能	可能	可能
讀已提交（Read committed）	不可能	可能	可能
可重複讀（Repeatable read）	不可能	不可能	可能
可序列化（Serializable ）	不可能	不可能	不可能

 

回到目錄