SQL Server指定查詢時使用的鎖 ：

性能，如何最大限度的提高數據庫的性能是每個DBA都需要面臨的問題，在小量數據時運行如飛，而在大量數據時卻慢如蝸牛，這樣的事情你有沒有碰到過呢？如何更好的提高數據庫的并發訪問性能呢？是的，“鎖”，解決問題的關鍵所在。

 預備知識
　　鎖定模式，大部分內容摘抄自SQL Server 2000 聯機叢書
　　如果你已經熟悉了SQL Server的鎖的類型，可以略過這一章

　　Microsoft? SQL Server? 2000 具有多粒度鎖定，允許一個事務鎖定不同類型的資源。為了使鎖定的成本減至最少，SQL Server 自動將資源鎖定在適合任務的級別。鎖定在較小的粒度（例如行）可以增加并發但需要較大的開銷，因為如果鎖定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就并發而言是相當昂貴的，因為鎖定整個表限制了其它事務對表中任意部分進行訪問，但要求的開銷較低，因為需要維護的鎖較少。

　　SQL Server 可以鎖定以下資源（按粒度增加的順序列出）。
資源 描述
RID 行標識符。用于單獨鎖定表中的一行。
KEY 索引中的行鎖。用于保護可串行事務中的鍵范圍。
PG 8 千字節 (KB) 的數據頁或索引頁。
EXT 相鄰的八個數據頁或索引頁構成的一組。
TAB 包括所有數據和索引在內的整個表。
DB 數據庫

　　SQL Server 使用不同的鎖模式鎖定資源，這些鎖模式確定了并發事務訪問資源的方式。

　　SQL Server 使用以下資源鎖模式。
鎖模式 描述
共享（S） 用于不更改或不更新數據的操作（只讀操作），如 SELECT 語句。
更新（U） 用于可更新的資源中。防止當多個會話在讀取、鎖定以及隨后可能進行的資源更新時發生常見形式的死鎖。
排它（X） 用于數據修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時對同一資源進行多重更新。
意向 用于建立鎖的層次結構。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。
架構 在執行依賴于表架構的操作時使用。架構鎖的類型為：架構修改 (Sch-M) 和架構穩定性 (Sch-S)。
大容量更新（BU） 向表中大容量復制數據并指定了 TABLOCK 提示時使用。

　　共享鎖
　　共享 (S) 鎖允許并發事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時，任何其它事務都不能修改數據。一旦已經讀取數據，便立即釋放資源上的共享 (S) 鎖，除非將事務隔離級別設置為可重復讀或更高級別，或者在事務生存周期內用鎖定提示保留共享 (S) 鎖。

　　更新鎖
　　更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成，此事務讀取記錄，獲取資源（頁或行）的共享 (S) 鎖，然后修改行，此操作要求鎖轉換為排它 (X) 鎖。如果兩個事務獲得了資源上的共享模式鎖，然后試圖同時更新數據，則一個事務嘗試將鎖轉換為排它 (X) 鎖。共享模式到排它鎖的轉換必須等待一段時間，因為一個事務的排它鎖與其它事務的共享模式鎖不兼容；發生鎖等待。第二個事務試圖獲取排它 (X) 鎖以進行更新。由于兩個事務都要轉換為排它 (X) 鎖，并且每個事務都等待另一個事務釋放共享模式鎖，因此發生死鎖。
　　若要避免這種潛在的死鎖問題，請使用更新 (U) 鎖。一次只有一個事務可以獲得資源的更新 (U) 鎖。如果事務修改資源，則更新 (U) 鎖轉換為排它 (X) 鎖。否則，鎖轉換為共享鎖。

　　排它鎖
　　排它 (X) 鎖可以防止并發事務對資源進行訪問。其它事務不能讀取或修改排它 (X) 鎖鎖定的數據。

　　意向鎖
　　意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設置意向鎖可防止另一個事務隨后在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因為 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。
　　意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。
鎖模式 描述
意向共享 (IS) 通過在各資源上放置 S 鎖，表明事務的意向是讀取層次結構中的部分（而不是全部）底層資源。
意向排它 (IX) 通過在各資源上放置 X 鎖，表明事務的意向是修改層次結構中的部分（而不是全部）底層資源。IX 是 IS 的超集。
與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖，表明事務的意向是讀取層次結構中的全部底層資源并修改部分（而不是全部）底層資源。允許頂層資源上的并發 IS 鎖。例如，表的 SIX 鎖在表上放置一個 SIX 鎖（允許并發 IS 鎖），在當前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個資源在一段時間內只能有一個 SIX 鎖，以防止其它事務對資源進行更新，但是其它事務可以通過獲取表級的 IS 鎖來讀取層次結構中的底層資源。

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

　　大容量更新鎖
　　當將數據大容量復制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設置了 table lock on bulk 表選項時，將使用大容量更新 (BU) 鎖。大容量更新 (BU) 鎖允許進程將數據并發地大容量復制到同一表，同時防止其它不進行大容量復制數據的進程訪問該表。

 鎖定提示
我們可以通過觀察sp_lock的結果來查看當前的鎖，不過呢，sp_lock返回的結果都是一堆ID，雖然我們可以通過object_name等函數來得到id所代表的意義，但是畢竟不方便。在Resource Kit里面有一個管理存儲過程，叫sp_lock2，可以返回較詳細的結果，請在參考資料中獲得此代碼。

一般的，我們在使用SQL語句的時候，SQL Server會根據SQL語句的類型，如SELECT, UPDATE等，和所使用的資源，來自動選擇一個合適的鎖模式和范圍。

但是有些時候我們需要更精細的控制鎖定行為，那么我們可以通過鎖定提示來改變鎖的行為。
鎖定提示 描述
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 指定該鎖，這種情況下排它鎖適用于適當級別的粒度。

　　現在簡單舉例說明一下，我們在什么時候會使用這些鎖定提示來提高性能。
　　1) 指定表鎖
　　一般地，SQL Server是不大使用表排它鎖的，尤其是對于比較大的表。因為維持一個大的表排它鎖會嚴重影響系統的并發性能。
　　但是呢，如果在沒有并發操作的情況下，出現很多的PAGE LOCK和EXT LOCK也會在一定程度上影響性能，畢竟鎖的管理還是需要一定的開銷的。所以，在確認沒有并發操作（或者并發操作優先級低）的情況下，我們可以使用TABLOCKX來指定使用表排他鎖。
    UPDATE dbo.ShortMessage WITH (TABLOCKX)
    SET OtherPartyNumber = u.Username
    FROM dbo.UserList u
    WHERE u.UserID = dbo.ShortMessage.OtherPartyID

　　2) 不使用鎖
　　一般地，在SELECT的時候，是會對資源發出一個共享鎖的�？墒窃诓樵兒透�新都很頻繁的時候，我們不希望因為互相等待鎖資源而降低性能，而且我們不在乎讀到的數據是不是最新的，不在乎臟讀和未提交讀的產生，我們可以指定SELECT不使用鎖
　　或者是，在大批更新或插入數據時，由于性子急的原因，我想要知道操作進行到什么程度了，而此時由于表排他鎖的存在，使得我不能或者共享鎖來查詢該表，那么我們也可以不使用鎖。
    SELECT COUNT(*) FROM dbo.ShortMessage WITH (NOLOCK)

　　3) 在查詢時使用排他鎖
　　這個跟性能倒沒有什么關系，但是在一些特殊的情況下有用，比如我們在查詢某些記錄的時候，不希望它被其它進程所查詢，那么我們可以使用排他鎖。
    SELECT * FROM dbo.ShortMessage WITH (XLOCK HOLDLOCK)
    WHERE OtherPartyID = 1
　　這里我還使用了一個HOLDLOCK，是為了使這個鎖保持到整個事務的結束

　　4) 跳過鎖定行
　　如果在查詢時，某些記錄由于不能獲得共享鎖而導致阻塞，而我們可以不查詢這些行，那么我們可以跳過鎖定行，比如在3)的情況下，其它查詢就可以跳過鎖定行。
    SELECT * FROM dbo.ShortMessage WITH (READPAST)

　　5) 指定使用行級鎖
　　假如我們需要在一個并發度很高的環境中做一個大規模的查詢，但是我們不希望這個查詢過于影響其它的查詢，而且本查詢的優先級又不高，那么我們可以指定使用行級鎖。
    SELECT * FROM dbo.ShortMessage WITH (ROWLOCK)

 總結
　　關于鎖定提示的使用上面簡單的舉了幾個例子，其實鎖定提示的用途還有很多，有待于各位讀者自己去發掘。
　　簡單的說，使用粒度更小的鎖，比如行級鎖，有助于提高系統的并發度，但是會增大鎖的開銷；而使用粒度更粗的鎖，比如表級鎖，有助于提高單個進程的性能，但是會損害系統的并發度。
　　而使用鎖定級別更高的鎖，比如排他鎖，能夠提高事務的隔離級別；而降低鎖定級別，比如不使用鎖，能夠提高系統的性能，但是會破壞事務的完整性。
關鍵字：SQL Server、數據庫、事務