SQLserver 中的各種鎖的說明

 

鎖定提示                                 描述  HOLDLOCK        將共用鎖定保留到事務完成，而不是在相應的表、行或資料頁不再需要時就立即釋放鎖。HOLDLOCK        等同於        SERIALIZABLE。        NOLOCK        不要發出共用鎖定，並且不要提供排它鎖。當此選項生效時，可能會讀取未提交的事務或一組在讀取中間復原的頁面。有可能發生髒讀。僅應用於        SELECT        語句。        PAGLOCK        在通常使用單個表鎖的地方採用頁鎖。        READCOMMITTED        用與運行在提交讀隔離等級的事務相同的鎖語義執行掃描。預設情況下，SQL        Server        2000        在此隔離等級上操作。        READPAST        跳過鎖定行。此選項導致事務跳過由其它事務鎖定的行（這些行平常會顯示在結果集內），而不是阻塞該事務，使其等待其它事務釋放在這些行上的鎖。READPAST        鎖提示僅適用於運行在提交讀隔離等級的事務，並且只在行級鎖之後讀取。僅適用於        SELECT        語句。        READUNCOMMITTED        等同於        NOLOCK。        REPEATABLEREAD        用與運行在可重複讀隔離等級的事務相同的鎖語義執行掃描。        ROWLOCK        使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。        SERIALIZABLE        用與運行在可串列讀隔離等級的事務相同的鎖語義執行掃描。等同於        HOLDLOCK。        TABLOCK        使用表鎖代替粒度更細的行級鎖或頁級鎖。在語句結束前，SQL        Server        一直持有該鎖。但是，如果同時指定        HOLDLOCK，那麼在事務結束之前，鎖將被一直持有。        TABLOCKX        使用表的排它鎖。該鎖可以防止其它事務讀取或更新表，並在語句或事務結束前一直持有。        UPDLOCK        讀取表時使用更新鎖定，而不使用共用鎖定，並將鎖一直保留到語句或事務的結束。UPDLOCK        的優點是允許您讀取資料（不阻塞其它事務）並在以後更新資料，同時確保自從上次讀取資料後資料沒有被更改。        XLOCK        使用排它鎖並一直保持到由語句處理的所有資料上的事務結束時。可以使用        PAGLOCK        或        TABLOCK        指定該鎖，這種情況下排它鎖適用於適當層級的粒度


死結
多個會話同時訪問資料庫一些資源時，當每個會話都需要別的會話正在使用的資源時，死結就有可能發生。 死結在多線程系統中都有可能出現，並不僅僅局限于于關聯式資料庫管理系統。
鎖的類型
一個資料庫系統在許多情況下都有可能鎖資料項目。其可能性包括：
 

  
Rows—資料庫表中的一整行
  
Pages—行的集合（通常為幾kb）
  
Extents—通常是幾個頁的集合
  
Table—整個資料庫表
  
Database—被鎖的整個資料庫表
 
除非有其它的說明，資料庫根據情況自己選擇最好的鎖方式。不過值得感謝的是，SQL Server提供了一種避免預設行為的方法。這是由鎖提示來完成的。
鎖提示
Tansact－SQL提供了一系列不同層級的鎖提示，你可以在SELECT,INSERT,UPDATE和DELETE中使用它們來告訴SQL Server你需要如何通過重設鎖。可以實現的提示包括：
FASTFIRSTROW—選取結果集中的第一行，並將其最佳化
HOLDLOCK—持有一個共用鎖定直至事務完成
NOLOCK—不允許使用共用鎖定或獨享鎖。這可能會造成資料重寫或者沒有被確認就返回的情況； 因此，就有可能使用到髒資料。這個提示只能在SELECT中使用。
PAGLOCK—鎖表格
READCOMMITTED—唯讀取被事務確認的資料。這就是SQL Server的預設行為。
READPAST—跳過被其它進程鎖住的行，所以返回的資料可能會忽略行的內容。這也只能在SELECT中使用。
READUNCOMMITTED—等價於NOLOCK.
REPEATABLEREAD—在查詢語句中，對所有資料使用鎖。這可以防止其它的使用者更新資料， 但是新的行可能被其它的使用者插入到資料中，並且被最新訪問該資料的使用者讀取。
ROWLOCK—按照行的層級來對資料上鎖。SQL Server通常鎖到頁或者表層級來修改行， 所以當開發人員使用單行的時候，通常要重設這個設定。
SERIALIZABLE—等價於HOLDLOCK.
TABLOCK—按照表層級上鎖。在運行多個有關表層級資料操作的時候，你可能需要使用到這個提示。
UPDLOCK—當讀取一個表的時候，使用更新鎖定來代替共用鎖定，並且保持一直擁有這個鎖直至事務結束。 它的好處是，可以允許你在閱讀資料的時候可以不需要鎖，並且以最快的速度更新資料。
XLOCK—給所有的資源都上獨享鎖，直至事務結束。 微軟將提示分為兩類：granularity和isolation-level。Granularity提示包括PAGLOCK, NOLOCK, ROWLOCK和TABLOCK。而isolation-level提示包括HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD和SERIALIZABLE。
 
可以在Transact-SQL聲明中使用這些提示。它們被放在聲明的FROM部分中，位於WITH之後。WITH聲明在SQL Server 2000中是可選部分，但是微軟強烈要求將它包含在內。這就使得許多人都認為在未來的SQL Server發行版中，就可能會包含這個聲明。下面是提示應用於FROM從句中的例子： [ FROM { < table_source > } [ ,...n ] ] < table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] < table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
詞彙表
會話 (session)
English Query 中由 English Query 引擎執行的操作序列。會話在使用者登入時開始，在使用者登出時結束。 會話期間的所有操作構成一個事務範圍，並受由登入使用者名稱和密碼決定的許可權的支配。 堆表 (heap table)
如果一個表沒有索引，資料行以隨機的順序儲存，這種結構稱為堆。這種表稱為堆表。 意圖鎖定 (intent lock)
放置在資源階層的一個層級上的鎖，以保護較低層級資源上的共用或排它鎖。例如，在 SQL Server 2000 資料庫引擎任務應用表內的共用或排它行鎖之前，在該表上放置意圖鎖定。如果另一個任務試圖在該表層級上應用共用或排它鎖，則受到由第一個任務控制的表層級意圖鎖定的阻塞。第二個任務在鎖定該表前不必檢查各個頁或行鎖，而只需檢查表上的意圖鎖定。 排它鎖(exclusive lock)
一種鎖，它防止任何其它事務擷取資源上的鎖，直到在事務的末尾將資源上的原始鎖釋放為止。在更新操作（INSERT、UPDATE 或 DELETE）過程中始終應用排它鎖。 隔離等級 (isolation level)
控制隔離資料以供一個進程使用並防止其它進程幹擾的程度的事務屬性。設定隔離等級定義了 SQL Server 會話中所有 SELECT 語句的預設鎖定行為。 擴充(盤)區 (extent)
每當 SQL Server 物件（如表或索引）需要更多空間時分配給該對象的空間的單元。在 SQL Server 2000 中，一個擴充是八個鄰接的頁。 鎖粒度(lock granularity)
SQL Server中資料以8KB為一頁(page)的單位儲存，連續的8個頁組成一個擴充(extent)。建立資料庫時， 按這種方式來分配磁碟空間。當資料庫容量增加時，意味著要建立更多的頁和擴充。按照資料的儲存結構 (row,page,extent)進行加鎖，就是鎖粒度。
SQL Server 2000裡，最低的鎖粒度是行(row)鎖。SQL Server可以單獨鎖行，資料頁，擴充，表。 假設在UPDATE操作中隻影響一行記錄，SQL Server會將該行記錄鎖定，其他使用者只有等該行記錄的 更新操作完畢後才能修改。另一方面，對於沒有鎖定的行記錄，其他使用者是可以進行修改的。 因此行級鎖對於並發是最佳的。
現在假設UPDATE操作影響1000行記錄，SQL Server是否一次鎖定一行？那就意味著如果有一個這樣的選項，在 記憶體允許前提下，需要1000個鎖。實際上，SQL Server會根據這些資料是否分布在連續的頁，來決定是否用 幾個頁面鎖，或者擴充鎖，或者是表鎖。如果SQL Server加了頁面鎖，那麼這些頁面上的記錄其它使用者就無法 訪問或者修改，即使頁面上有些資料並非屬於這1000行記錄。這就是一種追求並發效能和資源消耗之間的平衡策略。
SQL Server對鎖需要的資源十分敏感，也就是說，SQL Server查詢最佳化工具檢測到可用記憶體較低時，就會使用頁鎖來 替代多個行鎖。同樣，在記憶體消耗更低的判斷下，會優先選擇表鎖而幾個擴充鎖。 鎖資訊的標識
鎖類型:
 

  
RID :行標識符。用於在表中單獨鎖定一行。
  
KEY :鍵, 索引內部的行鎖。用於保護可串列事務中的鍵範圍。
  
PAG :資料或索引頁。
  
EXT :相鄰的八個資料頁或索引頁構成的一組。
  
TAB :包括所有資料和索引在內的整個表。
  
DB :資料庫。
 
 
^_^，說是簡單介紹，其實我覺得已經對鎖介紹也蠻多了，也許有寫得不對的地方，有心人幫忙指點一下。詞彙的中文翻譯 是從SQL Server線上說明(books online)上搬用的。下面開始本文，好歹人家也是發表在堂堂DBA大網站上的Article，呵呵。
 
使用SQL Server 6年多了，在下自認為對SQL Server還是比較熟悉的，而且我喜歡將SQL Server內部的一些 東西搞清楚。
當我在教一門SQL Server編程課程時，我注意到微軟MSDN中提到了鎖相容性，在MSDN 列舉了一個相容性關係的表格。
看過這張關係表格，我就想知道是否存在用於更新的意圖鎖定(Intent Update lock)？於是我開始閱讀相關的資料。 這篇文章也是我研究的結果。這篇文章的適用讀者是那些對隔離等級(isolation level)，意圖鎖定，死結和鎖粒度有所瞭解的。 如果你對這些領域還不瞭解，那麼我建議你在讀這篇文章前，應該先去瞭解和閱讀相關資料。
希望這篇文章能夠加深你對SQL Server鎖的理解，也許有些技巧還能夠在SQL Server編程中帶來協助。
必須指出，即使不知道鎖是如何工作的，你也能長時間愉快地使用SQL Server，並且能建立高品質的代碼和資料庫設計。 不過如果你象我那樣喜歡探究事情的內部機理，或者你的工作需要你掌握一些效能方面的知識，我很樂意能教你一些有用的東西。
更新鎖定(Update Locks)
死結的典型情況是SPID X鎖住了資源A，並在等待對資源B進行加鎖，而SPID Y鎖住了資源B，在等待對資源A加鎖，如此就 形成了死結。如果不理解，查詢 MSDN 或者相關的資料。
現在來假想更多情形下的死結。假設：SPID X在資源A上加了共用鎖定，SPID Y也在資源A上加了共用鎖定，因為是共用鎖定， 所以這樣沒有問題。現在X想把共用鎖定升級為排它鎖(exclusive lock)以用於更新資源。X就必須等Y釋放共用鎖定才能辦到， 當X在等待時，Y也想做同樣的事情。這樣，X在等Y釋放，Y同時在等待X釋放，死結產生了。這種死結被稱為 轉換死結(conversion deadlock)。
這種情況會很常見，為避免這種死結，就引入了更新鎖定機制。更新鎖定允許串連讀取資源，同時宣告它因為要編輯資料而要開始 鎖住資源了。SQL Server並無法提前知道一個事務要把共用鎖定轉換成排它鎖了，當然有一個情況特殊，即只在一個SQL語句中 完成讀取然後更新的操作，比如說UPDATE XXX (SELECT YYY ....)這種類型。對於一般的SELECT語句，我們必須顯示地 使用UPDLOCK提示。