當(dāng)一個數(shù)據(jù)庫會話中的事務(wù)正鎖定一個或多個其他會話事務(wù)想要讀取或修改的資源時，會產(chǎn)生阻塞(Blocking)。通常短時間的阻塞沒有問題，且是較忙的應(yīng)用程序所需要的。然而，設(shè)計糟糕的應(yīng)用程序會導(dǎo)致長時間的阻塞，這就不必要地鎖定了資源，而且阻塞了其他會話讀取和更新它們。

在SQL Server中，一個阻塞的進程會無限期地保持阻塞，或者直到它超時（根據(jù)set lock_timeout）、服務(wù)器關(guān)閉、進程被殺死、連接完成了更新或者其他發(fā)生在原始事務(wù)上的操作導(dǎo)致它釋放了資源上的鎖。

發(fā)生長時間阻塞的原因如下：

1. 在一個沒有索引的表上的過量的行鎖會導(dǎo)致SQL Server得到一個鎖，從而阻塞其他事務(wù)。
2. 應(yīng)用程序打開一個事務(wù)，并在事務(wù)保持打開的時候要求用戶進行反饋或交互。這通常是讓最終用戶在GUI上輸入數(shù)據(jù)而保持事務(wù)打開的時候發(fā)生。此時，事務(wù)引用的任何資源都會被占據(jù)。
3. 事務(wù)BEGIN后查詢的數(shù)據(jù)可能在事務(wù)事務(wù)開始前被調(diào)用
4. 查詢不恰當(dāng)?shù)厥褂面i定提示。例如，應(yīng)用程序僅使用很少的行，但卻使用一個表鎖提示
5. 應(yīng)用程序使用長時間運行的事務(wù)，在一個事務(wù)中更新了很多行或很多表（把一個大量更新的事務(wù)變成多個更新較少的事務(wù)有助于改善并發(fā)性）

一、找到并解決阻塞進程

下面我們演示使用SQL Server動態(tài)管理視圖sys.dm_os_waiting_tasks找出阻塞進程，該視圖用于代替早期SQL Server版本中的系統(tǒng)存儲過程sp_who

找出阻塞的進程后，我們使用sys.dm_exec_sql_text動態(tài)管理函數(shù)和sys.dm_exec_Connections（DMV）找出正在執(zhí)行的查詢的SQL文本，然后強制結(jié)束進程。

強制結(jié)束進程，我們使用kill命令。kill的用法，請參看MSDN:http://msdn.microsoft.com/zh-cn/library/ms173730.aspx

該命令有三個參數(shù)：

• session ID    要終止的進程的會話 ID。session ID 是在建立連接時為每個用戶連接分配的***整數(shù) (int)。在連接期間，會話 ID 值與該連接捆綁在一起。連接結(jié)束時，則釋放該整數(shù)值，并且可以將它重新分配給新的連接。使用 KILL session ID 可終止與指定的會話 ID 關(guān)聯(lián)的常規(guī)非分布式事務(wù)和分布式事務(wù)。
• UOW    標識分布式事務(wù)的工作單元 (UOW) ID。UOW 是可從 sys.dm_tran_locks 動態(tài)管理視圖的 request_owner_guid 列中獲取的 GUID。也可從錯誤日志中或通過 MS DTC 監(jiān)視器獲取 UOW。有關(guān)監(jiān)視分布式事務(wù)的詳細信息，請參閱 MS DTC 文檔。使用 KILL UOW 可終止孤立的分布式事務(wù)。這些事務(wù)不與任何真實的會話 ID 相關(guān)聯(lián)，與虛擬的會話 ID = '-2' 相關(guān)聯(lián)?？墒箻俗R孤立事務(wù)變得更為簡單，其方法是查詢 sys.dm_tran_locks、sys.dm_exec_sessions 或 sys.dm_exec_requests 動態(tài)管理視圖中的會話 ID 列。
• WITH STATUSONLY    生成由于更早的 KILL 語句而正在回滾的指定 session ID 或 UOW 的進度報告。KILL WITH STATUSONLY 不終止或回滾 session ID 或 UOW，該命令只顯示當(dāng)前的回滾進度。

在***個查詢窗口：

BEGIN TRAN  
UPDATE Production.ProductInventory  
SET Quantity = 400  
WHERE ProductID = 1 AND 
LocationID = 1 

第二個窗口：

UPDATE Production.ProductInventory  
SET Quantity = 406  
WHERE ProductID = 1 AND 
LocationID = 1 

第三個窗口：

SELECT blocking_session_id, wait_duration_ms, session_id  
FROM sys.dm_os_waiting_tasks  
WHERE blocking_session_id IS NOT NULL 
 
/*blocking_session_id    wait_duration_ms    session_id  
           52                  23876             54  
*/ 

可以看出是SessionID為52的會話阻塞了SessionID為54的會話。

那么，52正在干啥壞事呢？在第三個窗口中執(zhí)行：

SELECT t.text  
FROM sys.dm_exec_connections c  
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t  
WHERE c.session_id = 54  
 
/*text(@1 int,@2 tinyint,@3 tinyint)  
UPDATE [Production].[ProductInventory] set [Quantity] = @1    
WHERE [ProductID]=@2 AND [LocationID]=@3  
*/ 

注意：這并不是***個查詢窗口中的原SQL語句，SQL Server進行了自動參數(shù)化計劃緩存（預(yù)編譯）。

我們強制終止會話。在第三個窗口中執(zhí)行：

kill 52 注意：窗口一的語句和窗口二的語句均終止。

提示：第三個語句中，使用sys.dm_exec_connections(DMV)返回了Session ID為53的most_recent_sql_handle列。這是SQL文本在內(nèi)存中的指針。作為sys.dm_exec_sql_text動態(tài)管理函數(shù)的輸入?yún)?shù)使用。從sys.dm_exec_sql_text返回了text列，該列顯示了阻塞進程的SQL文本。如果阻塞成串，必須通過blocking_session_id和session_ID列仔細查看每一個阻塞進程，直到發(fā)現(xiàn)原始的阻塞進程。

二、配置語句等待鎖釋放的時長

如果有一個事務(wù)或語句被阻塞，意味著它在等待資源上的鎖被釋放。我們可以事先通過set lock_Timeout來設(shè)定需要等待的時間。

語法如下：SET LOCK_TIMEOUT time_period

參數(shù)以毫秒為單位。超過時會返回鎖定錯誤。示例：

在***個窗口中執(zhí)行：

USE AdventureWorks  
BEGIN TRAN  
UPDATE Production.ProductInventory  
SET Quantity = 400  
WHERE ProductID = 1 AND 
LocationID = 1 

在第二個窗口中執(zhí)行：

USE AdventureWorks  
SET LOCK_TIMEOUT 1000  
UPDATE Production.ProductInventory  
SET Quantity = 406  
WHERE ProductID = 1 AND 
LocationID = 1  
 
/*１秒后的執(zhí)行結(jié)果Msg 1222, Level 16, State 51,Line 3  
Lock request time out period exceeded.  
The statement has been terminated.  
*/ 

解析:在這個示例中，我們設(shè)置了鎖超時時間為1000毫秒，即１秒。這個設(shè)置不會影響資源被進程占有的時間，只會影響等待另一個進程釋放資源訪問的時間。

原文鏈接：http://www.cnblogs.com/downmoon/archive/2011/02/18/1957913.html

 【編輯推薦】

1. SQL Server 2008中SQL應(yīng)用之一“死鎖(Deadlocking)”
2. SQL Server 2008中SQL應(yīng)用之二“鎖定(locking)”
3. 淺析SQL Server 2008中的代碼安全之二：DDL觸發(fā)器與登錄觸發(fā)器
4. 淺析SQL Server 2008中的代碼安全之三：通過PassPhrase加密
5. 淺析SQL Server 2008中的代碼安全之四：主密鑰