SQL2008中SQL應用之-阻塞(Blocking)應用分析
在SQL Server中,一個阻塞的進程會無限期地保持阻塞,或者直到它超時(根據(jù)set lock_timeout)、服務器關閉、進程被殺死、連接完成了更新或者其他發(fā)生在原始事務上的操作導致它釋放了資源上的鎖。
發(fā)生長時間阻塞的原因如下:
1、在一個沒有索引的表上的過量的行鎖會導致SQL Server得到一個鎖,從而阻塞其他事務。
2、應用程序打開一個事務,并在事務保持打開的時候要求用戶進行反饋或交互。這通常是讓最終用戶在GUI上輸入數(shù)據(jù)而保持事務打開的時候發(fā)生。此時,事務引用的任何資源都會被占據(jù)。
3、事務BEGIN后查詢的數(shù)據(jù)可能在事務事務開始前被調用
4、查詢不恰當?shù)厥褂面i定提示。例如,應用程序僅使用很少的行,但卻使用一個表鎖提示
5、應用程序使用長時間運行的事務,在一個事務中更新了很多行或很多表(把一個大量更新的事務變成多個更新較少的事務有助于改善并發(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文本,然后強制結束進程。
強制結束進程,我們使用kill命令。kill的用法,請參看MSDN:http://msdn.microsoft.com/zh-cn/library/ms173730.aspx
該命令有三個參數(shù):
■session ID 要終止的進程的會話 ID。session ID 是在建立連接時為每個用戶連接分配的唯一整數(shù) (int)。在連接期間,會話 ID 值與該連接捆綁在一起。連接結束時,則釋放該整數(shù)值,并且可以將它重新分配給新的連接。使用 KILL session ID 可終止與指定的會話 ID 關聯(lián)的常規(guī)非分布式事務和分布式事務。
■UOW 標識分布式事務的工作單元 (UOW) ID。UOW 是可從 sys.dm_tran_locks 動態(tài)管理視圖的 request_owner_guid 列中獲取的 GUID。也可從錯誤日志中或通過 MS DTC 監(jiān)視器獲取 UOW。有關監(jiān)視分布式事務的詳細信息,請參閱 MS DTC 文檔。使用 KILL UOW 可終止孤立的分布式事務。這些事務不與任何真實的會話 ID 相關聯(lián),與虛擬的會話 ID = '-2' 相關聯(lián)??墒箻俗R孤立事務變得更為簡單,其方法是查詢 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,該命令只顯示當前的回滾進度。
在第一個查詢窗口:
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ù)化計劃緩存(預編譯)。
我們強制終止會話。在第三個窗口中執(zhí)行:
kill 52
注意:窗口一的語句和窗口二的語句均終止。
提示:第三個語句中,使用sys.dm_exec_connections(DMV)返回了Session ID為53的most_recent_sql_handle列。這是SQL文本在內存中的指針。作為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)原始的阻塞進程。
二、配置語句等待鎖釋放的時長
如果有一個事務或語句被阻塞,意味著它在等待資源上的鎖被釋放。我們可以事先通過set lock_Timeout來設定需要等待的時間。
語法如下: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
/*
1秒后的執(zhí)行結果
Msg 1222, Level 16, State 51, Line 3
Lock request time out period exceeded.
The statement has been terminated.
*/
解析:在這個示例中,我們設置了鎖超時時間為1000毫秒,即1秒。這個設置不會影響資源被進程占有的時間,只會影響等待另一個進程釋放資源訪問的時間。
相關文章
win2003 Server配置SQL Server 2005遠程連接的方法
這篇文章主要介紹了win2003 Server配置SQL Server 2005遠程連接的方法,需要的朋友可以參考下2015-01-01SQLServer 2005系統(tǒng)配置要求官方說明
SQLServer 2005系統(tǒng)配置要求官方說明,需要安裝sql2005的朋友需要了解下。2009-08-08MS-sql 2005拒絕了對對象 ''xxx'' (數(shù)據(jù)庫 ''xxx'',架構 ''dbo'')的 SELECT 權
訪問了提示“MS-sql 2005拒絕了對對象 'xxx' (數(shù)據(jù)庫 'xxx',架構 'dbo')的 SELECT 權限”的錯誤2008-05-05SqlServer 2005 T-SQL Query 學習筆記(3)
利用ROW_NUMBER()進行高效率的分頁。2010-02-02SQL Server 移動系統(tǒng)數(shù)據(jù)庫
SQL Server中系統(tǒng)數(shù)據(jù)庫有master、model、msdb、tempdb四個數(shù)據(jù)庫,對于一般的庫,我們要移動他們的位置,只需分離附加即可,而這些系統(tǒng)數(shù)據(jù)庫沒有分離的選項,那要怎么移動他們呢?2016-05-05SQL 2005使用專用管理員連接(DAC)的技巧及修改系統(tǒng)表的方法
SQL Server 2005 為管理員提供了一種特殊的診斷連接,以供在無法與服務器建立標準連接時使用。2011-07-07