Print 'Error ' + convert(varchar(20), ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()ĬREATE OR ALTER PROCEDURE DeadlockReceive lock 2nd record (which will be locked when the target proc calls sp_simulatedeadlock) Force other processes to be the deadlock victim. Insert into DeadlockTest(Deadlock_Key, Deadlock_Count) Set = N'Deadlock Test ' + as nvarchar) + N' ' + as nvarchar) + N' Simulating deadlock.'Įxec = 82, - 82 = UserConfigurable:0 through 91 = bit Hopefully if there is an outer transaction, it will complete and persist this change. Will not cause deadlock.'Įxec = 82, - 82 = UserConfigurable:0 through 91 = Reset the number of deadlocks. If > 0 AND > = N'Deadlock Test ' + as nvarchar) + N' ' + as nvarchar) + N' Resetting deadlock count. Also note that there are only 128 characters allowed in the trace text. Note that the user running this proc must have ALTER TRACE permission. To listen to the trace event, setup a SQL Server Profiler trace with event class "UserConfigurable:0". Trace the start of each deadlock event. Select = Deadlock_Count - this starts at 0
If exists (select * from sysobjects where id = object_id(N'sp_simulatedeadlock')ĪND objectproperty(id, N'IsProcedure') = 1)Ĭreate procedure int = -1 - specify the number of deadlocks you want -1 = constant deadlocking
#Query deadlock sql server code
When you are done, stop the execution of this window and run the code in the cleanup section at the bottom. This stored procedure, also created below, causes the deadlock. In the target script, insert a call to sp_simulatedeadlock where you want the deadlock to occur. Run the entire script in a SQL query window. When done testing, stop the SSMS query and run the cleanup code at the bottom. Run your process, and the deadlock should occur. In the place you want to simulate a deadlock, insert a call to sp_simulatedeadlock. (Tested on 2008 R2 only.) You can leave it running as long as necessary. Run the script below in a SQL Server Management Studio window. I came up with the following solution to simulate a deadlock on a development or test system. Our databases are using SQL 2005 compatibility, though our servers vary from 2005 through 2008 R2.Īs many have pointed out, the answer is no, a single process cannot reliably deadlock itself. 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? The deadlock needs to have the same message ID, level, and state as a normal deadlock.
Our deadlock retry logic checks if the error number is 1205. The number must be from 13000 through 2147483647 and it cannot be 50000. I can't raise this error using RAISERROR: raiserror(1205, 13, 51) Message_id language_id severity is_event_logged textġ205 1033 13 0 Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. I see this error message is in sys.messages: select * from sys.messages where message_id = 1205 and language_id = 1033 However, I would like to simulate a deadlock inside of a single stored procedure itself.Ī deadlock raises the following error message:ĭeadlocked on lock resources with another process and has been chosenĪs the deadlock victim. I can create a deadlock using two different connections. My goal is to test both the deadlock retry logic and deadlock handling inside of various stored procedures. The retry logic detects the deadlocks based on the error number 1205. Our client side code detects deadlocks, waits for an interval, then retries the request up to 5 times.
0 kommentar(er)
