MSSqlserver的鎖模式介紹

標籤：不可   取資料   request   resource   就是   犧牲品   允許   執行   表鎖   

 一 SQL Server 鎖類型的說明

在SQL Server資料庫中加鎖時，除了可以對不同的資源加鎖，還可以使用不同程度的加鎖方式，即有多種模式，SQL Server中鎖模式包括：

1.共用鎖定(S) 共用鎖定用於所以的制度資料操作。共用鎖定是非獨佔的，允許多個並發事務讀取其鎖定資源。預設情況下，資料被讀取後，SQL Server立刻釋放共用鎖定。

　　例如: 執行查詢"SELECT * FROM dbo.Customer"時，首先鎖定第一頁，讀取之後，釋放對第一頁的鎖定，然後鎖定第二頁。這樣，就允許在讀操作過程中，修改未被鎖定的第一頁。但是，交易隔離等級連結選項設定和SELECT語句中的鎖定設定都可以改變SQL Server的這種預設設定。

　　　　執行查詢"SELECT * FROM dbo.Customer WITH(HOLDLOCK)"就要求在整個查詢過程中，保持對錶的鎖定，直到查詢完成才釋放鎖定。

2.更新鎖定(U) 更新鎖定在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共用鎖定(S)造成的死結現象。因為使用共用鎖定(S)時，修改資料的操作分為兩步，首先獲得一個共用鎖定（S），讀取資料，然後再將共用鎖定升級為排它鎖(X),然後執行修改操作。這樣如果同時又兩個或多個事務同時對一個事務申請共用鎖定，在修改資料的時候，這些事務將共用鎖定升級為排它鎖（X）。這時，這些事務都不會釋放共用鎖定而是一直等待對方釋放，這樣就造成了死結。如果一個資料在修改前直接申請更新鎖定（U），在資料修改的時候再升級為排它鎖（X），就可以避免死結。

3.結構鎖（Sch） 執行表的資料定義語言 (Data Definition Language)（DDL）操作（例如添加列或除去表）時使用架構修改（Sch-M）鎖。當編譯查詢時，使用架構穩定性（Sch-S）鎖。架構穩定性鎖不阻塞任何事務鎖，包括排它鎖。因此在編譯查詢時，其它事務（包括在表上有排它鎖的事務）都能繼續運行。但不能在表上執行DDL操作。

4.意圖鎖定（I） 意圖鎖定說明SQL Server有在資源的底層獲得共用鎖定或排它鎖的意向。資料庫引擎使用意圖鎖定來保護共用鎖定或排它鎖放置在鎖階層的底層資源上。意圖鎖定之所以命名為意圖鎖定，是因為在較低層級鎖前可擷取它們，因此會通知意向將鎖放置在較低層級上。

　　例如：表級的共用意圖鎖定說明事務意圖講排它鎖釋放在表中的頁或者行。

　　　　　意圖鎖定有可以分為：

　　　　　　共用意圖鎖定（IS）：事務意圖在共用意圖鎖定所鎖定的底層資源上放置共用鎖定來讀取資料。

　　　　　　排它意圖鎖定（IX）：事務意圖在共用鎖定鎖定資源上放置排它鎖來修改資料。

　　　　　　共用式排它意圖鎖定（SIX）：事務允許其他事務使用共用鎖定來讀取頂層資源，並意圖在該資源低層上放置排它鎖。

　　　　　　意圖鎖定的兩種用途：

• • • 防止其他事務以會使較低層級的鎖無效的方式修改較進階別資源。
    • 提高資料庫引擎在較高的粒度層級檢測鎖衝突的效率。　　　　　　　　　　

5.大容量更新鎖定（BU） 當將資料大量複製到表，且指定了TABLOCK提示或者使用sp_tableoption設定了table lock on bulk表選項時，將使用大容量更新鎖定。大容量更新鎖定允許進程將資料並發大量複製到同一表，同時防止其它不進行大量複製資料的進程訪問該表。

 

SQL Server使用加鎖功能說明：

• NOLOCK(不加鎖)：SQL Server在讀取資料時不加任何鎖。在這種情況下，使用者可能讀取到未完成事務或者復原中的資料，即所謂的“髒資料”。僅應用於SELECT語句。　　　　
• HOLDLOCK(保持鎖): SQL Server會將此共用鎖定保持至整個事務結束，而不會再途中釋放。也就是說，共用鎖定保留到事務完成，而不是在相應的表、行、或資料頁不再需要時立即釋放鎖。等同於SERIALIZABLE。
• PAGLOCK(頁鎖): 在通常使用單個表鎖的地方採用頁鎖。READCOMMITTED用與運行在提交讀隔離等級的事務相同的鎖語義執行掃描。
• READPAST：跳過鎖定行，此選項導致事務跳過由其它事務鎖定的行（這些行平常會顯示在結果集內），而不是阻塞該事務，使其等待其它事務釋放在這些行上的鎖。僅用與SELECT語句。
• READUNCOMMITTED： 等同於NOLOCK，用與運行在可重複讀隔離等級的事務相同的鎖語義執行掃描。
• ROWLOCK：使用行級鎖，而不適用粒度更粗的頁級鎖和表級鎖。SERIALIZABLE用與運行在可串列讀隔離等級的事務相同的鎖語義執行掃描。等同於HOLDLOCK。
• TABLOCK: 使用表鎖代替粒度更細的行級鎖或頁級鎖。在語句結束前，SQL Server一直持有該鎖。但是，如果同時制定HOLDLOCK，那麼在事務結束之前，鎖將被一直持有。
• UPDLOCK: 讀取表時使用更新鎖定，而不使用共用鎖定，並將鎖一直保留到語句或事務的結束。UPDLOCK的優點是允許您讀取資料（不阻塞其它事務）並在以後更新資料，同時確保自從上次讀取資料後資料沒有被更改。
• XLOCK： 使用排它鎖並一直保持到語句處理的所有資料上的事務結束時。可以使用PAGLOCK或TABLOCK指定該鎖，這種情況下排它鎖適用於適當層級的粒度。至於鎖定多少條記錄的問題，sql預設的鎖定行本來就是行層級鎖定的，所以你用TOP 1指定只鎖定一條記錄就好。 SELECT TOP 1 * FROM dbo.Customer  WITH(UPLOCK,READPAST)

二 死結與死結解除

1. 死結

　　使用或管理資料庫都不可避免的涉及到死結，一旦發生死結，資料相互等待對方資源的釋放，會阻止對資料的訪問，嚴重會造成DB掛掉，當資源被鎖定，無法被訪問時，可以終止訪問DB的那個session來達到解鎖的目的（即Kill掉造成鎖的那個進程）。

　　在兩個或多個任務中，如果每個任務鎖定了其它任務試圖鎖定資源，此時會造成這些任務永久阻塞，從而出現死結。例如:

• • 事務A 擷取了行1的共用鎖定
  • 事務B擷取了行2的共用鎖定
  • 現在，事務A請求行2的排它鎖，但在事務B完成並釋放其對行2的共用鎖定之前被阻塞。
  • 現在，事務B請求擷取行1的排它鎖，但在事務A完成並釋放其行1持有的共用鎖定之前被阻塞。　　　　　　　　

　　事務B完成之後事務A才能完成，但是事務B由事務A阻塞。該條件也稱為循環相依性關係：事務A依賴於事務B，事務B通過對事務A的依賴關係關閉迴圈。

　　除非摸個外部進程斷開死結，否則死結中的兩個事務都將無線期等待下去。SQL Server資料庫引擎死結監視器定期檢查陷入死結的任務。如果監視器檢測到循環相依性關係，將選擇其中一個任務作為犧牲品，然後終止其事務並提示錯誤。這樣，其它任務就可以完成其事務。對於事務以鎖霧終止的應用程式，它還可以重試該事務，但通常要等到其它一起陷入死結的其它事務完成後執行。

2. 死結檢測

• SQL Server資料庫引擎自動檢測SQL Server中的死結迴圈。資料庫引擎選擇一個會話作為死結犧牲品，然後終止當前事務來打斷死結。
• 查看DMV：sys.dm_tran_locks (SELECT resource_type,resource_description,resource_associated_entity_id,request_mode,request_status,request_owner_type FROM sys.dm_tran_locks WHERE resource_type!=‘DATABASE‘)　　
• SQL Server Profile能夠直觀的顯示死結的圖形事件。

　　

　　死結樣本：

　　　　第一個串連中執行：

　　　　

BEGIN TRANUPDATE dbo.Customer SET NRIC=‘1000‘ WHERE TransactionNumber=6WAITFOR DELAY ‘00:00:30‘UPDATE dbo.Employee SET ts=1111 WHERE TransactionNumber=1COMMIT

　　　　第二個串連中執行

BEGIN TRANUPDATE dbo.Employee SET ts=1111 WHERE TransactionNumber=1WAITFOR DELAY ‘00:00:10‘UPDATE dbo.Customer SET NRIC=‘1000‘ WHERE TransactionNumber=6COMMIT

　　如果兩個串連同時執行資料庫（SQL Server2008）會自動檢測到死結，終止其中一個進程。

 

MSSqlserver的鎖模式介紹