無鎖 → 資料不一致(髒讀、不可重複讀、幻讀)。
過度鎖 → 併發度低、效能差。
鎖管理器 = 交警,平衡「併發」與「資料安全」。
| 維度 | 類型 |
|---|---|
| 模式 | 共享鎖 (S)、更新鎖 (U)、排他鎖 (X) |
| 結構鎖 (Sch-S, Sch-M) | |
| 意圖鎖 (IS, IU, IX) | |
| 轉換鎖 (SIX, SIU, UIX) | |
| 大量更新鎖 (BU) | |
| 粒度 | 資料庫、檔案、表、堆、索引、頁、鍵、區、列 |
| 應用程式鎖、中繼資料鎖 |
將細粒度鎖(列/頁)轉換為粗粒度鎖(表/堆),以減少鎖管理開銷(每鎖 ~96 bytes 記憶體 + CPU 維護成本)。
-- SQL 2008+ 表級設定
ALTER TABLE dbo.test SET (LOCK_ESCALATION = AUTO | TABLE | DISABLE);
-- 查詢提示強制粗粒度鎖(謹慎使用)
SELECT * FROM dbo.test WITH (TABLOCKX);
SELECT * FROM dbo.test WITH (PAGLOCK);
建議:預設 AUTO 即可,除非極度確定效能獲益大於併發損失。
CREATE TABLE test (ID INT IDENTITY PRIMARY KEY, Name VARCHAR(50) NOT NULL DEFAULT '', CreatedTime DATETIME NOT NULL DEFAULT GETDATE());S (DB)、IX (頁/表)、X (列)。UPDATE test SET Name='name' WHERE Name='kk';X 鎖大量持有。X 鎖 + Sch-S (中繼資料)。Sch-S,保留表級 X 至交易結束。sys.dm_tran_locks:即時鎖快照。sys.dm_os_waiting_tasks:阻塞鏈分析。| 隔離等級 | 悲觀鎖行為 | 樂觀鎖 (RCSI/SI) |
|---|---|---|
| Read Uncommitted | 不取 S 鎖,髒讀 | 版本儲存區 |
| Read Committed | 讀取即釋放 S 鎖 | |
| Repeatable Read | 持有 S 鎖至交易結束 | |
| Serializable | 範圍鎖 (Key-Range) 防幻讀 |
深入理解鎖機制,是寫出高併發、高正確性 SQL 的關鍵基石。