[SQL Server]檢查DB Lock狀態

[SQL Server]檢查DB Lock及解決方式

執行以下這段SQL可以看到各個資料庫鎖定的狀態及各項資訊

SELECT request_session_id AS spid,
    resource_type AS rt,
    resource_databASe_id AS rdb,
    (CASE resource_type 
    WHEN 'OBJECT' then object_name(resource_ASsociated_entity_id) 
    WHEN 'DATABASE' then '<db_name>' 
    ELSE 
    (SELECT object_name(object_id) FROM sys.partitions 
        WHERE hobt_id = resource_ASsociated_entity_id) END) AS objname,
    resource_description AS rd,
    request_mode AS rm,
    request_status AS rs
FROM sys.dm_tran_locks 

我們以北風資料庫為例,模擬dead lock狀態,先開啟一個查詢,啟用一段TRANS,但不COMMIT或ROLLBACK

BEGIN TRAN
	UPDATE [dbo].[Customers] SET CompanyName = 'Alfreds Futterkiste test' 
    WHERE customerID = 'ALFKI'

再開另一個查詢,針對Customers下查詢動作,即可造成Dead Lock

SELECT * FROM [dbo].[Customers]

之後以第一段的SQL查詢,就會看到以下結果:

其中我們需要注意的欄位主要有3個:

spid:該次Request的編號。
objname:該次Request處理的物件,從圖中可以看到Customers。
rm/rs:該次Request的鎖定狀態。

rm顯示的內容很多,但是基本上只需要注意以下3種即可:

rm = X 表示 dead lock,一般就是我們需要處理的。
rm = IX 表示 wait lock ,這種是因為前一個Request已經dead lock,而被pending的狀態。
rm = S or IS 表示 shared lock,一般的長時間查詢就會顯示這種狀態。

找出rm = X的其中一個spid(56),再下指令

KILL 56

就可以把dead lock狀態解除了,解除後再查詢一次

就可以看到dead lock都清除囉。

參考資料:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql