發生 Blocking 了!! 怎麼辦？

關於 Blocking 本篇將討論以下幾個問題

1. 什麼是 Blocking？

2. 如何查找 Blocking？

3. 如何排除 Blocking？

4. 如何保留 Blocking Log？

測試環境：

MS SQL：SQL Server 2019 Linux

SSMS：Microsoft SQL Server Management Studio 18

1. 什麼是 Blocking？

SQL Server 會依據所在主機 CPU 數量提供不同的 max worker threads，當進行操作時會取得一條執行緒，blocking 是指在操作未完成之前會占用執行緒，而其他操作必須等待來確保資料的正確性。過多的 blocking 則可能會讓整個資料庫都無法再進行任何操作。

2. 如何查找 Blocking？

在開始查找之前，我們先試著重現出 blocking 的狀況

1. 建立測試用資料庫 TestBlocking，跟資料表 Id_Name

2. 使用 Update 但不 commit 來占用資料表

Use TestBlocking
GO
Begin Tran
UPDATE Id_Name
SET Name='test'
WHERE Id=1

3. SELECT 被占用的資料表，此時應該會發現無法 正在執行查詢... 一直在轉圈圈

Use TestBlocking
SELECT * FROM Id_Name

4. 可以試著查詢當前 blocking

WITH RootBlocking AS
 (
     SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests 
     WHERE blocking_session_id > 50 
     AND blocking_session_id  not In 
         ( SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 50 )
 )
 SELECT ses.session_id,ses.host_name,ses.program_name,ses.login_name,ses.status, ses.last_request_end_time,
     ct1.text sql_text,ct2.text recent_sql_text
 FROM RootBlocking rot
 INNER JOIN sys.dm_exec_connections con ON rot.blocking_session_id = con.session_id
 INNER JOIN sys.dm_exec_sessions ses ON rot.blocking_session_id = ses.session_id
 LEFT  JOIN sys.dm_exec_requests req ON rot.blocking_session_id = req.session_id
 OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) ct1
 OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) ct2

5. 確認正待等待的 session_id 之間的關係

SELECT [session_id] AS '正在等待的 session_id',
       [blocking_session_id] AS '佔住資源的 session_id'
FROM sys.[dm_os_waiting_tasks]
WHERE [wait_type] LIKE N'LCK%'
ORDER BY [wait_duration_ms];
GO

由查詢結果可推斷出 session_id 51 正在被 session_id 65 卡住，而 session_id 65 則是被 session_id 57 卡住，因此我們可以猜測 session_id 57 很可能就是造成此次 blocking 的罪魁禍首。

6. 接著取得此 session_id 的 SQL 語法

-- 括號內為欲察看 SQL 的 session_id
-- dbcc inputbuffer(session_id)

dbcc inputbuffer(57)

3. 如何排除 Blocking？

到此我們已經找到造成 blocking 的 session_id 了，接下來只要排除這個 session_id 即可

刪除造成 blocking 的 session_id

-- KILL session_id
KILL 57

4. 如何保留 Blocking Log？

前面說的都是 blocking 發生的當下才能查到，接下來我們使用 MS SQL 擴充事件來記錄 blocking 的 Log

1. 建立擴充事件

-- 中括號內可自訂事件名稱
CREATE EVENT SESSION [BlockingLog] ON SERVER 
ADD EVENT sqlserver.blocked_process_report(ACTION(sqlserver.database_name))
-- 指定紀錄 log 的路徑
ADD TARGET package0.event_file(SET filename=N'home\BlockingLog.xel')
WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=ON,
      STARTUP_STATE=OFF)
GO

2. 啟用擴充事件

ALTER EVENT SESSION BlockingLog
      ON SERVER
-- 停用為 STOP
STATE = START;

3. 設定 blocking 多久要記錄，MSDN 上範例為 20 秒

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 20 ;  
GO  
RECONFIGURE ;  
GO

4. 擴充事件位置 (資料庫→管理→擴充事件→工作階段→BlockingLog→package0.event_file)

5. 點開 package0.event_file 就會看到已記錄的 Log

6. 接著選擇任一筆 Log，接著點開下方詳細資料中的 blocked_process 可以看到兩段 SQL

上方 SQL 為 被封鎖的交易(被害人)
下方 SQL 為 封鎖其他人的交易(嫌犯)

總結

到此已經把如何排除 blocking 說明完畢，但儘管解決了眼前的問題，但實際上造成 blocking 的原因還是必須要找出來並予以排除，否則 blocking 的狀況一定會再次發生的。

參考資料

1. 史丹利好熱
2. Rock的SQL筆記本
3. MSDN
4. DBA 黑白講
5. CaryHsu - 學無止盡

新手上路，若有錯誤還請告知，謝謝