SQL Server中怎么排查死锁问题

一、背景

我们在UAT环境压测的时候，遇到了如下的死锁异常。

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

我们立即 查看应用日志，找到报错的方法查看，发现在一个方法对同一张表进行了3种操作，先INSERT，然后SELECT，最后DELETE。也就是说在同一个事务中，对同一张表先插入，然后查询，最后根据查询结果删除。此时，我大概意识到问题所在了。但是UAT环境中，SQL Server数据库是部署在客户侧的，不太好拿死锁报告。所以我决定在本地模拟出来这个死锁问题，然后进行修复。

二、本地模拟死锁

1.业务场景简介

我们有一张userToken表，核心字段有id、loginId和token，主要用来记录用户的登录token，用来控制系统中一个用户能不能多次登录。

我们出现死锁问题的方法是登录方法，该方法在登录时会向userToken表中插入一条数据，插入成功之后回去第三方检查这个用户的状态等是否正常，因为用户数据是第三方维护的。如果检查结果是这个用户状态不可用，那么就会去删除这个用户的token数据，同时给前端返回相应的异常信息。问题就出在删除的时候，是先根据用户的loginId去查询出该用户的所有token数据，然后找出本次登录的token数据，进行删除。为什么这里有问题后面我们再详细说明。

2.在本地模拟死锁

1). 准备数据

要模拟这个死锁场景，可以在 SQL Server Management Studio (SSMS) 或者DBeaver中创建一个简单的脚本，我使用的是DBeaver也很好用。使用以下存储过程代码：

-- 1.创建一个示例 userToken 表
CREATE TABLE userToken (
    id INT IDENTITY(1,1) PRIMARY KEY,
    loginId VARCHAR(50),
    token VARCHAR(50)
);

-- 2.创建一个存储过程，以模拟登录过程
CREATE PROCEDURE sp_Login
    @loginId VARCHAR(50)
AS
BEGIN
    -- 插入一个新记录
    INSERT INTO userToken (loginId, token) VALUES (@loginId, 'token_' + CONVERT(VARCHAR(50), NEWID()));

    WAITFOR DELAY '00:00:05'; -- 模拟延迟，更容易发生死锁
    -- 选择和删除记录
    DECLARE @id INT;
    SELECT @id = id FROM userToken WHERE loginId = @loginId;
    DELETE FROM userToken WHERE id = @id;
END;

-- 3. 在第一个窗口中模拟第一个线程

DECLARE @loginId VARCHAR(50) = 'user';

BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;

-- 4. 在第二个窗口中模拟第二个线程
DECLARE @loginId VARCHAR(50) = 'user';

BEGIN TRANSACTION;
EXEC sp_Login @loginId;
COMMIT TRANSACTION;

-- 5. 在两个窗口中同时运行，模拟并发登录，并观察执行结果

2).执行存储过程并观察死锁发生

按照上面的步骤创建表和存储过程，并分别在两个窗口中同时执行。可能需要执行多次才能出现死锁。如果出现下面的两种之一，就说明已经发生了死锁。

情况一：

数据库连接工具控制台出现以下错误：SQL Error [1205] [40001]: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

情况二：

通过sqlserver自带的扩展事件[system_health]查看死锁的详细信息，执行下面的sql如果表格中有数据则已经发生了死锁。

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'