# 發生 Blocking 了!! 怎麼辦？
###### tags: `Database` `troubleshooting`
## 關於 Blocking 本篇將討論以下幾個問題
:::info
### 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](https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-ver15)，當進行操作時會取得一條執行緒，blocking 是指在操作未完成之前會占用執行緒，而其他操作必須等待來確保資料的正確性。過多的 blocking 則可能會讓整個資料庫都無法再進行任何操作。
---
## 2. 如何查找 Blocking？
### 在開始查找之前，我們先試著重現出 blocking 的狀況
1. 建立測試用資料庫 TestBlocking，跟資料表 Id_Name
2. 使用 Update 但不 commit 來占用資料表
```SQL
Use TestBlocking
GO
Begin Tran
UPDATE Id_Name
SET Name='test'
WHERE Id=1
```
3. SELECT 被占用的資料表，此時應該會發現無法 正在執行查詢... 一直在轉圈圈
```SQL
Use TestBlocking
SELECT * FROM Id_Name
```
4. 可以試著查詢當前 blocking
```SQL
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 之間的關係
```SQL
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
-- 括號內為欲察看 SQL 的 session_id
-- dbcc inputbuffer(session_id)
dbcc inputbuffer(57)
```
---
## 3. 如何排除 Blocking？
### 到此我們已經找到造成 blocking 的 session_id 了，接下來只要排除這個 session_id 即可
刪除造成 blocking 的 session_id
```SQL
-- KILL session_id
KILL 57
```
---
## 4. 如何保留 Blocking Log？
### 前面說的都是 blocking 發生的當下才能查到，接下來我們使用 MS SQL 擴充事件來記錄 blocking 的 Log
1. 建立擴充事件
```SQL
-- 中括號內可自訂事件名稱
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. 啟用擴充事件
```SQL
ALTER EVENT SESSION BlockingLog
ON SERVER
-- 停用為 STOP
STATE = START;
```
3. 設定 blocking 多久要記錄，[MSDN](https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option?view=sql-server-ver15) 上範例為 20 秒
```SQL
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. [史丹利好熱](https://dotblogs.com.tw/stanley14/2017/11/21/find_sql_block2)
2. [Rock的SQL筆記本](https://dotblogs.com.tw/rockchang/2016/03/02/115933)
3. [MSDN](https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option?view=sql-server-ver15)
4. [DBA 黑白講](https://mssqltaiwan.wordpress.com/2017/03/13/table-lock-%E8%99%95%E7%90%86/)
5. [CaryHsu - 學無止盡](http://caryhsu.blogspot.com/2012/11/sql-server-dmv.html)
---
## 新手上路，若有錯誤還請告知，謝謝
Sign in with Wallet
Connect another wallet