[MSSQL] sql server查看锁表及解锁方法(示例详解)

sql server查看锁表以及解锁方法

1.查看sqlserver被锁的表：

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type='OBJECT'

2.解锁：@spid为锁表进程

declare @spid int

Set @spid = 141

declare @sql varchar(1000)set @sql='kill '+cast(@spid as varchar)

exec(@sql)

补充：SQL Server 查看锁表情况

SQL Server 查看锁表情况

查看锁表数据
SELECT
	request_session_id spid, -- 请求会话ID
	OBJECT_NAME( resource_associated_entity_id ) tableName  -- 表名
FROM
	sys.dm_tran_locks 
WHERE
	resource_type = 'OBJECT'

-- 	杀掉锁表进程
	DECLARE
	@spid INT 
	SET @spid = 123 --锁表进程
  DECLARE
	@SQL VARCHAR ( 1000 ) 
	SET @SQL = 'kill ' + CAST ( @spid AS VARCHAR ) EXEC ( @SQL )