一、鎖的基本概念：
鎖定(Locking)是一個關系型數據庫系統的常規和必要的一部分，它防止對相同數據作 並發更新 或在更新過程中查看數據， 從而保證被更新數據的完整性。它也能防止用戶讀取正在被修改的數據 。Sql Server動態地管理鎖定，然而，還是很有必要 了解Transact- SQL查詢如何影響SQL Server中的鎖定。在此，簡單介紹下鎖的基本常識。
鎖定有助於防止並發問題的發生。當一個用戶試圖讀取另一個用戶正在修改的數據，或者修改另一個用戶正在讀取的數據時，或者嘗試修改另一個事務正在嘗試修改的數據時，就會出現並發問題。
SQL Server資源會被鎖定，資源的鎖定方式稱作它的鎖定模式(lock mode)，下表列出SQL Server處理的主要鎖定模式：
名稱
描述
共享 (S) 用於不更改或不更新數據的讀取操作，如 SELECT 語句。 更新 (U) 用於可更新的資源中。 防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。 排他 (X) 用於數據修改操作，例如 INSERT、UPDATE 或 DELETE。 確保不會同時對同一資源進行多重更新。 意向 用於建立鎖的層次結構。 意向鎖包含三種類型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。 架構 在執行依賴於表架構的操作時使用。 架構鎖包含兩種類型：架構修改 (Sch-M) 和架構穩定性 (Sch-S)。 大容量更新 (BU) 在向表進行大容量數據復制且指定了 TABLOCK 提示時使用。 鍵范圍 當使用可序列化事務隔離級別時保護查詢讀取的行的范圍。 確保再次運行查詢時其他事務無法插入符合可序列化事務的查詢的行。可以鎖定SQL Server中的各種對象，既可以是一個行，也可以是一個表或數據庫。可以鎖定的資源在粒度（granularity）上差異很大。從細（行）到粗（數據庫）。細粒度鎖允許更大的數據庫並發，因為用戶能對某些未鎖定的行執行查詢。然而，每個由SQL Server產生的鎖都需要內存，所以數以千計獨立的行級別的鎖也會影響SQL Server的性能。粗粒度的鎖降低了並發性，但消耗的資源也較少。下表介紹SQL Server可以鎖定的資源：
資源
說明
KEY 索引中用於保護可序列化事務中的鍵范圍的行鎖。 PAGE 數據庫中的 8 KB 頁，例如數據頁或索引頁。 EXTENT 一組連續的八頁，例如數據頁或索引頁。 HoBT 堆或 B 樹。 用於保護沒有聚集索引的表中的 B 樹（索引）或堆數據頁的鎖。 TABLE 包括所有數據和索引的整個表。 FILE 數據庫文件。 RID 用於鎖定堆中的單個行的行標識符。 APPLICATION 應用程序專用的資源。 METADATA 元數據鎖。 ALLOCATION_UNIT 分配單元。 DATABASE 整個數據庫。不是所有的鎖都能彼此兼容。例如，一個被排他鎖鎖定的資源不能被再加其他鎖。其他事務必須等待或超時，直到排他鎖被釋放。被更新鎖鎖定的資源只能接受其他事務的共享鎖。被共享鎖鎖定的資源還能接受其他的共享鎖或更新鎖。
SQL Server自動分配和升級鎖。升級意味著細粒度的鎖（行或頁鎖）被轉化為粗粒度的表鎖。當單個T－SQL語句在單個表或索引上獲取5000多個鎖,或者SQL Server實例中的鎖數量超過可用內存阈值時，SQL Server會嘗試啟動鎖升級。鎖占用系統內存，因此把很多鎖轉化為一個較大的鎖能釋放內存資源。然而，在釋放內存資源的同時會降低並發性。
SQL Server 2008帶來了新的表選項，可以禁用鎖升級或在分區（而不是表）范圍啟用鎖升級。
二、查看鎖的活動
下面演示一個實例，它使用sys.dm_tran_locks動態視圖監視數據庫中鎖的活動。
打開一個查詢窗口，執行如下語句：
復制代碼 代碼如下:
USE AdventureWorks
BEGIN TRAN
SELECT ProductID, ModifiedDate
FROM Production.ProductDocument
WITH (TABLOCKX)
打開另一個查詢窗口，執行：
復制代碼 代碼如下:
SELECT request_session_id sessionid,
resource_type type,
resource_database_id dbid,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) objectname,
request_mode rmode,
request_status rstatus
FROM sys.dm_tran_locks
WHERE resource_type IN ('DATABASE', 'OBJECT')
執行結果：
復制代碼 代碼如下:
/*
sessionid type dbid objectname rmode rstatus
51 DATABASE 4 NULL S GRANT
52 DATABASE 4 NULL S GRANT
53 DATABASE 8 NULL S GRANT
56 DATABASE 8 NULL S GRANT
53 OBJECT 8 ProductDocument X GRANT
*/
解析：本示例中，我們首先啟動了一個新事務，並使用TABLOCKX鎖提示（這個提示對表放置了排他鎖），對Production.ProductDocument表執行了一個查詢。查詢sys.dm_tran_locks動態管理視力可以監視當前SQL Server實例中打開了哪些鎖。它返回了AdventureWorks數據庫中活動鎖的列表。可以在結果中的最後一行看到ProductDocument表上的排他鎖。
前三列定義了會話鎖、資源類型和數據庫ID。第四列使用了Object_Name函數，注意它使用了兩個參數（對象ID和數據庫ID）來指定訪問哪個名稱（第二個參數是SQL Server 2005 SP2引入的，它用來指定為了轉換對象名稱而使用哪個數據庫）。同時也查詢鎖定請求模式和狀態，最後，From子句引用DMV，用Where子句指定了兩個資源類型。Resource_Type指定了鎖定的資源類型，如Database\Object\File\Page\Key\RID\Extent\Metadata\Application\Allocation_Unit或HOBT類型。依賴資源類型的resource_associated_entity_id，確定ID是object ID, allocation unit ID, 或Hobt ID。
■如果resource_associated_entity_id列包含Object ID(資源類型為Object),可以使用sys.objects目錄視圖來轉換名稱。
■如果resource_associated_entity_id列包含allocation unit ID(資源類型為Allocation_Unit),可以引用sys.allocatiion_units和contain_id聯結到sys.partitions上，就可以確定object ID。
■如果resource_associated_entity_id列包含Hobt ID(資源類型為Key\page\Row或HOBT),可以直接引用sys.partitions,然後查找相應的Object ID。
■對於Database、Extent、 Application或MetaData的資源類型，resource_associated_entity_id列將為0。
使用sys.dm_tran_locks能對無法預料的並發問題進行故障調試。例如，一個查詢會話占用鎖的時間可能比預期時間長而被鎖，或者鎖的粒度或鎖模式不是我們所期望的（可能是希望使用表鎖而不是更小粒度的行鎖或頁鎖）。理解鎖處於的鎖定級別有助於我們更有效地對查詢的並發問題進行故障調試。
三、控制表的鎖升級行為
每個在SQL Server中創建的鎖都會消耗內存資源。當鎖的數量增加時，內存就會減少。如果鎖的內存使用百分比超過一個特定阈值，SQL Server會將細粒度鎖（頁或行）轉換為粗粒度鎖（表鎖）。這個過程稱為鎖升級。鎖升級可以減少SQL Server實例占有的鎖數量，減少鎖內存的使用。
雖然細粒度會消耗更多的內存，但由於多個查詢可以訪問未鎖定的行，因此也會改善並發性。引入表鎖可能會減少內存的消耗，但也會帶來阻塞，這是因為一條查詢鎖住了整個表。根據使用數據庫的應用程序，這個行為可能是不希望發生的，而且你可能希望當SQL Server實施鎖升級時盡量獲得更多的控制。
SQL Server 2008引入了使用ALter table命令在表級別控制鎖升級的功能。現在可以從如下3個設置中選擇：
■Table 這是SQL Server 2005中使用的默認行為。當設置為該值時，在表級別啟用了鎖升級，不論是否為分區表。
■Auto 如果表已分區，則在分區級別（堆或B樹）啟用鎖升級。如果表未分區，鎖升級將發生在表級別上。
■Disable 在表級別刪除鎖升級。注意，對於用了TABLOCK 提示或使用可序列化隔離級別下Heap的查詢時，你仍然可能看到表鎖。
下面示例演示了修改表的新設置：
復制代碼 代碼如下:
ALTER TABLE Person.Address
SET (LOCK_ESCALATION = AUTO)
--注意這句在SQL Server 2005下會出錯
SELECT lock_escalation,lock_escalation_desc
FROM sys.tables
WHERE name='Address'
/*
lock_escalation lock_escalation_desc
2 AUTO
*/
下來，我們禁用鎖升級：
復制代碼 代碼如下:
ALTER TABLE Person.Address
SET ( LOCK_ESCALATION = DISABLE)
SELECT lock_escalation,lock_escalation_desc
FROM sys.tables
WHERE name='Address'
/*
lock_escalation lock_escalation_desc
1 DISABLE
*/
說明：在更改了這個配置後，可以通過查詢sys.tables目錄視圖的lock_escalation_desc列來驗證這個選項。
注意：如果表未分區，通常情況為表級別升級。如果你指定了Disable選項，將不會出現表級別的鎖升級。這會提高並發性，但如果你請求訪問大量的行或頁，會增加內存的消耗。
邀月 來自 http://www.cnblogs.com/downmoon
