萬盛學電腦網

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

SQL Server 鎖機制深度解析:粒度、模式、升級、四大並發問題與對應隔離等級

四大並發問題(鎖要解決的痛點)

問題場景後果
丟失更新兩事務讀同行,基於舊值更新後提交覆蓋先提交
髒讀事務 B 讀取事務 A 未提交的修改依據髒資料決策,A 回滾後資料不存在
不可重複讀事務 A 兩次讀同行,中間事務 B 修改提交兩次結果不一致
幻讀事務 A 範圍查詢,事務 B 插入新行滿足條件再次查詢多出「幻影」行

鎖粒度階層(由細到粗)

  • Row (RID/Key):最細,並發最高,鎖結構開銷大;
  • Page (8 KB):SQL 7.0 前預設,鎖住整頁(含無關行);
  • Extent (8 頁 = 64 KB):空間配置專用(建表/建索引),不鎖資料操作;
  • Table:系統資源少,但並發極低(全表阻塞);
  • Database:最高層,僅還原/緊急修復用。

鎖升級

單一事務鎖數量達閾值 (約 5000) → 自動將 Row/Page 升級為 Table 鎖,降低鎖管理開銷。閾值與觸發由引擎自動決定,不可人工設定。

四大核心鎖模式

模式縮寫用途相容性
共享鎖S唯讀操作 (SELECT)與 S、U 相容;與 X、Sch-M 衝突
更新鎖U修改前預備 (尋找目標行)與 S 相容;與 U、X、Sch-M 衝突 → 防死鎖關鍵
排他鎖X修改操作 (INSERT/UPDATE/DELETE)與所有鎖衝突
結構鎖Sch-S / Sch-MSch-S:編譯查詢;Sch-M:DDL (ALTER/CREATE/DROP)Sch-S 與所有鎖相容 (除 Sch-M);Sch-M 與所有衝突

鎖模式與隔離等級對應

  • Read Uncommitted:不取 S 鎖 (或立即釋放) → 允許髒讀;
  • Read Committed:讀取瞬間取 S 鎖,讀完即釋放 → 禁止髒讀,允許不可重複讀/幻讀;
  • Repeatable Read:讀取取 S 鎖持有到事務結束 → 禁止不可重複讀,允許幻讀;
  • Serializable:範圍鎖 (Range S-S/Range X-X) 鎖住鍵值區間 → 禁止幻讀,實質序列化。

結構鎖細節

  • Sch-S:編譯查詢計劃時取得,不阻塞任何資料操作(含 X 鎖),只阻塞 DDL;
  • Sch-M:執行 DDL 時取得,阻塞所有其他操作。
copyright © 萬盛學電腦網 all rights reserved