SQL Server 检查和处理死锁问题

SELECT spid,
blocked,
DB_NAME(sp.dbid) AS DBName,
program_name, waitresource,
lastwaittype, sp.loginame,
sp.hostname, a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE a.[Text]like '%QueryNewProductDeitalTop150SKUTrQty%' or spid = 69 or spid = 78
ORDER BY
spid desc,
blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];

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

 

declare @spid int
Set @spid = 51 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)

 

 

 

同时查看活动监视器 中的堵塞者