您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

MS SQL 入門基礎：資料庫鎖機制完整教學 (概念、類型、隔離級別、觀察工具)

編輯：關於SqlServer

鎖是多使用者資料庫環境中協調並發存取、保證資料一致性的核心機制。本文系統介紹 SQL Server 的鎖概念、鎖對象、三大鎖類型、程式設計師視角的樂觀/悲觀鎖、四大隔離級別與觀察工具。

11.5.1 鎖的概念與可鎖定對象

鎖是對資源存取的限制。SQL Server 可鎖定以下層級對象 (由細到粗)：

資料列 - 數據頁中的單行
索引鍵 - 索引頁中的單行 (鍵值)
頁 - 8KB 儲存基本單位
區 - 8 個連續頁
表
資料庫

11.5.2 鎖的類別

(1) 系統視角：三大基礎鎖類型

鎖類型	相容性	觸發場景	釋放時機
獨佔鎖	與所有鎖不相容	INSERT/UPDATE/DELETE	交易結束 (COMMIT/ROLLBACK)
共享鎖	與 S、U 相容；與 X 不相容	SELECT 讀取	讀取完成即釋放 (除非 HOLDLOCK/隔離級較高)
更新鎖	與 S 相容；與 U、X 不相容	UPDATE/DELETE 尋找階段	確定修改升級為 X 鎖，持有至交易結束

(2) 程式設計師視角：樂觀鎖 vs 悲觀鎖

類型	核心思想	實現方式	優點	缺點
樂觀鎖	假設衝突稀少，提交時檢查	版本戳/時間戳/檢查和比對	無鎖開銷，高並發讀取效能佳	衝突時需重試，適用低衝突場景
悲觀鎖	假設衝突常見，先鎖後用	SELECT ... FOR UPDATE / UPDLOCK / 鎖提示	強一致性，適合高衝突寫入	並發度低，易死鎖，鎖開銷大

SQL Server 預設行為偏悲觀 (自動加鎖)，但可透過 `READ_COMMITTED_SNAPSHOT` 實現樂觀讀取。

11.5.3 隔離級別

隔離級別定義交易對其他交易修改的可見度，等同於鎖定行為的預設規則。

隔離級別	髒讀	不可重複讀	幻影讀	鎖行為特徵
READ UNCOMMITTED	允許	允許	允許	不發共享鎖 (相當 NOLOCK)
READ COMMITTED (預設)	禁止	允許	允許	發共享鎖，讀完即釋放
REPEATABLE READ	禁止	禁止	允許	共享鎖持有至交易結束
SERIALIZABLE	禁止	禁止	禁止	範圍鎖 (Key-Range)，最高隔離

設定語法


SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 或 READ UNCOMMITTED / REPEATABLE READ / SERIALIZABLE

也可在單一查詢用鎖提示覆寫：`SELECT * FROM T WITH (NOLOCK)` 等。

11.5.4 觀察鎖的工具

(1) 企業管理器 / SSMS 活動監視器

路徑：管理 → 目前活動 → 鎖/處理序 ID 或鎖/對象
右鍵進程 → 屬性 → 可刷新、終止進程 (KILL spid)

活動監視器鎖視圖鎖進程細節對話框

(2) 系統預存程序 sp_lock


EXEC sp_lock [spid];  -- spid 可從 master.dbo.sysprocesses 查詢，省略則顯示所有鎖

sp_lock 輸出範例

(3) 現代 DMV (推薦 SQL Server 2005+)


SELECT
    request_session_id AS SPID,
    resource_type,
    resource_description,
    request_mode,
    request_status,
    OBJECT_NAME(resource_associated_entity_id) AS ObjectName
FROM sys.dm_tran_locks;