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

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

(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

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

Again based on Michael J Swart's answer above this can be done in a single batch;

BEGIN TRAN CREATE TYPE dbo.TestType AS TABLE(Value0 Int NOT NULL) EXECUTE sp_executesql N'DECLARE @t dbo.TestType' 

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?

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 

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.

Taking Michael J Swart's answer one step further, I found a way to use this in a stored procedure.

Hopefully someone will find this useful!

1. Create the Type:

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

2. Create this stored procedure:

    CREATE PROCEDURE [dbo].[GenDeadlock] AS SET NOCOUNT ON begin tran dead if (type_id('IntIntSet') IS NOT NULL) BEGIN drop type IntIntSet END CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int NOT NULL ) declare @myPK dbo.IntIntSet; if (1 = 2) rollback tran dead 

3. Then call the procedure like this:

    begin try exec dbo.GenDeadlock end try begin catch exec dbo.GenDeadlock end catch 

NOTE: The first call to GenDeadLock results in:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

So try/catching that and running it again results in:

Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

None of the answers so far address an important part of the question, i.e. that only a fixed number of deadlocks are to occur. This allows the user to test whether their retry algorithm will succeed after a certain number of attempts. To the technique shown by others for creating deadlocks, I've added a technique for creating autonomous transactions so that I can decrement a counter in a trigger without it being rolled back. There are a few steps to this, but all of them are simple and straightforward, and none of them require you to alter your production code.

Apologies for the poor code formatting, I tried!

1. Create the proc create_deadlock as per https://sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock

CREATE proc create_deadlock as begin -- Always run this in a transaction, otherwise it will run to -- completion and you'll get an unwanted type created. if @@TRANCOUNT < 1 begin raiserror('create_deadlock should be run in a transaction', 16, 1); return 50000; end

DECLARE @sqlText NVARCHAR(256); SET @sqlText = 'CREATE TYPE dbo.UNWANTED_TEMP_TYPE FROM VARCHAR(320);' EXEC (@sqlText); SET @sqlText = 'DECLARE @x TABLE (e dbo.UNWANTED_TEMP_TYPE);' EXEC (@sqlText); 

end

2. Create a table to hold the number of deadlocks you want on a given table:

   CREATE TABLE deadlock_limits( TableName sysname NOT NULL, Limit int NULL, CONSTRAINT pk_deadlock_limits PRIMARY KEY CLUSTERED ( TableName ASC ) ) ON PRIMARY

3. Create a proc to update deadlock_limits:

   create procedure set_deadlock_limit @TableName sysname, @Limit int as begin

    set nocount on; update deadlock_limits set Limit = @Limit where TableName = @TableName; if @@ROWCOUNT = 0 insert into deadlock_limits (TableName, Limit) values (@TableName, @Limit); 

   end

4. Create a loopback server, so we can create autonomous transactions, as per https://learn.microsoft.com/en-us/archive/blogs/sqlprogrammability/how-to-create-an-autonomous-transaction-in-sql-server-2008:

   EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME EXEC sp_serveroption loopback,N'remote proc transaction promotion','FALSE'

5. Enable RPC for your loopback server in Linked Server Properties, as per https://stackoverflow.com/a/55797974/10248941

6. Create a trigger on a table that your calling code will attempt to update. A deadlock will occur when you attempt the update:

   CREATE TRIGGER test_force_deadlock_on_MyTable ON dbo.MyTable AFTER INSERT,DELETE,UPDATE AS BEGIN

    set nocount on; -- Read the deadlock_limits table to see if there is a limit on how many deadlocks to create. -- If the limit is null, this means no limit. declare @num_locs int; select @num_locs = Limit from deadlock_limits where TableName = 'MyTable'; if @num_locs is null or @num_locs > 0 begin if @num_locs > 0 begin declare @NewLimit int = @num_locs - 1 exec loopback.MyDatabase.dbo.set_deadlock_limit 'MyTable', @NewLimit; end -- Force a deadlock exec create_deadlock; end 

   END

And that's it. To test how your calling code handles deadlocks, set the number of deadlocks you want in deadlock_limits and enable the trigger:

exec set_deadlock_limit 'MyTable', 1 alter table MyTable enable trigger test_force_deadlock_on_MyTable 

The trigger will decrement the Limit in the deadlock_limits table each time it runs. Because it calls set_deadlock_limit via the loopback server, the new value of Limit will not be rolled back. Each time you attempt the write the Limit counter will decrement, and it when it reaches zero your write will succeed:

update MyTable set Number = 1; -- First time fails with a deadlock update MyTable set Number = 1; -- second time succeeds 

When you are done testing, you need only disable the trigger:

alter table MyTable disable trigger test_force_deadlock_on_MyTable