chi's coding life

假設我們發現某張表被Lock住了，可以透過下面的SQL指令去尋找被LOCK住的情形

SELECT
    OBJECT_NAME(P.object_id) AS TableName,
    Resource_type,
    request_session_id
FROM
    sys.dm_tran_locks L
JOIN
    sys.partitions P ON L.resource_associated_entity_id = p.hobt_id
WHERE   
    OBJECT_NAME(P.object_id) = 'tableName'

你會看到LOCK的 request_session 這時候你可以透過

Kill session_ID

來解決lock之情形 

20190119 更新

找到更好的SQL語句，可以看到執行的SQL哪裡lock了

use tempdb
--找鎖定的物件、類型以及被誰Blocking
SELECT
        tl.request_session_id AS [我的SID]
        ,tl.resource_type AS [資源類型]
        ,DB_NAME(tl.resource_database_id) AS [資料庫名稱]
        ,(CASE resource_type
                WHEN 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
                ELSE (SELECT
                                        OBJECT_NAME(object_id)
                                FROM sys.partitions
                                WHERE hobt_id = resource_associated_entity_id)
        END) AS [物件名稱]
        ,tl.resource_description AS [資源說明]
        ,tl.request_mode AS [鎖定類型]
        ,tl.request_status AS [狀態]
        ,wt.blocking_session_id AS [被阻塞SID]
        ,c.connect_time AS [連接時間]
        ,txt.text AS [最近執行語法]
	   ,lock_txt.text AS [被阻塞的執行語法]
FROM sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_os_waiting_tasks AS wt
        ON tl.lock_owner_address = wt.resource_address
LEFT JOIN sys.dm_exec_connections AS c
        ON tl.request_session_id = c.session_id
LEFT JOIN sys.dm_exec_connections AS d
        ON wt.blocking_session_id = d.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) txt 
OUTER APPLY sys.dm_exec_sql_text(d.most_recent_sql_handle) lock_txt 
WHERE resource_type != 'DATABASE'
  AND tl.request_session_id > 50  and tl.request_status = 'LOCK'
ORDER BY tl.request_session_id
GO

20190214更新

上方的我發現好像查不太出來是否有LOCK(待查原因)

用第一個方法確實可以看到LOCK的requestSession

---

資料來源:

https://stackoverflow.com/questions/37156834/how-to-unlock-table-in-sql-server-2012