[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的鎖定狀態。
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 = IX 表示 wait lock ，這種是因為前一個Request已經dead lock，而被pending的狀態。
rm = S or IS 表示 shared lock，一般的長時間查詢就會顯示這種狀態。
找出rm = X的其中一個spid(56)，再下指令
KILL 56
就可以把dead lock狀態解除了，解除後再查詢一次
就可以看到dead lock都清除囉。
參考資料：