發生 Blocking 了!! 怎麼辦？

tags: Database troubleshooting

關於 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 - 學無止盡

---

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