sqlsql-serverunit-testingdeadlockraiserror

How to simulate a deadlock in SQL Server in a single process?


Our client side code detects deadlocks, waits for an interval, then retries the request up to 5 times. The retry logic detects the deadlocks based on the error number 1205.

My goal is to test both the deadlock retry logic and deadlock handling inside of various stored procedures. I can create a deadlock using two different connections. However, I would like to simulate a deadlock inside of a single stored procedure itself.

A deadlock raises the following error message:

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I see this error message is in 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.

I can't raise this error using RAISERROR:

raiserror(1205, 13, 51)

Msg 2732, Level 16, State 1, Line 1
Error number 1205 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.

Our deadlock retry logic checks if the error number is 1205. The deadlock needs to have the same message ID, level, and state as a normal deadlock.

Is there a way to simulate a deadlock (with RAISERROR or any other means) and get the same message number out with just one process?

Our databases are using SQL 2005 compatibility, though our servers vary from 2005 through 2008 R2.


Solution

  • As many have pointed out, the answer is no, a single process cannot reliably deadlock itself. I came up with the following solution to simulate a deadlock on a development or test system..

    Run the script below in a SQL Server Management Studio window. (Tested on 2008 R2 only.) You can leave it running as long as necessary.

    In the place you want to simulate a deadlock, insert a call to sp_simulatedeadlock. Run your process, and the deadlock should occur.

    When done testing, stop the SSMS query and run the cleanup code at the bottom.

    /*
    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