死锁的进程

杀掉那些死锁的进程

 

　　前些天，同事遇到一个死锁的问题，又想起三年前面试的时候问的一个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，杀掉它们。

完工！

 

 

 

标签: SQL Server 死锁 解除