mssql 锁的操作

-- 查询被锁的表
select  request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName from   sys.dm_tran_locks 
where resource_type='OBJECT'

if OBJECT_ID(N'tempdb..#locktable',N'U') is not null DROP TABLE #locktable
select IDENTITY(INT, 1,1) AS id1,  request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName  
INTO #locktable 
from   sys.dm_tran_locks where resource_type='OBJECT'
SELECT * FROM #locktable
DELETE FROM #locktable WHERE  ISNULL(tableName,'')=''

-- 解锁表
declare @sql varchar(8000)
declare @spid INT
 DECLARE @i INT,@maxi INT
 SELECT @i=1, @sql='', @maxi=MAX(id1) FROM #locktable 
 WHILE @i<= @maxi
 BEGIN
    SELECT  @spid =spid FROM #locktable WHERE id1=@i      --锁表进程
    set @sql=@sql+ '
            kill '+cast(@spid as varchar);
            
    SET @i=@i+1        
END
PRINT(@sql)
exec(@sql)