USE master

go

/*

======= 死锁经常与正常阻塞混淆 ==========

死锁是无期限的等待和阻塞，所以数据库引擎会提前判断而自动进行处理。

而阻塞是当一个事务锁定了另一个事务需要的资源，第二个事务必须等待锁被释放。

默认情况下，SQL Server 事务不会超时(除非设置了 LOCK_TIMEOUT)。

所以表现为第二个事务被长久阻塞，而不是被死锁。故需要人为发现和处理。

======== 以下显示被长久阻塞的线程列表 ==========================

*/

SELECT

 DB_NAME(a.[dbid]) AS [所在的数据库],

 a.spid AS [等待资源的线程(被阻塞)],

 a.blocked AS 产生阻塞的线程,

 a.waittime AS 等待时间,

 a.waitresource AS 等待资源,

 a.[status] AS 线程状态,

 (SELECT [name] FROM sys.all_objects WHERE [object_id] = rt.[objectid]) AS [OBJ],

 rt.[text] AS 等待资源的语句

FROM sys.sysprocesses AS a

CROSS APPLY sys.dm_exec_sql_text(a.[sql_handle]) AS rt

WHERE a.blocked > 0  -- 被其他线程阻塞了

ORDER BY a.blocked,a.spid;

-- blocked:占用锁的进程(造成当前进程等待他释放锁)

/* ============ 锁定产生阻塞的源头 =======================

有时候产生阻塞的线程可能也是被其它线程阻塞了，所以造成了连锁反应。

这种情况下要找出源头，产生阻塞的源头线程。

以下两个条件同时成立时，即是源头线程，应该查明原因，紧急情况下用kill结束掉：

1.线程阻塞了其它线程。

2.该线程没有被任何其它线程阻塞。

*/

DECLARE @sp_id sysname;

SELECT @sp_id = spid FROM sys.sysprocesses

WHERE spid IN(

 SELECT DISTINCT blocked

 FROM sys.sysprocesses

 WHERE blocked > 0

) AND blocked = 0;

SELECT

 DB_NAME(a.[dbid]) AS 所在数据库,

 @sp_id AS [产生阻塞原因的spid],

 (SELECT [name] FROM sys.all_objects WHERE [object_id] = rt.[objectid]) AS [OBJ],

 rt.[text] AS [产生阻塞的相关语句]

FROM sys.sysprocesses a

CROSS APPLY sys.dm_exec_sql_text(a.[sql_handle]) AS rt

WHERE a.spid = @sp_id;

-- kill 78

/* ==============================================================

线程状态为suspended(挂起)，并不代表产生了阻塞,必须blocked>0

这说明本身线程所涉及到的语句比较复杂，需要优化，或者需要创建索引。

SELECT

 DB_NAME(a.[dbid]) AS 存在阻塞所在的数据库,

 a.spid AS 被阻塞的线程,

 a.blocked AS 产生阻塞的线程,

 a.waittime AS 等待时间,

 a.waitresource AS 等待资源,

 a.[status] AS 线程状态,

 (SELECT [name] FROM sys.all_objects WHERE [object_id] = rt.[objectid]) AS [OBJ],

 rt.[text] AS 被阻塞的语句

FROM sys.sysprocesses AS a

CROSS APPLY sys.dm_exec_sql_text(a.[sql_handle]) AS rt

WHERE a.[status] = 'suspended' -- 延缓；暂停

ORDER BY a.blocked,a.spid;

================================================================== */