發生 Blocking 了!! 怎麼辦?


Posted by WayneCheng on 2020-11-03

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

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


#Database #troubleshooting #MSSQL







Related Posts

8 月開始上班,切換行銷與寫程式的腦袋

8 月開始上班,切換行銷與寫程式的腦袋

這次,我是芬蘭極地導遊分享會「聽眾」

這次,我是芬蘭極地導遊分享會「聽眾」

JavaScript - 對 Closure 閉包的初步理解

JavaScript - 對 Closure 閉包的初步理解


Comments