發生 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 的狀況
- 建立測試用資料庫 TestBlocking,跟資料表 Id_Name
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
- 使用 Update 但不 commit 來占用資料表
- SELECT 被占用的資料表,此時應該會發現無法 正在執行查詢… 一直在轉圈圈
- 可以試著查詢當前 blocking
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
- 確認正待等待的 session_id 之間的關係
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
由查詢結果可推斷出 session_id 51 正在被 session_id 65 卡住,而 session_id 65 則是被 session_id 57 卡住,因此我們可以猜測 session_id 57 很可能就是造成此次 blocking 的罪魁禍首。
- 接著取得此 session_id 的 SQL 語法
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
3. 如何排除 Blocking?
到此我們已經找到造成 blocking 的 session_id 了,接下來只要排除這個 session_id 即可
刪除造成 blocking 的 session_id
4. 如何保留 Blocking Log?
前面說的都是 blocking 發生的當下才能查到,接下來我們使用 MS SQL 擴充事件來記錄 blocking 的 Log
- 建立擴充事件
- 啟用擴充事件
- 設定 blocking 多久要記錄,MSDN 上範例為 20 秒
-
擴充事件位置 (資料庫→管理→擴充事件→工作階段→BlockingLog→package0.event_file)
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
-
點開 package0.event_file 就會看到已記錄的 Log
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
-
接著選擇任一筆 Log,接著點開下方詳細資料中的 blocked_process 可以看到兩段 SQL
上方 SQL 為 被封鎖的交易(被害人)
下方 SQL 為 封鎖其他人的交易(嫌犯)
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Learn More →
總結
到此已經把如何排除 blocking 說明完畢,但儘管解決了眼前的問題,但實際上造成 blocking 的原因還是必須要找出來並予以排除,否則 blocking 的狀況一定會再次發生的。
參考資料
- 史丹利好熱
- Rock的SQL筆記本
- MSDN
- DBA 黑白講
- CaryHsu - 學無止盡
新手上路,若有錯誤還請告知,謝謝