【SQLServer】sqlserver死锁检测

--1.通过以下dmvs方式

SELECT
	dowt.session_id
	,dowt.wait_duration_ms
	,dowt.wait_type
	,dowt.blocking_session_id
	,dese.host_name as HostName
	,der.command
	,der.percent_complete
	,der.cpu_time
	,der.total_elapsed_time
	,der.reads
	,der.writes
	,der.logical_reads
	,der.row_count
	,dest.text AS QueryText
	,dest.dbid AS DatabaseID
	,deqp.query_plan
	,der.plan_handle
FROM sys.dm_os_waiting_tasks as dowt
INNER JOIN sys.dm_exec_sessions as dese
	ON dowt.session_id = dese.session_id
INNER JOIN sys.dm_exec_requests as der
	ON dese.session_id = der.session_id
CROSS APPLY sys.dm_exec_sql_text(der.plan_handle) as dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) as deqp
WHERE dowt.session_id > 50

　　

--2.通过sysprocesses查看死锁
--查询死锁脚本

USE master 
GO
DECLARE @spid INT,@bl INT 
DECLARE s_cur CURSOR FOR 
SELECT 0,blocked 
FROM
	( SELECT * FROM sysprocesses WHERE blocked > 0 ) a 
WHERE
	NOT EXISTS ( SELECT * FROM ( SELECT * FROM sysprocesses WHERE blocked > 0 ) b WHERE a.blocked= spid ) 
UNION
SELECT
	spid,blocked 
FROM
	sysprocesses 
WHERE
	blocked > 0 
OPEN s_cur FETCH NEXT 
FROM
	s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0 
BEGIN
		IF @spid = 0 
			SELECT ' 引起数据库死锁的是: ' + CAST ( @bl AS VARCHAR ( 10 ) ) + ' 进程号, 其执行的SQL 语法如下' 
		ELSE 
			SELECT ' 进程号SPID ：' + CAST ( @spid AS VARCHAR ( 10 ) ) + ' 被' + ' 进程号SPID ：' + CAST ( @bl AS VARCHAR ( 10 ) ) + ' 阻塞, 其当前进程执行的SQL 语法如下' 
DBCC INPUTBUFFER ( @bl ) FETCH NEXT 
FROM
s_cur INTO @spid,@bl 
END CLOSE s_cur 
DEALLOCATE s_cur 
EXEC sp_who2

运行sp_who2,看blk by这一列， 如果这栏不为空，有一个数字，这就是造成阻塞（blocking）的线程id. 然后运行DBCC Inputbuffer(线程id), 就可以看见这个线程在干什么

--杀死锁脚本：

SELECT
	* 
FROM
	master.dbo.sysprocesses 
WHERE
	spid > 50 
	AND waittype = 0x0000 
	AND waittime = 0 
	AND status = 'sleeping' 
	AND last_batch < dateadd( MINUTE, - 10, getdate( ) ) 
	AND login_time < dateadd( MINUTE, - 10, getdate( ) ) 
DECLARE
	hcforeach CURSOR GLOBAL FOR SELECT
	'kill ' + rtrim( spid ) 
FROM
	master.dbo.sysprocesses 
WHERE
	spid > 50 
	AND waittype = 0x0000 
	AND waittime = 0 
	AND status = 'sleeping' 
	AND last_batch < dateadd( MINUTE, - 60, getdate( ) ) 
AND login_time < dateadd( MINUTE, - 60, getdate( ) ) EXEC sp_msforeach_worker '?'

--3.以下是备用方案：
--查询死锁

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

--杀死死锁

KILL 155

--显示死锁相关信息

exec sp_who2 137

参考原文：https://www.cnblogs.com/guangang/articles/9242028.html