Reports

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!

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

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

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

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'
Enable RPC for your loopback server in Linked Server Properties, as per https://stackoverflow.com/a/55797974/10248941

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.CertEaseEthigen_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

79203565