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;