查看数据库的表被谁锁住了，以及如何解锁

被谁锁住了

SELECT request_session_id spid ,
OBJECT_NAME(resource_associated_entity_id)  tbm_cih_CustomTableItemHistory_Product
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

tbm_cih_CustomTableItemHistory_Product  是数据表的表名

 

如何解锁

DECLARE @spid INT;
SET @spid = 69; --锁表进程
DECLARE @sql VARCHAR(1000);
SET @sql = 'kill ' + CAST(@spid AS VARCHAR);
EXEC ( @sql );

http://www.cnblogs.com/shy1766IT/p/6225694.html

 

扩展

从sys.dm_tran_locks筛选的时候，主要是匹配resource_type

Represents the resource type.

The value can be one of the following: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT.

SELECT * FROM sys.databases
WHERE name='CL_LS_PRG_ECRP'

SELECT *
FROM   sys.dm_tran_locks
WHERE  resource_database_id IN (   SELECT database_id
                                   FROM   sys.databases
                                   WHERE  name = 'CL_LS_PRG_ECRP'
                               );

 

 

作者:Chuck Lu    GitHub