SQL Server 交易與鎖機制深度解析：ACID、鎖層級、相容性矩陣與死鎖排查

交易是資料庫一致性的基石，鎖是並發控制的手段。本文系統梳理 SQL Server 交易控制語句、ACID 特性、可鎖定資源層級、六大鎖模式、相容性矩陣、死鎖錯誤碼與排查思路。

一、交易控制語句

二、交易日誌運作

• 寫前日誌：資料頁修改前，先將邏輯操作寫入記憶體日誌緩衝區
• COMMIT 時強制將日誌刷入磁碟（WAL），保證持久性
• 檢查點 定期將髒頁刷盤，縮短復原時間

三、可鎖定資源層級（由粗到細）

1. Database：DDL 變更結構時
2. Table：整表鎖定，含資料列與所有索引鍵
3. Extent (區段)：8 頁 = 64KB
4. Page (頁)：8KB，資料或索引頁
5. Key (索引鍵)：索引中的特定鍵值範圍
6. Row / RID (列/列標識符)：最細粒度，實際鎖定 RID

SQL Server 採用鎖升級：細粒度鎖數量超過閾值（約 5000）自動升級為表鎖，可用 ALTER TABLE ... SET (LOCK_ESCALATION = AUTO/DISABLE) 控制。

四、六大鎖模式

五、鎖相容性矩陣（核心）

口訣：S 兼容 S/IS/IX；X 只兼容無；U 兼容 S/IS，不兼容 U/X/IX。意向鎖只與結構鎖衝突，不與資料鎖直接衝突。

六、死鎖與排查

• 錯誤碼：1205（Victim 選中者回滾，其他繼續）
• 捕捉：DBCC TRACEON(1222, -1)、Extended Events (xml_deadlock_report)、sys.dm_tran_locks 即時查看
• 常見成因：
  • 多交易以不同順序存取相同資源（表 A → B vs B → A）
  • 索引缺失導致掃描鎖升級為表鎖
  • 長交易持有鎖過久
• 預防：
  • 固定存取順序（如按 PK 排序）
  • 縮短交易範圍、避免交易中呼叫外部 API
  • 加強索引減少掃描範圍
  • 適當使用 ROWLOCK / UPDLOCK / HOLDLOCK Hint
  • 啟用 READ_COMMITTED_SNAPSHOT（RCSI）讓讀不阻寫

七、ACID 四大特性

八、隔離等級與現象對照

預設為 READ COMMITTED。高並發建議開啟 READ_COMMITTED_SNAPSHOT ON，以行版本替代共享鎖，大幅降低阻塞。