深入解析 SQL Server 事務隔離等級與鎖機制完整指南

鎖機制與事務隔離等級是 SQL Server 並發控制的核心。本文透過實測範例，詳細解析 NOLOCK、READPAST、ROWLOCK、HOLDLOCK、TABLOCK、TABLOCKX、UPDLOCK 等資料表提示的行為差異，以及四大標準隔離等級與快照隔離的實際運作模式。

一、資料表提示實測：NOLOCK vs READPAST

場景：未提交的插入操作

-- 會話 1：開啟交易插入資料但不提交
BEGIN TRAN t
INSERT INTO Customer SELECT 'a','a'
-- 此處未 COMMIT，資料處於未提交狀態

會話 2 執行不同查詢的結果：

場景：另一筆已提交的插入

-- 會話 3：插入並提交
BEGIN TRAN t
INSERT INTO Customer SELECT 'b','b'
COMMIT TRAN t

此時即使會話 1 回滾，'b' 仍會保留在資料庫中。

二、各類鎖提示詳細行為對照表

NOLOCK（等同 READ UNCOMMITTED）

不加任何鎖，允許增刪查改不阻塞：

BEGIN TRAN ttt
SELECT * FROM Customer WITH (NOLOCK)
WAITFOR DELAY '00:00:20'
COMMIT TRAN ttt

-- 其他會話可自由操作：
INSERT INTO Customer SELECT 'a','b'  -- 不等待
DELETE Customer WHERE ID=1           -- 不等待
SELECT * FROM Customer               -- 不等待
UPDATE Customer SET Title='aa' WHERE ID=1  -- 不等待

ROWLOCK（需搭配 REPEATABLE READ 隔離等級）

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN ttt
SELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17
WAITFOR DELAY '00:00:20'
COMMIT TRAN ttt

-- 其他會話影響：
INSERT INTO Customer SELECT 'a','b'     -- 不等待
DELETE Customer WHERE ID=17             -- 等待（鎖衝突）
DELETE Customer WHERE ID<>17           -- 不等待
SELECT * FROM Customer                  -- 不等待
UPDATE Customer SET Title='aa' WHERE ID=17  -- 等待
UPDATE Customer SET Title='aa' WHERE ID<>17 -- 不等待

HOLDLOCK / TABLOCK / TABLOCKX

UPDLOCK（更新鎖）

僅對更新操作加鎖，讀取與插入不受影響：

-- 會話 A
BEGIN TRAN ttt
SELECT * FROM Customer WITH (UPDLOCK)
WAITFOR DELAY '00:00:10'
COMMIT TRAN ttt

-- 其他會話：
UPDATE Customer SET Title='aa' WHERE ID=1  -- 等待 10 秒
SELECT * FROM Customer                     -- 不等待
INSERT INTO Customer SELECT 'a','b'        -- 不等待

注意：使用這些提示會覆蓋 SET TRANSACTION ISOLATION LEVEL 設定的隔離等級。

三、五大事務隔離等級深度解析

1. READ UNCOMMITTED（髒讀）

允許讀取未提交資料。相當於 NOLOCK。
風險：若來源交易回滾，讀到的資料即為髒資料。

2. READ COMMITTED（提交讀，預設等級）

只能讀取已提交資料，避免髒讀。
無法防止：不可重複讀、幻影讀。

3. REPEATABLE READ（可重複讀）

阻止其他交易修改正在被讀取的資料，直到讀取交易結束。
防止：髒讀、不可重複讀。
無法防止：幻影讀（新增符合條件的新列）。

4. SERIALIZABLE（序列化/可序列化）

使用範圍鎖防止其他交易插入/刪除符合條件的列。
防止：髒讀、不可重複讀、幻影讀。
代價：並發度最低，鎖衝突最多。

5. SNAPSHOT（快照隔離）

為每個交易提供資料快照，讀取不阻塞寫入，寫入不阻塞讀取（MVCC 機制）。
防止：髒讀、不可重複讀、幻影讀。
需求：資料庫啟用 ALLOW_SNAPSHOT_ISOLATION 與 READ_COMMITTED_SNAPSHOT。

四、三種核心鎖類型

• 共享鎖（S 鎖）：允許並發讀取（SELECT），阻塞修改。讀取完成即釋放，除非隔離等級為 REPEATABLE READ 或更高，或使用鎖提示保留。
• 更新鎖（U 鎖）：防止常見死鎖。讀取時取得 S 鎖，修改前轉為 X 鎖。同一資源同一時間只能有一個交易持有 U 鎖，避免兩交易互相等待轉換造成死鎖。
• 排他鎖（X 鎖）：防止任何並發存取。修改語句（INSERT/UPDATE/DELETE）會先取得 S 鎖讀取，再轉 X 鎖修改。僅 NOLOCK 或 READ UNCOMMITTED 可讀取 X 鎖資源。

資料修改語句通常同時請求 S 鎖（讀取聯結表）與 X 鎖（修改目標表）。