RDS SQL Server死锁（Deadlock）系列之一使用DBCC捕获死锁-CSDN博客

问题引入

在日常运维阿里云RDS SQL Server产品过程中，经常会被客户问道：“应用程序被死锁报错啦？影响很大，到底是哪个进程导致了死锁发生的啊？怎么解决啊？怎么办呀？”。从客户一连串的问题中，我们深刻体会到了死锁问题的紧迫性和影响之大。授人予鱼而不如授人予渔，RDS SQL Server死锁系列文章就是为了帮助客人彻底解决死锁问题为初衷而诞生的。本篇文章是系列文章的开篇，主要是讨论如何使用DBCC来捕获死锁信息，内容包括：
DBCC捕获死锁
死锁测试
死锁分析
解决方法

DBCC捕获死锁

DBCC捕获死锁是利用了SQL Server死锁自动监测机制（默认每5秒运行一次）的返回信息，来将死锁信息记录到数据库日志记录中，我们可以事后从错误日中来查看这些有用的死锁信息，包括：
死锁的牺牲进程
死锁发生时的进程信息
死锁发生时争抢的资源
其实，DBCC捕获死锁信息的方法本身非常简单，只需要使用DBCC命令打开两个跟踪标记（1222和1204）即可。方法如下：

USE master
GO

DBCC TRACEON(1222,-1)
GO

--also write like this, that’s fine to use any one 
DBCC TRACEON (1204, 1222, -1)
GO

跟踪标记打开后，我们可以使用下面的语句再次检查，确保标记打开成功：

DBCC TRACESTATUS(-1)
GO

截图如下所示：

在这里也顺便把如何关闭死锁跟踪标记的方法写到这里：

DBCC TRACEOFF (1204, 1222, -1)
GO

--split into two stats
DBCC TRACEOFF (1204,-1)
GO
DBCC TRACEOFF (1222,-1)
GO

死锁测试

获取死锁信息的跟踪标记已经打开，接下来进行死锁测试。首先，在Test数据库下创建两个测试表，表名分别为：dbo.test_deadlock1和dbo.test_deadlock2，代码如下：

IF DB_ID('Test') IS NULL
    CREATE DATABASE Test;
GO

USE Test
GO

-- create two test tables
IF OBJECT_ID('dbo.test_deadlock1','u') IS NOT NULL
    DROP TABLE dbo.test_deadlock1
GO

CREATE TABLE dbo.test_deadlock1(
id INT IDENTITY(1,1) not null PRIMARY KEY
,name VARCHAR(20) null
);

IF OBJECT_ID('dbo.test_deadlock2','u') IS NOT NULL
    DROP TABLE dbo.test_deadlock2
GO

CREATE TABLE dbo.test_deadlock2(
id INT IDENTITY(1,1) not null PRIMARY KEY
,name VARCHAR(20) null
);

INSERT INTO dbo.test_deadlock1
SELECT 'AA'
UNION ALL
SELECT 'BB';


INSERT INTO dbo.test_deadlock2
SELECT 'AA'
UNION ALL
SELECT 'BB';
GO

接下来，我们使用SSMS打开一个新的连接，我们假设叫session 1，执行如下语句：

--session 1
USE Test
GO

BEGIN TRAN 
UPDATE dbo.test_deadlock1
SET name = 'CC'
WHERE id = 1
;
WAITFOR DELAY '00:00:05'

UPDATE dbo.test_deadlock2
SET name = 'CC'
WHERE id = 1
;
ROLLBACK

紧接着，我们使用SSMS打开第二个连接，假设叫Session 2，执行下面的语句：

--session 2
USE Test
GO

BEGIN TRAN 
UPDATE dbo.test_deadlock2
SET name = 'CC'
WHERE id = 1
;

UPDATE dbo.test_deadlock1
SET name = 'CC'
WHERE id = 1
;
COMMIT

一段时间以后，你会发现Session 2执行的语句会被死锁，做为了死锁的牺牲品，错误信息如下：

Msg 1205, Level 13, State 51, Line 11
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

截图为证：

死锁分析

死锁场景，我们已经模拟出来了，接下来就是分析死锁的时候了。让我们查看错误日志：

EXEC sys.sp_readerrorlog

截图如下所示：

从这个死锁信息中，我们不难发现几个非常有用的信息：
参与死锁的进程（process-list）：锁住其他进程的进程和死锁牺牲者进程（会有deadlock victim标记）。
死锁发生时，进程执行的语句（inputbuf）：这个很重要，找到了语句就可以针对死锁的语句进行针对性的优化解决。
进程争抢的资源（resource-list）：死锁发生时，到底进程之间在争抢什么资源，死锁的类型是什么？本例资源争抢发生在表Test.dbo.test_deadlock1 的主键上indexname=PK__test_dea__3213E83F07020F21，死锁类型为X锁（排他锁）。

解决方法

通过SQL Server错误日志中死锁信息的分析，我们可以从死锁发生时进程执行的语句发现，死锁发生的原因是两个UPDATE进程操作的表顺序不一致导致的。我们只需要调整其中一个进程的UPDATE表顺序即可解决这个死锁问题。比如，调整Session 2的执行语句，如下：

--session 2
USE Test
GO

BEGIN TRAN 
UPDATE dbo.test_deadlock1
SET name = 'CC'
WHERE id = 1
;

UPDATE dbo.test_deadlock2
SET name = 'CC'
WHERE id = 1
;
COMMIT