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

Question

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.

Answer 1

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

Answer 2

You can exploit a bug that Microsoft seems in no hurry to fix by running

begin tran
go

CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
go

declare @myPK dbo.IntIntSet;
go

rollback

This SQL will cause a deadlock with itself. Lots more details at Aaron Bertand's blog http://sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock

Answer 3

(Apparently I don't have enough reputation to add a comment. So posting as an answer.)

A deadlock requires at least two processes. the only exception being is the intra-query parallel deadlocks which are kind of impossible to reproduce.

However you can simulate a deadlock on two processes running the exact same query (or sp). Some ideas here

Answer 4

This works reliably from a single session. Use service broker activation to invoke the second thread which is required for a deadlock.

NOTE1: cleanup script not included
NOTE2: service broker has to be enabled: 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

Answer 5

Simplest way to reproduce in C# with Parallel e.g.

    var List = ... (add some items with same ids)

    Parallel.ForEach(List, 
        (item) =>
    {

        ReportsDataContext erdc = null;
        try
        {
            using (TransactionScope scope = new TransactionScope())
            {
                erdc = new ReportsDataContext("....connection....");
                var report = erdc.Report.Where(x => x.id == item.id).Select(x => x);
                report.Count++
                erdc.SubmitChanges();

                scope.Complete();
            }

            if (erdc != null)
                erdc.Dispose();
        }
        catch (Exception ex)
        {
            if (erdc != null)
                erdc.Dispose();
            ErrorLog.LogEx("multi thread victim", ex);
        }

more interest how to prevent that error in real cross thread situation?

Answer 6

I had difficulty getting Paul's answer to work. I made some small changes to get it working.

The key is to begin and rollback the sp_simulatedeadlock transaction within the procedure itself. I made no changes to the procedure in Paul's answer.

DECLARE @DeadlockCounter INT = NULL

SELECT @DeadlockCounter = 0

WHILE @DeadlockCounter < 10
BEGIN
    BEGIN TRY
    /* The procedure was leaving uncommitted transactions, I rollback the transaction in the catch block */
        BEGIN tran simulate
            Exec sp_simulatedeadlock

        /* Code you want to deadlock */

        SELECT @DeadlockCounter = 10
    END TRY
    BEGIN CATCH
        Rollback tran simulate

        PRINT ERROR_MESSAGE()

        IF (ERROR_MESSAGE() LIKE '%deadlock%' OR ERROR_NUMBER() = 1205) AND @DeadlockCounter < 10
            BEGIN
                SELECT @DeadlockCounter +=1
                PRINT @DeadlockCounter

                IF @DeadlockCounter = 10
                BEGIN
                    RAISERROR('Deadlock limit exceeded or error raised', 16, 10);
                END
            END
    END CATCH
END

Answer 7

If you happen to run into problems with the GO/go keywords (Incorrect syntax near 'GO') in any of the scripts above, it's important to know, that this instruction only seems to work in Microsoft SQL Server Management Studio/sqlcmd:

The GO keyword is not T-SQL, but a SQL Server Management Studio artifact that allows you to separate the execution of a script file in multiple batches.I.e. when you run a T-SQL script file in SSMS, the statements are run in batches separated by the GO keyword. […]

SQL Server doesn't understand the GO keyword. So if you need an equivalent, you need to separate and run the batches individually on your own.

(from JotaBe's answer to another question)

So if you want to try out e.g. Michael J Swart's Answer through DBeaver for example, you'll have to remove the go's and run all parts of the query on its own.