SqlServer中如何解決session阻塞問題

簡介

對于數(shù)據(jù)庫運維人員來說創(chuàng)建session或者查詢時產生問題是常規(guī)情況，下面介紹一種很有效且不借助第三方工具的方式來解決類似問題。

最近開始接觸運維工作，所以自己總結一些方案便于不懂數(shù)據(jù)庫的同事解決一些不太緊要的數(shù)據(jù)庫問題。類似方法很多理論也很多，我就不做深究，就是簡單寫一個方案，便于菜鳥使用的。

阻塞理解

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

例子

為了更好說明，下面用一個例子來介紹。創(chuàng)建一個表并插入數(shù)據(jù)，然后創(chuàng)建不同的session，同事阻塞session。具體的代碼截圖如下：

1.創(chuàng)建表Employee

2.插入測試數(shù)據(jù)

現(xiàn)在我們有了測試表，表中有12條數(shù)據(jù)，打開另一個查詢對話框在SSMS中（意味著重新創(chuàng)建了一個session）

3.在新的查詢窗口中首先要開啟事務，然后寫一個插入語句

在這個地方，我們能看到開啟了一個事務。但是沒有end tran 來終止事務，因此事務狀態(tài)為“open”，現(xiàn)在運行腳本來看一下當前看起的運行處于“open”狀態(tài)的session。

現(xiàn)在能夠看到如上圖展示一樣，運行的查詢正在open狀態(tài)的session。我們執(zhí)行了這個命令但是沒有完結它，DBA會聯(lián)系這個session的創(chuàng)建者來完成事務，或者回滾事務。

現(xiàn)在讓我們創(chuàng)建另一個session，更新一條記錄并且不提交，即讓查詢session的狀態(tài)為“open”。因此在新的查詢窗口中 寫一個語句來執(zhí)行如下：

這里會看到系統(tǒng)正在運行后沒有完成語句的狀態(tài)（因為上一個事務沒有關閉導致表鎖，這個不能插入），現(xiàn)在可以在另外的窗口查詢一下阻塞的情況，如下檢查阻塞的session。

如上所示，阻塞的session ID是58，由于我們更新查詢導致阻塞了54的執(zhí)行，54就是我們插入數(shù)據(jù)未提交的批處理。

現(xiàn)在我們能搞清楚阻塞的原因，也就可以從容解決阻塞了。

解決

方案1

在了解業(yè)務的情況下，可以直接使用kill session ID的語句來終止某個阻塞的session。

方案2

在執(zhí)行的事務的起始加入“set lock_timeout 1000” 語句，這表示如果阻塞超過1000毫秒，這個請求將被終止。

方案3

回滾或者提交事務。這個就不細說了。

下面是所有語句的代碼：

/****Creating dummy table Employee ****/ 
CREATE TABLE Employee ( Empid int NOT NULL, Name nchar(10) NULL, City nchar(10) NULL ) ON [PRIMARY] GO 
/**** Insert dummy data in Employee table *****/ 
Insert into Employee Values(1245,'George','Jax'), (1045,'Peter','Anadale'), (1157,'John','Dallas'), (1175,'Pete','Topeka'), (875,'Petron','Vienna'), 
(2311,'Kohli','Mumbai'), (1547,'Peter','Kansas'), (3514,'Abian','KHI'), (4251,'Ghani','Alexandria'), (957,'Ahmed','Vienna'), (1084,'Bhanu','Manderin'), 
(2954,'Ganeshan','Mcclean')
/***** Insert query in new session ****/ 
BEGIN TRAN Insert into Employee Values(1245,'George','Jax') 
/**** Query to check currently running sessions ****/ 
SELECT DISTINCT name AS database_name, session_id, host_name, login_time, login_name, reads, writes FROM sys.dm_exec_sessions 
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id 
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id 
WHERE resource_type > 'DATABASE' --AND name ='specific db name' 
ORDER BY name
/**** update query in new session ****/ 
update Employee set name = 'SHERAZ' where empid = 1245 
/**** Query to check blocking queries with session id ****/ 
SELECT session_id, blocking_session_id, text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle); 
/*** Command if you want to kill blocking session ****/ kill (54)

總結

自己也使用過多種不同的語句來查詢定位阻塞甚至死鎖，然后解決，這里也是介紹一種臨時解決方式。萬變不離其宗，歸根結底還是因為代碼甚至數(shù)據(jù)庫設計上存在很多問題才導致的阻塞，比如缺失索引、事務中的查詢性能和邏輯順序存在問題、T-SQL語句性能引起的等等不一而足。對于一些常年解決類似問題的DBA人員來說沒啥價值，但是對于不太理解數(shù)據(jù)庫的人來說還是能暫時解決一些緊急問題，當然最后還是要把理論基礎打好才能盡可能的杜絕類似情況。

以上所述是小編給大家介紹的SqlServer中如何解決session阻塞問題，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！