Sql Server鎖總結

 

MSSQL 並發控制小結

 

l 並行作業中容易出現的問題：

1.       更新丟失

a)         資料庫中的原值是200,A使用者在一個事務裡將其更新為300，同時B使用者在一個事務裡將其更新為400,但是B事務因為一些原因導致事務rollback了，當AB執行完後，資料庫中的值仍為200，這樣A的更新就丟失了

2.       髒讀

a)         資料庫中的原值為200，A使用者在一個事務裡將其更新為300,在該事務還沒有Commit & rollback之前，B使用者會讀到300,如果後來A事務rollback了，這樣B就出現了髒讀。

3.       不可重複讀取

a)         資料庫中的原值為200, A使用者在一個事務裡，第一次select時，結果為200，緊接著B使用者在另一個事務中更新為300，然後當A再做select操作，結果就不再是200，而是300，這就出現了同一事務中對同一行資料的兩次select結果不一致，該行資料就為不可重複讀取。

4.       虛讀

a)         資料庫中值大於200的行數原來有10行，A使用者在一個事務裡，第一次select後，結果為10行，緊接著B使用者在另一個事務中insert了一條新資料，如300，然後當A再做select操作，結果就不再是10，而是11，新增的那行資料就為虛讀。

l 為解決這些問題，而生的隔離等級：

1.       末授權讀取

a)         隔離等級最低，不允許丟失更新

b)         允許多個事務同時讀資料，但不允許多個事務同時寫資料

2.       授權讀取

a)         不允許髒讀，也不允許丟失更新

b)         事務中已經修改的行，在沒提交事務之前，禁止被其它事務select

c)         保證一個事務不會讀到其它事務已經修改但末提交的資料

d)         為SQL Server的預設隔離等級

3.       授權讀取快照

a)         不允許髒讀，也不允許丟失更新

b)         事務中已經修改的行，在沒提交事務之前， 允許被其它事務select，訪問的是該行的曆史版本（可通過READ_COMMITED_SNAPSHOT設定）。

c)         保證一個事務不會讀到其它事務已經修改但末提交的資料

4.       可重複讀取

a)         不允許髒讀，也不允許丟失更新，也不允許不可重複讀取

b)         事務中已經修改的行，在沒提交事務之前，禁止被其它事務select

c)         事務中已經讀取的行，在沒提交事務之前，也禁止其它事務的修改，允許其它事務讀取

d)         所有共用鎖定也必須保持到事務結束。

5.       序列化

a)         最嚴格的事務隔離，禁止相關事務的並發，相關事務只能一個接一個地執行。

b)         所有共用鎖定也必須保持到事務結束。

c)         不僅需要鎖定已經讀資料，還要使用‘關鍵範圍鎖定’鎖定潛在的資料，防止其它事務insert新的資料。

l 並發控制項模型

1.         悲觀

a)         在悲觀模型中，寫者總是阻塞讀者和寫者，而讀者也會阻塞寫者                  

2.         樂觀

a)         在樂觀模型中，唯一可能發生阻塞的是寫者阻塞寫者。

b)         上面5種隔離等級中，只有授權讀取快照屬於樂觀模型

l 各種鎖

1.         共用鎖定

a)         讀取資料時，會在一行，一頁，或者整個表上加上共用鎖定

b)         共用鎖定不會妨礙其它事務的讀取操作，但會使其它事務的修改操作陷入等待

2.         獨佔鎖定

a)         插入，更新，刪除操作時，會加獨佔鎖定

b)         排它鎖會持續鎖定，直到該事務結束

3.         更新鎖定

a)         當執行一個修改操作時，需要先找著等待修改的記錄的位置，此時會加上更新鎖定，來保護資料，當找著正確的位置時，更新鎖定會升級為獨佔鎖定。

b)         與共用鎖定相容

4.         意圖鎖定

a)         當執行一行資料修改時，該行會加獨佔鎖定，則該行鎖在的頁，或者表會加意向獨佔鎖定，防止另外的事務在其父容器上，如頁級或者表級擷取獨佔鎖定。

l 鎖的觀察

1.         sys.dm_tran_locks

a)         該視圖用來取代sp_lock預存程序,來觀察系統中的鎖狀態

b)         select object_name(object id)可查看是哪張表

2.         死結

a)         迴圈死結

                                       i.              兩個事務互相持有對方想要的鎖，就會產生死結。

b)         轉換死結

                                       i.              兩個事務均持有一個頁上的共用鎖定，又想將共用鎖定轉換為獨佔鎖定，由於;雙方共用鎖定的存在，所以無法完成，而產生死結。

c)         死結時的優先順序

                                       i.              可設定 SET DEADLOCK_PRIORITY來設定優先權，當死結發生時，低優先順序的事務將會作為犧牲者

                                     ii.              當優先順序一致時，復原開銷較低的會話會作為犧牲者。

 

附一個經典的死結:

    SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
string sql1 = "Update Lock1 SET C1=C1+1";
string sql2 = "SELECT * FROM Lock1";
ExecuteNonQuery(tran, sql1); 

ExecuteNonQuery(null, sql2); 

重現鎖:

Query1: 

Begin Tran
  insert [User] values(1,'asdf')
  WaitFor Delay '00:00:10';

Rollback Tran; 

Query2:

  select * from [User] 

 解決方案：

       a). 把SELECT放在Update語句前：SELECT不在事務中，且執行完畢會釋放S鎖；
       b). 把SELECT也放加入到事務中：ExecuteNonQuery(tran, sql2);

       c). SELECT加With(NOLock)