萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 鎖表、解鎖與查看鎖定表的完整指南

SQL Server 鎖表、解鎖與查看鎖定表的完整指南

在 SQL Server 中,鎖定資料表是控制並發存取的常見手段。本文透過三個實例,詳細說明如何鎖定資料表、查詢被鎖定的資料表,以及如何解除鎖定(Kill 阻塞進程)。

一、鎖定資料表的兩種常見模式

1. HOLDLOCK(共用鎖,允許讀取、禁止寫入)

SELECT * FROM YourTable WITH (HOLDLOCK);

特性:其他交易仍可讀取該資料表,但無法執行 UPDATE、DELETE 等寫入操作。適用於需確保讀取一致性、但不想完全封鎖讀取的場景。

2. TABLOCKX(獨佔鎖,禁止所有存取)

SELECT * FROM YourTable WITH (TABLOCKX);

特性:其他交易無法讀取、更新或刪除該資料表。屬於最高層級的獨佔鎖定,謹慎使用。

> SELECT 語句中的「加鎖選項」功能說明
> SQL Server 提供完善的鎖機制支援並發與高效能。除預設隔離等級外,可在 SELECT 中使用表提示實現預期鎖定行為。常用提示包括 HOLDLOCK(相當於 SERIALIZABLE)、TABLOCKX、NOLOCK(讀取未認可資料)等。

二、查詢被鎖定的資料表

利用 DMV sys.dm_tran_locks 即可快速定位:

SELECT
  request_session_id AS spid,
  OBJECT_NAME(resource_associated_entity_id) AS tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';
  • spid:持有鎖定的進程 ID。
  • tableName:被鎖定的資料表名稱。

三、解除鎖定(Kill 阻塞進程)

若需強制釋放鎖定,可殺掉對應的 SPID。完整步驟如下:

  1. 建立暫存表儲存 sp_lock 輸出:
CREATE TABLE #HarveyLock (
  SPID INT, DBID INT, OBJID INT, INDID INT,
  TYPE VARCHAR(100), RESOURCE VARCHAR(100),
  MODE VARCHAR(100), STATUS VARCHAR(100)
);
  1. 將鎖定資訊寫入暫存表:
INSERT INTO #HarveyLock EXEC sp_lock;
  1. 查詢目標鎖定:
SELECT * FROM #HarveyLock WHERE ...; -- 依條件篩選
  1. Kill 對應 SPID:
DECLARE @spid INT = 57; -- 目標進程 ID
DECLARE @sql VARCHAR(1000) = 'KILL ' + CAST(@spid AS VARCHAR);
EXEC(@sql);
> ⚠️ 注意:KILL 會回滾該交易的所有未認可工作,可能導致資料不一致或應用程式錯誤。請確認業務影響後再執行,並優先嘗試正常提交/回滾交易。

小結

掌握 HOLDLOCK/TABLOCKX 的差異、善用 sys.dm_tran_locks 定位阻塞、謹慎使用 KILL 解除死結,是 DBA 與開發人員處理並發問題的基本功。

copyright © 萬盛學電腦網 all rights reserved