SQL Server 2005鎖的問題

SQL Server通過鎖定資源來保證資料庫的一致性。SQL Server中的鎖不會對行、頁、表或索引等資源有實際影響，它更像一個預訂系統，所有任務在資料庫內預訂某些資源時都遵守它。過多的鎖或長時間持有的鎖會導致阻塞和其他問題，但鎖本身也可能產生一些問題。
1 解決鎖記憶體問題

為了確定SQL Server中鎖使用的記憶體量，可以監視SQL Server中的“鎖記憶體（KB）”計數器和系統監視器（Perfmon）中的“記憶體管理”物件。通過設定sp_configure中的鎖選項，可以修改SQL Server中鎖的記憶體配額。使用SQLServer：Locks計數器，可以瞭解更多關於鎖行為的細節。

如果系統中的鎖記憶體消耗完了，SQL Server不能分配更多的鎖記憶體，session會收到訊息1204：

 

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time.

Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

 

這個訊息說得很清楚：需要增加鎖的記憶體配額，或者減少系統中鎖的數量。

如果鎖佔用很大的記憶體，應該首先嚐試找出造成這麼多鎖的根本原因。例如，可能是SQL Server的鎖升級不充分。如果是這樣，就需要修改鎖的配置。一旦修改了鎖動態配置，就影響了鎖升級的行為，由此可能造成意外的影響。

如果資料庫不需要任何寫訪問，建議將其設定為只讀的。這會降低系統中產生的鎖的數量。在一個只讀的資料庫中，SQL Server仍會發行資料庫的共享鎖和讀表的意向共享鎖，但行鎖、頁鎖及SERIALIZABLE隔離級別的行鎖，都不會被髮行。例如，對於只在夜間更新的報表資料庫，使用者可以將在白天對資料庫的查詢設定為只讀的。這樣做對鎖記憶體的影響會降低，這也是SQL Server的鎖管理器必須做的。還可以在同一臺伺服器上對只讀資料庫建立資料庫快照，SQL Server不會在資料庫快照上發行共享鎖。

為了減少鎖記憶體，同樣建議將讀操作與寫操作分開。一種方法是把報表從一個OLTP系統中分開，通過建立報表伺服器和使用事務複製或SQL Server整合服務（SSIS）來為另一個使用者查詢讀操作的伺服器獲取資料。這會去掉OLTP主伺服器的共享鎖。如果資料庫伺服器能夠支援這種方法，可以考慮用一個資料庫快照來定期解除安裝讀操作。在本章後面我們還可以看到使用一種基於行版本的快照隔離級別來減少讀資料查詢產生的鎖。

2 鎖超時

預設狀態下，一個被阻塞的查詢會無限地等待一個未被滿足的鎖的請求。通過使用LOCK_TIMEOUT設定，可以指定一個session鎖等待的時間。當鎖超時發生時，session會收到訊息1222：

Lock request time out period exceeded.

使用LOCK_TIMEOUT給事務帶來了問題，因為錯誤1222發生後，SQL Server只是退出當前程式語句，而並沒有中止事務。因此需要在Transact-SQL程式碼中使用TRY/CATCH模組來捕獲1222錯誤。若發生了超時，可能需要回滾事務。若要了解更多內容，可以參考Inside SQL Server 2005：The Storage Engine（《Microsoft SQL Server 2005：儲存引擎》，電子工業出版社，2007）第8章的“設定鎖超時”。

3 鎖升級

SQL Server經常會鎖定表中獨立的行，尤其當更新和刪除比較少的行時。但執行大規模更新時，SQL Server選擇表中某行或某頁的鎖進行升級，以更好的使用鎖記憶體資源。但有時鎖升級會造成阻塞，我們希望減少鎖升級的數量（參考KB文章323630《如何解決SQL Server會中由鎖升級引起的阻塞問題》，獲取更多詳細資訊）。

檢測鎖升級有很多方法，最簡單的方法是使用SQL Trace/Profiler中的Lock：Escalation事件類。當升級發生時，該事件被觸發。但一個升級會有多個觸發，所以將它們繫結在一起很重要。

確保選擇Lock:Escalation事件類的預設列，這些列提供基本資訊。但我們新增以下列可能也很有用：TransactionID、DatabaseID、DatabaseName和ObjectID。因為可能看到trace中一個升級事件的多行，可以使用TransactionID將它們繫結在一起，特定物件（即表）可以使用ObjectID。

通過監視表鎖的數目或者和它們的持續時間，可以檢測到正在發生的升級。如果可以估計應用系統很少需要（或者曾經需要）表上的共享鎖或獨佔鎖，就可以推斷無論什麼時候我們看到這樣的鎖，它都由鎖升級產生。可以通過sys.dm_tran_locks DMV在給定的時間點探測表鎖。下面的查詢顯示了一個例項：

 

SELECT

request_session_id,

resource_type,

DB_NAME(resource_database_id) AS DatabaseName,

OBJECT_NAME(resource_associated_entity_id) AS TableName,

request_mode,

request_type,

request_status

FROM sys.dm_tran_locks AS TL

JOIN sys.all_objects AS AO

ON TL.resource_associated_entity_id = AO.object_id

WHERE request_type = ’LOCK’

AND request_status = ’GRANT’

AND request_mode IN (’X’,’S’)

AND AO.type = ’U’

AND resource_type = ’OBJECT’

AND TL.resource_database_id = DB_ID();

 

上面用來查詢表鎖的查詢引用了sys.all_objects的目錄檢視，所以返回資訊的範圍限制在查詢執行的資料庫上。由於sys.dm_tran_locks沒有返回鎖定物件更詳細的資訊，就沒有辦法得知這個物件是否是表。這樣一來，就必須加入返回那些資訊的資料庫的一些東西，而在這種情況下，sys.all_objects包含這些資訊，而且OBJECT_NAME()函式可以返回表的名稱。（例項見第1章“效能故障檢修方法”。）但是，它們都只返回當前資料庫的資訊。因此，查詢過濾器的最後一個條件限制了當前資料庫中那些資源的返回行。

另一種策略是使用sp_lock系統儲存過程，它返回鎖型別，從而可以檢視錶鎖。不幸的是，為了過濾sp_lock，必須抓取臨時資料，然後查詢它並在一個WHERE子句中過濾。可以從sp_lock儲存過程中提取key並執行它，但是它只適合於查詢sys.dm_tran_locks DMV並對其過濾。
解決鎖升級

防止多餘的鎖升級的最簡單的方法是減少以下批量操作的批次大小：插入、更新或刪除。例如，若必須執行批量更新，可以限制行數，或鎖數量的最大值5 000。我們需要嘗試找到防止升級的合適數值。SQL Server的查詢優化器可以檢測到表遍歷和任何的鎖升級。

目前，對每個表防止鎖升級最有趣的方法是在表上建立意向鎖，這樣SQL Server就不能升級鎖。通常鎖升級隻影響很少的查詢或隻影響某個查詢，我們可以關注有問題的表。微軟KB文章323630《如何解決SQL Server中由鎖升級引起的阻塞問題》給出了一個很好的例子。對於SQL Server例項資料庫AdventureWorks中的表Sales.SalesOrderDetail，若要防止鎖升級，如下程式碼可以使這張表在1小時內防止鎖升級：

 

BEGIN TRAN；

SELECT *

FROM Sales.SalesOrderDetail WITH (UPDLOCK, HOLDLOCK)

WHERE 1=0;

WAITFOR DELAY ’1:00:00’；

COMMIT；

這個查詢可以防止表Sales.SalesOrderDetail上的鎖升級(雖然事務日誌不會比沒有它增加更多）。當升級嘗試發生時，仍然可以在SQL Profiler中看到Lock:Escalation事務，但是通過檢查sys.dm_tran_locks，我們能驗證事務只採取了行鎖。不幸的是，這個請求使表的一個事務無期限地open，即使它並不鎖定任何行。而且，如果這張表有其他表的外來鍵或觸發器，SQL Server也會升級它的鎖，所以阻止一張表的鎖升級不像想象中那麼簡單。

一種有風險的選擇是完全關掉鎖升級。例如，可以設定跟蹤標識1211，它禁止整個SQL Server例項的鎖升級。問題是雖然這個選項可以減少阻塞，但是它會造成更多的鎖，因此鎖記憶體增加。如果系統的鎖記憶體消耗完，會使SQL Server停止或降低它的效能。還可以使用跟蹤標識1224，它禁止鎖升級，直到鎖管理器使用掉SQL Server例項的40%的非AWE動態分配記憶體。如果鎖記憶體的數量達到可用非AWE記憶體的60%，鎖記憶體就會被消耗完。

另一種降低鎖升級的方法是使用查詢的“行鎖”或“頁鎖”提示。這必須在每張表每個查詢上完成。遇到的問題和所有鎖提示的問題相同：查詢不能使用更優化的計劃。如果指定“頁鎖”提示，SQL Server仍然會在表鎖級別進行鎖升級，而且在其他不需要升級的情況下禁止使用行鎖，行鎖會有更好的行為。

還可以設定索引選項，通過SQL Server 2005的CREATE/ALTER INDEX語句可以改善sp_indexoption系統儲存過程的效能。從根本上說，通過SET選項設定ALLOW_ROW_LOCKS或ALLOW_PAGE_LOCKS為OFF，可以防止索引上的行鎖或頁鎖。這兩個選項的預設值是ON。這些選項有效地控制索引葉子節點的顆粒度，迫使SQL Server在一個較高的顆粒度起點使用鎖。重申一下，通常來說這些選項沒有很高的價值，因為它們阻止了可能受益於較低鎖顆粒度的查詢計劃。

如果由於讀操作產生鎖升級，我們可以試著去掉資料庫中的讀活動，通過使用資料庫快照，複製到報表資料庫上，或使用一個“快照隔離”級別。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-620542/，如需轉載，請註明出處，否則將追究法律責任。