如何在单个进程中模拟 SQL Server 中的死锁？

Question

我们的客户端代码检测到死锁，等待一段时间，然后重试请求最多 5 次。重试逻辑根据错误号 1205 检测死锁。

我的目标是测试各种存储过程中的死锁重试逻辑和死锁处理。我可以使用两个不同的连接创建死锁。但是，我想在单个存储过程本身内部模拟死锁。

死锁会引发以下错误消息：

消息 1205，第 13 级，状态 51，第 1 行

事务（进程 ID 66）在锁定资源上与另一个进程死锁，并已被选为死锁牺牲品。重新运行事务。

我看到此错误消息在 sys.messages 中：

 select * from sys.messages where message_id = 1205 and language_id = 1033

message_id language_id severity  is_event_logged   text
1205       1033        13        0                 Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

我无法使用 RAISERROR 引发此错误：

 raiserror(1205, 13, 51)

消息 2732，第 16 层，状态 1，第 1 行

错误号 1205 无效。该数字必须从 13000 到 2147483647，并且不能是 50000。

我们的死锁重试逻辑检查错误号是否为 1205。死锁需要与普通死锁具有相同的消息 ID、级别和状态。

有没有办法模拟死锁（使用 RAISERROR 或任何其他方式）并 仅通过一个进程 获得相同的消息编号？

我们的数据库使用 SQL 2005 兼容性，尽管我们的服务器从 2005 到 2008 R2 有所不同。

原文由 Paul Williams 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

正如许多人指出的那样，答案是否定的，单个进程本身无法可靠地死锁。我想出了以下解决方案来模拟开发或测试系统上的死锁。

在 SQL Server Management Studio 窗口中运行以下脚本。 （仅在 2008 R2 上测试。）您可以根据需要让它一直运行。

在要模拟死锁的地方，插入对 sp_simulatedeadlock 的调用。运行您的进程，应该会发生死锁。

完成测试后，停止 SSMS 查询并运行底部的清理代码。

 /*
This script helps simulate deadlocks.  Run the entire script in a SQL query window.  It will continue running until stopped.
In the target script, insert a call to sp_simulatedeadlock where you want the deadlock to occur.
This stored procedure, also created below, causes the deadlock.
When you are done, stop the execution of this window and run the code in the cleanup section at the bottom.
*/
set nocount on

if object_id('DeadlockTest') is not null
    drop table DeadlockTest

create table DeadlockTest
(
    Deadlock_Key int primary key clustered,
    Deadlock_Count int
)
go

if exists (select * from sysobjects where id = object_id(N'sp_simulatedeadlock')
           AND objectproperty(id, N'IsProcedure') = 1)
drop procedure sp_simulatedeadlock
GO

create procedure sp_simulatedeadlock
(
    @MaxDeadlocks int = -1 -- specify the number of deadlocks you want; -1 = constant deadlocking
)
as begin

    set nocount on

    if object_id('DeadlockTest') is null
        return

    -- Volunteer to be a deadlock victim.
    set deadlock_priority low

    declare @DeadlockCount int

    select @DeadlockCount = Deadlock_Count -- this starts at 0
    from DeadlockTest
    where Deadlock_Key = 2

    -- Trace the start of each deadlock event.
    -- To listen to the trace event, setup a SQL Server Profiler trace with event class "UserConfigurable:0".
    -- Note that the user running this proc must have ALTER TRACE permission.
    -- Also note that there are only 128 characters allowed in the trace text.
    declare @trace nvarchar(128)

    if @MaxDeadlocks > 0 AND @DeadlockCount > @MaxDeadlocks
    begin

        set @trace = N'Deadlock Test @MaxDeadlocks: ' + cast(@MaxDeadlocks as nvarchar) + N' @DeadlockCount: ' + cast(@DeadlockCount as nvarchar) + N' Resetting deadlock count.  Will not cause deadlock.'
        exec sp_trace_generateevent
            @eventid = 82,  -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
            @userinfo = @trace

        -- Reset the number of deadlocks.
        -- Hopefully if there is an outer transaction, it will complete and persist this change.
        update DeadlockTest
        set Deadlock_Count = 0
        where Deadlock_Key = 2
        return
    end

    set @trace = N'Deadlock Test @MaxDeadlocks: ' + cast(@MaxDeadlocks as nvarchar) + N' @DeadlockCount: ' + cast(@DeadlockCount as nvarchar) + N' Simulating deadlock.'
    exec sp_trace_generateevent
        @eventid = 82,  -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
        @userinfo = @trace

    declare @StartedTransaction bit
    set @StartedTransaction = 0
    if @@trancount = 0
    begin
        set @StartedTransaction = 1
        begin transaction
    end

    -- lock 2nd record
    update DeadlockTest
    set Deadlock_Count = Deadlock_Count
    from DeadlockTest
    where Deadlock_Key = 2

    -- lock 1st record to cause deadlock
    update DeadlockTest
    set Deadlock_Count = Deadlock_Count
    from DeadlockTest
    where Deadlock_Key = 1

    if @StartedTransaction = 1
        rollback
end
go

insert into DeadlockTest(Deadlock_Key, Deadlock_Count)
select 1, 0
union select 2, 0

-- Force other processes to be the deadlock victim.
set deadlock_priority high

begin transaction

while 1 = 1
begin

    begin try

        begin transaction

        -- lock 1st record
        update DeadlockTest
        set Deadlock_Count = Deadlock_Count
        from DeadlockTest
        where Deadlock_Key = 1

        waitfor delay '00:00:10'

        -- lock 2nd record (which will be locked when the target proc calls sp_simulatedeadlock)
        update DeadlockTest
        set Deadlock_Count = Deadlock_Count
        from DeadlockTest
        where Deadlock_Key = 2

        rollback

    end try
    begin catch
        print 'Error ' + convert(varchar(20), ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
        goto cleanup
    end catch

end

cleanup:

if @@trancount > 0
    rollback

drop procedure sp_simulatedeadlock
drop table DeadlockTest

原文由 Paul Williams 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

这可以从单个会话可靠地工作。使用服务代理激活来调用死锁所需的第二个线程。

NOTE1：不包括清理脚本

注意2：必须启用服务代理：ALTER DATABASE dbname SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE；

 EXEC sp_executesql N'
CREATE OR ALTER PROCEDURE DeadlockReceive
AS
DECLARE @MessageBody NVARCHAR(1000);
RECEIVE @MessageBody = CAST(message_body AS NVARCHAR(1000) )FROM DeadlockQueue
SELECT @MessageBody
EXEC sp_executesql @MessageBody;'

IF EXISTS (SELECT * FROM sys.services WHERE name = 'DeadlockService') DROP SERVICE DeadlockService
IF OBJECT_ID('DeadlockQueue') IS NOT NULL DROP QUEUE dbo.DeadlockQueue
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'DeadlockContract') DROP CONTRACT DeadlockContract
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'DeadlockMessage') DROP MESSAGE TYPE DeadlockMessage
DROP TABLE IF EXISTS DeadlockTable1 ;
DROP TABLE IF EXISTS DeadlockTable2 ;

CREATE MESSAGE TYPE DeadlockMessage VALIDATION = NONE;
CREATE QUEUE DeadlockQueue WITH STATUS = ON, ACTIVATION (PROCEDURE_NAME = DeadlockReceive, EXECUTE AS SELF, MAX_QUEUE_READERS = 1);
CREATE CONTRACT DeadlockContract AUTHORIZATION dbo (DeadlockMessage SENT BY ANY);
CREATE SERVICE DeadlockService ON QUEUE DeadlockQueue (DeadlockContract);

CREATE TABLE DeadlockTable1 (Value INT); INSERT dbo.DeadlockTable1 SELECT 1;
CREATE TABLE DeadlockTable2 (Value INT); INSERT dbo.DeadlockTable2 SELECT 1;

DECLARE @ch UNIQUEIDENTIFIER
BEGIN DIALOG @ch FROM SERVICE DeadlockService TO SERVICE 'DeadlockService' ON CONTRACT DeadlockContract WITH ENCRYPTION = OFF ;
SEND ON CONVERSATION @ch MESSAGE TYPE DeadlockMessage (N'
set deadlock_priority high;
begin tran;
update DeadlockTable2 set value = 5;
waitfor delay ''00:00:01'';
update DeadlockTable1 set value = 5;
commit')

SET DEADLOCK_PRIORITY LOW
BEGIN TRAN
    UPDATE dbo.DeadlockTable1 SET Value = 2
    waitfor delay '00:00:01';
    UPDATE dbo.DeadlockTable2 SET Value = 2
COMMIT

原文由 Vad 发布，翻译遵循 CC BY-SA 4.0 许可协议