StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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..</p> <p>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.</p> <p>In the place you want to simulate a deadlock, insert a call to <code>sp_simulatedeadlock</code>. Run your process, and the deadlock should occur.</p> <p>When done testing, stop the SSMS query and run the cleanup code at the bottom.</p> <pre><code>/* 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload