杀掉那些死锁的进程
前些天，同事遇到一个死锁的问题，又想起三年前面试的时候问的一个with(nolock)相关的问题，一下子不知道怎么解锁，走过去看看，一下子不知道怎么写，忙活了一会，弄出个东东，核心主要是视图sys.SysProcesses，它源自于系统表sysprocesses，前者是微软推荐的用法，因为后者在未来SQL Server版本中可能会被隐藏。视图字段含义见这里。然后是kill 命令，它加上进程id就可以杀掉相应进程，它的简单用法如下：
use Test
go
-- 列出所有进程信息
EXEC sp_who2
go
-- 执行杀掉进程命令
KILL 29
go
1 use Test 2 go 3 -- 列出所有进程信息 4 EXEC sp_who2 5 go 6 7 -- 执行杀掉进程命令 8 KILL 29 9 go
准备工作：
CREATE TABLE TestDead
(
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(10) NOT NULL
)
GO
CREATE TABLE TestDead2
(
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(10) NOT NULL
)
GO
INSERT INTO TestDead(name)
VALUES ('name01'),
('name02'),
('name03')
GO
INSERT INTO TestDead2(name)
VALUES ('name01'),
('name02'),
('name03')
GO
BEGIN TRAN -- 打开一个更新事务，不提交, 锁住第一个表
UPDATE TestDead SET name = 'nameXX' WHERE id = 1
GO
BEGIN TRAN --开始一个事务，不提交，锁住第二个表
UPDATE TestDead2 SET name = 'nameXX' WHERE id = 1
GO
1 CREATE TABLE TestDead 2 ( 3 id INT IDENTITY(1,1) PRIMARY KEY, 4 name NVARCHAR(10) NOT NULL 5 ) 6 GO 7 8 CREATE TABLE TestDead2 9 ( 10 id INT IDENTITY(1,1) PRIMARY KEY, 11 name NVARCHAR(10) NOT NULL 12 ) 13 GO 14 15 INSERT INTO TestDead(name) 16 VALUES ('name01'), 17 ('name02'), 18 ('name03') 19 GO 20 21 INSERT INTO TestDead2(name) 22 VALUES ('name01'), 23 ('name02'), 24 ('name03') 25 GO 26 27 BEGIN TRAN -- 打开一个更新事务，不提交, 锁住第一个表 28 29 UPDATE TestDead SET name = 'nameXX' WHERE id = 1 30 31 GO 32 33 34 BEGIN TRAN --开始一个事务，不提交，锁住第二个表 35 36 UPDATE TestDead2 SET name = 'nameXX' WHERE id = 1 37 38 GO
创建了表之后，开始一个更新数据的事务并锁住了表，然后新建多个查询窗口，在各个不同窗口中，分别执行下面两条语句：
-- 查询第一个表
select * from TestDead
-- 查询第二个表
select * from TestDead2
1 -- 查询第一个表 2 3 select * from TestDead 4 5 6 -- 查询第二个表 7 8 select * from TestDead2
现在由于表被锁住，是查询不出数据，除非在后面加上 with(nolock)，如：
1 select * from TestDead2 with(nolock)
1 select * from TestDead2 with(nolock)
列出并杀掉那些死锁的进程：
-- =============================================
-- Author: Mike Deng
-- Create date: 2014/05/14
-- Description: 杀掉死锁进程
-- =============================================
DECLARE @toKill BIT = 0 --0 或 NULL 只显示， 1.杀掉
BEGIN
DECLARE @sql NVARCHAR(MAX);
DECLARE @fromSql NVARCHAR(MAX);
-- 查询出所有死锁和与被阻塞的进程
SET @fromSql = '
DECLARE @cmd NVARCHAR(MAX);
;WITH CTE as(
SELECT DISTINCT Type = ''死锁的进程''
, a.spid
, blockId = 0
, DBName = DB_NAME(a.dbid)
, a.HostName
, ProgramName = a.Program_Name
, Dead = 1
FROM SYS.SYSPROCESSES a JOIN
SYS.SYSPROCESSES as b ON a.spid = b.blocked
WHERE a.blocked = 0
UNION ALL
SELECT DISTINCT Type = ''被阻塞的进程''
, a.spid
, blockId = a.blocked
, DBName = DB_NAME(a.dbid)
, a.HostName
, ProgramName = a.Program_Name
, Dead = 0
FROM SYS.SYSPROCESSES a WHERE blocked <> 0
) '
-- 根据传入的值，决定是否删除
IF(ISNULL(@toKill, 0) = 1) --如果等于 1，则杀掉
BEGIN
SET @sql = @fromSql
+ 'SELECT @cmd = (
SELECT ('' KILL '' + LTRIM(str(spid))) FROM cte WHERE dead = 1
FOR XML PATH('''')
)
IF(ISNULL(@cmd, '''') <> '''')
BEGIN
EXEC (@cmd);
PRINT ''已执行kill死锁命令【'' + @cmd + ''】， 锁已解除'';
END
ELSE IF(@@RowCount = 0)
BEGIN
PRINT ''未发现死锁, 不能杀掉'';
END'
END
ELSE
BEGIN -- 显示所有死锁与被阻塞的进程
SET @sql = @fromSql + ', cte2 AS( SELECT * FROM cte WHERE Dead = 1
UNION ALL
SELECT k.* FROM cte AS k JOIN
cte2 AS p ON k.blockId = p.spid
)
SELECT * FROM cte2 ORDER BY blockId';
END
EXEC SP_EXECUTESQL @sql
END
1 -- ============================================= 2 -- Author: Mike Deng 3 -- Create date: 2014/05/14 4 -- Description: 杀掉死锁进程 5 -- ============================================= 6 7 DECLARE @toKill BIT = 0 --0 或 NULL 只显示， 1.杀掉 8 9 BEGIN 10 DECLARE @sql NVARCHAR(MAX); 11 DECLARE @fromSql NVARCHAR(MAX); 12 13 -- 查询出所有死锁和与被阻塞的进程 14 SET @fromSql = ' 15 DECLARE @cmd NVARCHAR(MAX); 16 ;WITH CTE as( 17 SELECT DISTINCT Type = ''死锁的进程'' 18 , a.spid 19 , blockId = 0 20 , DBName = DB_NAME(a.dbid) 21 , a.HostName 22 , ProgramName = a.Program_Name 23 , Dead = 1 24 FROM SYS.SYSPROCESSES a JOIN 25 SYS.SYSPROCESSES as b ON a.spid = b.blocked 26 WHERE a.blocked = 0 27 UNION ALL 28 SELECT DISTINCT Type = ''被阻塞的进程'' 29 , a.spid 30 , blockId = a.blocked 31 , DBName = DB_NAME(a.dbid) 32 , a.HostName 33 , ProgramName = a.Program_Name 34 , Dead = 0 35 FROM SYS.SYSPROCESSES a WHERE blocked <> 0 36 ) ' 37 38 -- 根据传入的值，决定是否删除 39 IF(ISNULL(@toKill, 0) = 1) --如果等于 1，则杀掉 40 BEGIN 41 SET @sql = @fromSql 42 + 'SELECT @cmd = ( 43 SELECT ('' KILL '' + LTRIM(str(spid))) FROM cte WHERE dead = 1 44 FOR XML PATH('''') 45 ) 46 47 IF(ISNULL(@cmd, '''') <> '''') 48 BEGIN 49 EXEC (@cmd); 50 PRINT ''已执行kill死锁命令【'' + @cmd + ''】， 锁已解除''; 51 END 52 ELSE IF(@@RowCount = 0) 53 BEGIN 54 PRINT ''未发现死锁, 不能杀掉''; 55 END' 56 END 57 ELSE 58 BEGIN -- 显示所有死锁与被阻塞的进程 59 SET @sql = @fromSql + ', cte2 AS( SELECT * FROM cte WHERE Dead = 1 60 UNION ALL 61 SELECT k.* FROM cte AS k JOIN 62 cte2 AS p ON k.blockId = p.spid 63 ) 64 SELECT * FROM cte2 ORDER BY blockId'; 65 END 66 67 EXEC SP_EXECUTESQL @sql 68 END
查询结果：
设置参数 @toKill = 1，杀掉它们。
完工！