萬盛學電腦網

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

SQL Server 事務核心觀念與實務:ACID 屬性、隔離等級、鎖機制、死鎖預防全解析

事務定義:隱式與顯式

每條 DML(SELECT/INSERT/UPDATE/DELETE)皆為隱式事務,自動提交或回滾。多條語句需原子性時,用 BEGIN TRAN 開啟顯式事務。

ACID 四大屬性

屬性英文核心含義
原子性Atomic全做或全不做,錯誤自動回滾到事務起始狀態
一致性Consistent事務前後資料庫完整性約束(PK/FK/Check/Trigger)均滿足
隔離性Isolated並發事務互不干擾,看不到彼此未提交的中間狀態
持久性Durable提交後的變更永久保存,掉電/崩潰不丟失

事務日志與檢查點機制

  • 所有修改先寫入記憶體日志緩衝區,再強制寫入磁碟(WAL,Write-Ahead Logging);
  • 檢查點定期將 Dirty Page 刷入資料檔,並在日志標記檢查點 LSN,縮短恢復時間;
  • 啟動時按日志 REDO(重做已提交)+ UNDO(回滾未提交)保證 ACID。

常用控制語句

BEGIN TRANSACTION;      -- 開始
COMMIT TRANSACTION;     -- 提交
ROLLBACK TRANSACTION;   -- 回滾
SAVE TRANSACTION SavePt;-- 存檔點(部分回滾)
-- 範例
BEGIN TRAN;
  UPDATE A SET ...;
  SAVE TRANSACTION Pt1;
  UPDATE B SET ...; -- 若失敗
  ROLLBACK TRANSACTION Pt1; -- 僅回滾 B 的更新
COMMIT TRAN;

五大隔離等級(由低到高)

等級別名髒讀不可重複讀幻讀鎖實現適用場景
Read Uncommitted未授權讀取✅ 允許✅ 允許✅ 允許排他寫鎖報表/近似查詢,極高並發
Read Committed授權讀取❌ 禁止✅ 允許✅ 允許瞬間共享讀鎖 + 排他寫鎖預設,大多數 OLTP
Repeatable Read可重複讀取❌ 禁止❌ 禁止✅ 允許共享讀鎖(持有到結束)+ 排他寫鎖需穩定讀取集合的報表
Snapshot快照隔離❌ 禁止❌ 禁止❌ 禁止行版本控制(tempdb),讀不阻寫,寫不阻讀高並發讀寫混合,需啟用資料庫選項
Serializable序列化❌ 禁止❌ 禁止❌ 禁止範圍鎖 + 排他鎖,實質單線程化極嚴格一致性需求(如財務結帳)

設定方式:SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 或連接字串/資料庫選項啟用 Snapshot。

死鎖成因與預防六原則

死鎖 = 循環等待(T1 鎖 A 等 B,T2 鎖 B 等 A)。

  1. 統一存取順序:所有事務按相同順序鎖表/行(如先 A 後 B)。
  2. 避免事務中夾帶使用者互動:思考時間長 = 鎖持有久 = 衝突率升。
  3. 事務極小化:單批次提交,減少網路來回與鎖持有時間。
  4. 降低隔離等級:能用 Read Committed/Snapshot 就不用 Repeatable Read/Serializable。
  5. 善用索引:精準定位行 → 鎖範圍小 → 衝突少。
  6. 綁定連接:同一應用多連線共享鎖(sp_getbindtoken/sp_bindsession),避免自家打架。

實務建議

  • 高頻表加叢集索引;
  • 避免大範圍 UPDATE/DELETE,分批處理;
  • WHERE 條件走索引,減少掃描鎖升級;
  • 只讀報表加 WITH(NOLOCK) 或啟用 RCSI(Read Committed Snapshot Isolation)。
copyright © 萬盛學電腦網 all rights reserved