单客户端单会话下模拟SQL Server死锁以测试重试逻辑的方案咨询

以下方案完全在单个会话、单个客户端连接内实现，执行后会直接抛出1205死锁错误，无需开启其他会话或线程，可直接用于重试逻辑测试。

可直接执行的存储过程（稳定复现版） #

CREATE OR ALTER PROCEDURE dbo.SimulateSingleSessionDeadlock
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    -- 自动创建临时测试表，无需提前准备资源
    IF OBJECT_ID(N'tempdb..#DeadlockTest1', N'U') IS NOT NULL DROP TABLE #DeadlockTest1;
    IF OBJECT_ID(N'tempdb..#DeadlockTest2', N'U') IS NOT NULL DROP TABLE #DeadlockTest2;
    CREATE TABLE #DeadlockTest1 (ID INT PRIMARY KEY, Val INT);
    CREATE TABLE #DeadlockTest2 (ID INT PRIMARY KEY, Val INT);
    INSERT INTO #DeadlockTest1 VALUES (1, 10), (2, 20);
    INSERT INTO #DeadlockTest2 VALUES (1, 100), (2, 200);

    BEGIN TRY
        BEGIN TRANSACTION;
        -- 第一步：对测试表加排他锁
        UPDATE #DeadlockTest1 SET Val = Val + 1 WHERE ID = 1;

        -- 临时修改并行阈值保证触发并行查询
        DECLARE @OriginalCostThreshold INT;
        SELECT @OriginalCostThreshold = CAST(value AS INT) FROM sys.configurations WHERE name = 'cost threshold for parallelism';
        EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
        EXEC sp_configure 'cost threshold for parallelism', 0; RECONFIGURE WITH OVERRIDE;

        -- 构造高开销查询触发多工作线程并行，触发线程间锁循环等待
        SELECT COUNT(*)
        FROM #DeadlockTest1 t1
        INNER HASH JOIN #DeadlockTest2 t2 ON t1.ID = t2.ID
        CROSS JOIN sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2
        WHERE t1.Val > 0
        OPTION (MAXDOP 4, RECOMPILE); -- 强制4并行度

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 自动恢复原实例配置，无残留影响
        EXEC sp_configure 'cost threshold for parallelism', @OriginalCostThreshold; RECONFIGURE WITH OVERRIDE;
        EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;

        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        -- 直接抛出原始*1205*死锁错误
        THROW;
    END CATCH
END
GO

调用方法 #

EXEC dbo.SimulateSingleSessionDeadlock;

轻量无配置修改版（2016+版本适用） #

如果无法修改实例配置，可直接执行以下语句触发死锁：

BEGIN TRY
    BEGIN TRANSACTION;
    -- 先获取系统元数据表的排他锁
    SELECT * FROM sys.tables WITH (TABLOCKX, HOLDLOCK) WHERE object_id = OBJECT_ID(N'sys.tables');
    -- 触发表创建的内部元数据校验，需要获取sys.tables共享锁，形成循环等待
    DECLARE @Test TABLE (ID INT);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH
GO

实现说明 #

• 全程仅在当前单会话内运行，不需要额外的连接、线程或外部操作
• 存储过程版本执行完成后会自动恢复原实例的cost threshold for parallelism配置，不会留下脏配置
• 建议优先在非生产环境执行测试，避免实例配置临时修改带来的性能波动

内容的提问来源于stack exchange，提问作者VinZCodz