【SQLServer】sqlserver死锁检测

--1.通过以下dmvs方式

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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查看死锁
--查询死锁脚本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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), 就可以看见这个线程在干什么

--杀死锁脚本：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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.以下是备用方案：
--查询死锁

?

1 2 3

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

--杀死死锁

?

1

KILL 155

 

--显示死锁相关信息

?

1	exec sp_who2 137

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