sqlserver 查看鎖表，解鎖

查看被鎖表：

代碼如下 復制代碼?
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName?
from sys.dm_tran_locks where resource_type='OBJECT'

spid 鎖表進程?
tableName 被鎖表名

[@more@]

解鎖：

創建一個臨時Table

代碼如下 復制代碼?
CREATE TABLE #HarveyLock
(
SPID INT,
DBID INT,
OBJID INT,
INDID INT,
TYPE VARCHAR(100),
RESOURCE VARCHAR(100),
MODE VARCHAR(100),
STATUS VARCHAR(100)
)

將Lock信息存入該Table

INSERT INTO #HarveyLock EXEC SP_LOCK

3.在Table中下條件查詢得到的LOCK
SELECT * FROM #HarveyLock

4.KILL 相關LOCK
KILL @SPID

例

代碼如下 復制代碼?
declare @spid int?
Set @spid = 57 --鎖表進程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)