SQL Server DeadLock 分析

1. 设置 XEvents 会话以收集死锁

1.1 扩展事件-会话-右键新建会话向导

1.2 设置会话名称

1.3 选择要捕获的事件

database_xml_deadlock_report

1.4 捕获全局字段

1.5 指定会话存储

1.6 结束

1.7 确保启动会话

2. 制造死锁

2.1 准备数据表及数据

2.1.1 准备数据库

TestBug

2.1.2 创建表

-- TestBug.dbo.Product definition

-- Drop table

-- DROP TABLE TestBug.dbo.Product;

CREATE TABLE TestBug.dbo.Product (
        Id bigint NOT NULL,
        Name nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        Color nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        CreateTime datetime NOT NULL,
        UpdateTime datetime NOT NULL,
        CONSTRAINT PK_Product PRIMARY KEY (Id)
);
-- TestBug.dbo.ProductDescription definition

-- Drop table

-- DROP TABLE TestBug.dbo.ProductDescription;

CREATE TABLE TestBug.dbo.ProductDescription (
        Id bigint NOT NULL,
        ProductId bigint NOT NULL,
        Description nvarchar(500) COLLATE Chinese_PRC_CI_AS NOT NULL,
        CreateTime datetime NOT NULL,
        UpdateTime datetime NOT NULL,
        CONSTRAINT PK_ProductDescription PRIMARY KEY (Id)
);

2.1.3 添加数据

INSERT
        INTO
        TestBug.dbo.Product (Id,
        Name,
        Color,
        CreateTime,
        UpdateTime)
VALUES
         (10001,
N'IPAD',
N'Red',
'2022-11-26 00:00:00.0',
'2022-11-26 20:34:25.46'),
         (10002,
N'Mac',
N'White',
'2022-11-26 00:00:00.0',
'2022-11-26 00:00:00.0');

INSERT
        INTO
        TestBug.dbo.ProductDescription (Id,
        ProductId,
        Description,
        CreateTime,
        UpdateTime)
VALUES
         (20001,
10001,
N'红色的IPAD',
'2022-11-26 00:00:00.0',
'2022-11-26 20:34:34.537'),
         (20002,
10002,
N'白色的Mac电脑',
'2022-11-26 00:00:00.0',
'2022-11-26 00:00:00.0');

2.2 模拟死锁过程

2.2.1 死锁会话脚本A

Begin
        Transaction
        UPDATE
        Product
set
        UpdateTime = GETDATE()
WHERE
        Color = 'Red';

UPDATE
        ProductDescription
set
        UpdateTime = GETDATE()
FROM
        ProductDescription as pd
join Product as p on
        pd.ProductId = p.Id
WHERE
        p.Color = 'Red';

commit transaction;

2.2.2 死锁会话脚本B

UPDATE
        ProductDescription
set
        UpdateTime = GETDATE()
FROM
        ProductDescription as pd
join Product as p on
        pd.ProductId = p.Id
WHERE
        p.Color = 'Red';

2.2.3 步骤 1——新建查询会话A

执行如下脚本

Begin
        Transaction
        UPDATE
        Product
set
        UpdateTime = GETDATE()
WHERE
        Color = 'Red';

2.2.4 步骤 2——新建查询会话B

执行如下脚本

UPDATE
        ProductDescription
set
        UpdateTime = GETDATE()
FROM
        ProductDescription as pd
join Product as p on
        pd.ProductId = p.Id
WHERE
        p.Color = 'Red';

2.2.5 步骤 3——回到查询会话A

执行如下脚本

UPDATE
        ProductDescription
set
        UpdateTime = GETDATE()
FROM
        ProductDescription as pd
join Product as p on
        pd.ProductId = p.Id
WHERE
        p.Color = 'Red';

2.2.6 步骤 4——回到查询会话B

死锁已经产生

2.2.7 步骤 5——再回到查询会话A

提交事务

commit transaction;

2.2.8 步骤 6——结束

3. 查看死锁

3.1 选择-检测死锁-查看目标数据

3.2 双击查看xml_report

3.3 保存为 xdl文件，然后关闭上一步打开的 XML 页

3.4 双击打开 或者 直接拖动到SSMS打开

3.5 查看死锁图

3.6 结束

参考

https://learn.microsoft.com/zh-cn/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer

https://learn.microsoft.com/zh-cn/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16