SQL Server 鎖機制深度解析：類型、粒度、模式、死鎖與自訂控制全景圖

鎖是 SQL Server 實現悲觀並發控制的核心機制。本文系統梳理鎖的七大模式、六大粒度、持有時間規則、自訂控制手段、死鎖處理與實戰範例，構建完整鎖知識體系。

一、為什麼需要鎖：四大並發異常

• 遺失更新：兩用戶讀同一資料並修改，一修改覆蓋另一
• 髒讀：讀到未提交的修改，後來回滾導致不一致
• 不可重複讀：同一查詢前後兩次結果不一致（他人已修改提交）
• 幻讀：同一範圍查詢前後筆數不同（他人插入/刪除）

二、七大鎖模式（資源鎖）

意圖鎖細分

• IS (Intent Shared)：意圖在下層資源放 S 鎖（讀取部分）
• IX (Intent Exclusive)：意圖在下層放 X 鎖（修改部分），IS 的超集
• SIX (Shared with Intent Exclusive)：讀取全部 + 修改部分，表級 SIX + 頁級 IX + 列級 X，允許並發 IS

三、六大鎖粒度：粒度越細並發越高，開銷越大

四、鎖持有時間：隔離等級決定讀鎖壽命

• READ COMMITTED (預設)：讀取頁期間持有 S 鎖，掃描到下一頁釋放
• HOLDLOCK / REPEATABLE READ / SERIALIZABLE：交易結束才釋放 S 鎖
• 游標捲動鎖：下一次 FETCH 或關閉游標釋放；加 HOLDLOCK 則交易結束釋放
• X 鎖：交易結束才釋放

五、自訂鎖控制五大手段

1. 死鎖優先權： SET DEADLOCK_PRIORITY LOW/NORMAL/HIGH/@var，決定犧牲者
2. 鎖逾時： SET LOCK_TIMEOUT 1800 (毫秒)，逾時自動取消並回傳錯誤 1222；@@LOCK_TIMEOUT 查看當前設定
3. 交易隔離等級： SET TRANSACTION ISOLATION LEVEL ...
4. 表級鎖定提示： WITH (HOLDLOCK, ROWLOCK, TABLOCKX, UPDLOCK, NOLOCK, READPAST...)
5. 索引鎖粒度配置： sp_indexoption 禁用頁/列鎖強制表鎖

六、查看鎖資訊

• EXEC sp_lock 經典報表
• 查詢分析器 Ctrl+2 快捷鍵
• DMV：sys.dm_tran_locks、sys.dm_os_waiting_tasks 現代替代

七、死鎖避免六大原則

1. 縮短交易邏輯，盡早提交/回滾
2. 設定合理死鎖逾時（3-10 分鐘），避免進程懸掛
3. 優化存取順序，統一多表操作順序
4. 上線前嚴格測試所有腳本與預存程序
5. 所有預存程序必須有 @error 錯誤處理
6. 不輕易修改預設隔離等級，不建議強制加鎖提示

八、實戰範例：三大經典場景演示

1. 排他鎖阻塞讀取

-- 連線 1
BEGIN TRAN
UPDATE table1 SET A='aa' WHERE B='b2'
WAITFOR DELAY '00:00:30'
COMMIT TRAN

-- 連線 2 (同時執行)
BEGIN TRAN
SELECT * FROM table1 WHERE B='b2'
COMMIT TRAN
-- SELECT 必須等待 30 秒

2. 共享鎖允許並發讀，阻塞寫入

-- 連線 1
BEGIN TRAN
SELECT * FROM table1 WITH (HOLDLOCK) WHERE B='b2'
WAITFOR DELAY '00:00:30'
COMMIT TRAN

-- 連線 2
BEGIN TRAN
SELECT A,C FROM table1 WHERE B='b2'  -- 可立即執行
UPDATE table1 SET A='aa' WHERE B='b2' -- 必須等待 30 秒
COMMIT TRAN

3. 經典死鎖：資源請求順序相反

-- 連線 1: 先鎖 table1 再鎖 table2
-- 連線 2: 先鎖 table2 再鎖 table1
-- 系統偵測死鎖，選擇犧牲者中止

九、補充：應用程式鎖

• 客戶端代碼生成，非引擎內部鎖
• sp_getapplock 取得 / sp_releaseapplock 釋放
• 適用：跨表/跨資料庫邏輯互斥、單例執行控制

十、表級鎖提示快速對照 (SQL 2000/2005/2008 通用)

深度掌握鎖機制，是寫出高並發、低阻塞、無死鎖 SQL 程式碼的基石。