[SQL] 解決 DB Lock 的問題

 如題，近期因為寫 Store Procedure 產生了一個錯誤

/*EXECUTE 之後的交易計數顯示遺漏了 COMMIT 或 ROLLBACK TRANSACTION 陳述式。前次計數 = 0，目前的計數 = 1。*/

後續就發現特定 Table 無法搜尋，直覺就是應該被 Lock 住了，但是沒有特別處理過這問題，這次特別紀錄下。

以下開始 DB LOCK 的說明及解釋:

    1.    模擬 Db Lock，只要有開啟 Transactoin 但是沒有 Commit 或 Rollback 就會產生 Dead Lock。
            以下使用 北風資料庫為範例:

BEGIN TRAN UPDATE dbo.Employees SET Country = 'JAPAN'

    2.     此時 dbo.Employees 已經被 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 '' 

    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 

    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，一般的長時間查詢就會顯示這種狀態。

    4.    解除 Dead Lock，找到 rm = X 的 spid，並且執行下列 SQL 即可(若是KILL 發生錯誤，嘗試重新新增查詢，再執行一次):

KILL 54

參考資料:

1.     [SQL Server]檢查DB Lock狀態

2.    sp_lock (Transact-SQL) - SQL Server | Microsoft Learn

3.    sys.dm_tran_locks (Transact-SQL) - SQL Server | Microsoft Learn