Locking 每次讀寫都在發生、正常；當一個交易持鎖、另一個要相同資源只能等，就是 blocking；兩邊各持對方要的鎖、誰都動不了，就升級成 deadlock。這頁不談鎖的理論，只談 production 卡住或死鎖時，怎麼查、怎麼解。
🧠 Intuition
TIP
三個詞要分清楚：Locking 是協調存取的機制（一直發生）、Blocking 是「B 在等 A 放鎖」的等待、Deadlock 是等待成了環（必有一方被犧牲）。查 blocking 先找 head blocker（那條沒在等別人、卻擋住一串的交易）；查 deadlock 先拿到 deadlock graph。
- Blocking 的根源是 Isolation：為了讓交易互不干擾就得拿鎖、就得等；降 blocking 的核心是挑對 isolation level，而不是拿掉鎖。
- 鎖有粒度：從單列（RID/KEY）、頁（PAGE）、到整表（TAB）。粒度太細會佔記憶體，太粗會擋住一大片——中間的自動切換就是 lock escalation。
- 一條沒索引的查詢會 scan 整表，掃過的列全要拿 S 鎖；即使最後只回 1 列，也可能鎖掉上百萬列數秒，把 blocking 範圍放到最大。
- Deadlock 是效能問題不只是錯誤：victim 回滾要耗資源、使用者通常重送再吃一次資源，短時間內一堆 deadlock 等於系統一直在做白工。
⚖️ Tradeoffs
觀測 Blocking — 先找 head blocker
sys.dm_exec_requests的blocking_session_id直接告訴你「誰被誰擋」；順著鏈往上找到那條blocking_session_id = 0、自己沒在等的，就是 head blocker。sys.dm_tran_locks看單一 session 實際持有/等待哪些鎖：resource_type（RID/KEY/PAGE/OBJECT）、request_mode（S/X/U/IS/IX/SIX）、request_status（GRANT已取得 /WAIT被擋）。- SSMS 內建 Activity Monitor 能看 lock waits 與 head blocker，是臨場快速定位的工具。
- 長期觀測靠 Extended Events 的
blocked_process_report：把blocked process threshold設 5–10 秒，超過門檻就吐 XML 報告供事後分析——production 最低成本的做法。
Blocking 的常見成因 — 鎖範圍太大或持太久
- 缺索引：查詢被迫全表掃描，鎖範圍暴增；設計索引讓鎖精準落在 KEY/RID 而非 PAGE/TAB。
- 交易太長：把外部 API 呼叫、使用者輸入包進 transaction，鎖就一直握著；持鎖越久、後面等的人越多。
- isolation 過高：預設的 Read Committed 讀完即放 S 鎖，但更高的級別（Repeatable Read/Serializable）會把鎖持有到 commit，等待鏈更長。
- cursor / 逐列處理：逐列鎖定又慢，是 blocking 與 deadlock 的溫床，能改 set-based 就改。
Lock Escalation — 省記憶體卻擋住整表
- 太多細粒度鎖（每列一把 RID/KEY）會吃爆記憶體，SQL Server 會把幾千把細鎖換成一把表級鎖。
- 觸發門檻大約是單一語句鎖到 5,000 列，升級目標是 TABLE(或 partition table 的 HoBT)。
- 副作用：對 OLTP 是災難——一張表突然被獨佔，所有讀寫全卡住。
- 緩解：批次操作分塊、每批 < 5,000 列；partition table 設
LOCK_ESCALATION = AUTO；用索引讓鎖落在 KEY。
Deadlock 的三種典型成因
- 循環依賴 (cycle)：A 拿 R1 想要 R2、B 拿 R2 想要 R1——存取順序不一致造成的環。
- 鎖升級衝突 (conversion)：A、B 各持同一資源的 S 鎖，同時想升級成 X，互相卡死。
- trigger 偷加的存取：input 看似只動一張表，但 trigger 暗中又 UPDATE 了另一張，湊成循環——deadlock 報告的
<executionStack>若含 trigger 名稱，它通常是真兇。 - 內部的 lock monitor 持續掃描、發現循環就選一個估算成本較低的當 victim 回滾，讓另一個繼續（標準錯誤是
Msg 1205）。
拿到並解讀 Deadlock Graph
- 三件必須拿到的資訊：Sessions（誰跟誰打架）、Resources（爭哪些鎖）、Queries（實際的 SQL）。
- 免費第一站：SQL Server 自 2008 起預設開啟的
system_healthExtended Events session 自動記錄 deadlock graph，什麼都不用設；但它用固定大小檔案輪替、舊資料會被覆寫，要長期保存得自建 XE。 - 自訂 XE 用
xml_deadlock_report（含查詢文字，最好找根因）或xml_deadlock_report_filtered（去掉查詢文字以符合隱私規範）；另有 Trace flag 1222 落地到 error log。 - 用 SSMS 開 graph：被打 X 的 process 是 victim；XML 的
<inputbuf>是使用者送出的查詢、<executionStack>顯示真正觸發鎖的內部呼叫（如 trigger）。
化解與預防 — 重試是補丁，根因要修
- 統一存取順序：讓所有交易都以固定順序（先表 A、再表 B）操作，就永遠不會形成循環——這是 deadlock 的根本解。
- 縮短交易 + 好索引：把昂貴查詢、外部呼叫移出 transaction；用索引縮小鎖範圍、降低碰撞機率。
- 降 isolation / 用快照：多數 OLTP 開 RCSI 是最佳折衷（讀者不擋寫者、寫者不擋讀者，仍維持 read committed 語意），能消除大量 read-vs-write 衝突；代價是 tempdb 要存 row version。不要用
WITH (NOLOCK)換快，那是把問題變成 dirty data。 - 重試 victim：deadlock 是可預期的執行時錯誤，程式應捕捉
ERROR_NUMBER() = 1205、小退避後有限次重試——但重試是補丁，不能取代修根因。
🔑 Takeaways
- Locking ≠ Blocking ≠ Deadlocking：鎖一直在發生、blocking 是等待、deadlock 是等待成環必有人被犧牲；查 blocking 先找 head blocker、查 deadlock 先拿 deadlock graph。
- 觀測靠 DMV 與事件：
sys.dm_exec_requests（blocking_session_id看誰擋誰）、sys.dm_tran_locks（看實際持/等的鎖），長期用blocked_process_report與system_health的 deadlock graph。 - 缺索引與長交易是 blocking 的兩大成因，lock escalation（約 5,000 列升表鎖）是 OLTP 殺手；deadlock 三大根因是不一致存取順序、鎖升級衝突、trigger 暗中存取。
- 解法優先序：統一存取順序 + 縮短交易 + 對的索引 + RCSI；重試被選為 victim 的交易只是補丁，別靠
DEADLOCK_PRIORITY或NOLOCK掩蓋根因。
No notes yet — jot your takeaways or Q&A here.