萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 事務與鎖機制深度解析:ACID、鎖層級、模式、兼容性與死鎖

事務標記點

  • BEGIN TRAN:起始點。
  • COMMIT TRAN:永久提交,寫入日志強制刷盤(WAL)。
  • ROLLBACK TRAN:回滾到事務初始或保存點。
  • SAVE TRAN SavePoint:部分回滾標記,ROLLBACK TRAN SavePoint 僅撤銷其後操作。

事務日志工作原理 (WAL)

  1. 修改先寫日志緩衝區(記錄 Redo/Undo 信息)。
  2. 日志刷盤(強制寫入 LDF)先於數據頁刷盤。
  3. Checkpoint 定期將髒頁刷盤,截斷非活動日志(簡單模式)或等待日志備份(完整模式)。

可鎖定資源層級(粒度由粗到細)

  1. Database:DDL、備份、恢復時。
  2. Table / Heap / B-Tree:整表鎖(含所有索引)。
  3. Extent (8 頁):區段鎖,罕見顯式獲取。
  4. Page (8KB):頁鎖,常見於範圍掃描、索引維護。
  5. Key / Key Range:索引鍵鎖/範圍鎖(可序列化隔離級別防幻讀)。
  6. Row / RID:行鎖(堆表用 RID,聚集表用 Key)。

引擎自動升級:行鎖超過閾值 (默認 5000) → 嘗試升級為表鎖(可用 ALTER TABLE SET (LOCK_ESCALATION = AUTO/DISABLE) 控制)。

六大鎖模式與兼容性矩陣

模式縮寫用途兼容 (✓=兼容)
共享鎖S讀取 (SELECT)S✓, U✓, X✗, IS✓, IX✓, SIX✓
更新鎖U尋找待更新行(預防死鎖)S✓, U✗, X✗, IS✓, IX✗, SIX✗
排他鎖X修改 (INSERT/UPDATE/DELETE)全不兼容
意向共享IS意向在下層加 SS✓, U✓, X✗, IS✓, IX✓, SIX✓
意向排他IX意向在下層加 XS✗, U✗, X✗, IS✓, IX✓, SIX✗
共享意向排他SIX表級 S + 行級 IXS✓, U✗, X✗, IS✓, IX✗, SIX✗

鎖兼容性矩陣圖

特殊鎖類型

  • Schema Lock:Sch-M (DDL 修改結構)、Sch-S (查詢編譯穩定結構)。
  • Bulk Update Lock (BU):BULK INSERT / BCP 並行加載時獲取,允許並發 BU,阻塞其他鎖。

死鎖與處理

  • 錯誤號 1205:死鎖受害者被選中回滾。
  • 死鎖圖:TRACE FLAG 1222 或 Extended Events (xml_deadlock_report) 捕獲分析。
  • 預防:固定訪問順序、縮短事務、避免用戶交互、使用 UPDLOCK / HOLDLOCK 提示、行版本控制 (RCSI/SI)。

ACID 解讀

  1. Atomicity:全做或全不做(日志 Undo 保證)。
  2. Consistency:約束、觸發器、級聯動作全部通過,數據庫從一致狀態轉移到一致狀態。
  3. Isolation:四大隔離級別(Read Uncommitted / Read Committed / Repeatable Read / Serializable)控制並發異常(髒讀、不可重複讀、幻讀)。SQL Server 預設 RC,RCSI 用行版本實現讀不阻塞寫。
  4. Durability:Commit 後日志強制刷盤,掉電恢復重做 保證持久。
copyright © 萬盛學電腦網 all rights reserved