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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |