鎖是資料庫在多使用者環境下保證資料完整性與一致性的核心機制。SQL Server 採用動態鎖管理，系統自動分析查詢並在效能與並發間取得平衡。但對於高並發、嚴格一致性需求的系統，深入理解鎖機制並掌握手動控制技巧至關重要。
| 問題 | 描述 | 範例 | 鎖解決方式 |
|---|---|---|---|
| 遺失更新 | 兩筆交易讀取同一資料並基於舊值更新，後寫入者覆蓋先前更新 | 兩編輯者同時修改同一文件，後存檔者覆蓋先前變更 | 排他鎖 (X-lock) 保護寫入期間資料 |
| 髒讀 | 交易讀取未提交的修改資料，若修改者回滾則讀到不存在的資料 | 編輯者修改文件中，他人複製草稿分發；編輯者取消修改，分發版本含無效內容 | 共享鎖 (S-lock) 阻塞讀取未提交資料 |
| 不可重複讀 | 同一交易多次讀取同一資料，結果不一致 (被其他交易修改) | 報表查詢同一帳戶兩次，中間被轉帳交易修改餘額 | 交易隔離等級 REPEATABLE READ |
| 幻覺讀 | 同一交易多次執行相同範圍查詢，結果筆數不同 (被其他交易插入/刪除) | 統計部門員工數，中間有新進/離職人員 | 範圍鎖、SERIALIZABLE 隔離等級 |
| 既有鎖 \ 要求鎖 | S | U | X | IS | IX |
|---|---|---|---|---|---|
| S | ✓ | ✓ | ✗ | ✓ | ✗ |
| U | ✓ | ✗ | ✗ | ✓ | ✗ |
| X | ✗ | ✗ | ✗ | ✗ | ✗ |
| IS | ✓ | ✓ | ✗ | ✓ | ✓ |
| IX | ✗ | ✗ | ✗ | ✓ | ✓ |
SQL Server 監控鎖數量，當單一陳述式持有鎖超過記憶體閾值 (約 5000 個) 或佔用鎖記憶體超過 40%，嘗試將細粒度鎖 (資料列/頁面) 升級為粗粒度鎖 (資料表/分區)。
控制方式：
ALTER TABLE ... SET (LOCK_ESCALATION = TABLE | AUTO | DISABLE) (SQL 2008+)| 隔離等級 | 髒讀 | 不可重複讀 | 幻覺讀 | 鎖持有時間 | 並發度 |
|---|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 無 S 鎖 | 最高 |
| READ COMMITTED (預設) | 防止 | 可能 | 可能 | 讀取瞬間 | 高 |
| REPEATABLE READ | 防止 | 防止 | 可能 | 交易結束 | 中 |
| SNAPSHOT | 防止 | 防止 | 防止 | 版本存儲 | 高 (讀不阻寫) |
| SERIALIZABLE | 防止 | 防止 | 防止 | 交易結束 + 範圍鎖 | 最低 |
-- 強制使用索引 + 排他鎖
SELECT * FROM Orders WITH (INDEX(IX_OrderDate), UPDLOCK, HOLDLOCK)
WHERE OrderDate > '2024-01-01'
-- 僅讀取已提交資料，不加共享鎖 (髒讀)
SELECT * FROM Products WITH (NOLOCK) -- 等同 READ UNCOMMITTED
-- 分頁查詢最佳化
SELECT * FROM Sales WITH (READPAST) -- 跳過被鎖定的資料列
WHERE Region = 'North'
ORDER BY SaleDate DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY-- 取得應用程式層級互斥鎖
DECLARE @Result INT
EXEC @Result = sp_getapplock
@Resource = 'InvoiceGeneration_Batch001',
@LockMode = 'Exclusive',
@LockOwner = 'Transaction',
@LockTimeout = 30000 -- 30 秒逾時
IF @Result >= 0
BEGIN
-- 執行關鍵業務邏輯
EXEC sp_releaseapplock @Resource = 'InvoiceGeneration_Batch001', @LockOwner = 'Transaction'
ENDsys.dm_tran_locks：即時鎖狀態sys.dm_os_wait_stats：等待類型分析 (LCK_M_*)DBCC OPENTRAN：最長執行交易lock_deadlock, lock_escalationNOLOCK 或 SNAPSHOT 隔離
