Databases
StackOverflow2013
Postsdbo
POHow to simulate a deadlock in SQL Server in a single process?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to simulate a deadlock in SQL Server in a single process?
    primarykey
    data
    text
    <p>Our client side code detects deadlocks, waits for an interval, then retries the request up to 5 times. The retry logic detects the deadlocks based on the error number 1205.</p> <p>My goal is to test both the deadlock retry logic and deadlock handling inside of various stored procedures. I can create a deadlock using two different connections. However, I would like to simulate a deadlock inside of a single stored procedure itself.</p> <p>A deadlock raises the following error message:</p> <blockquote> <p>Msg 1205, Level 13, State 51, Line 1<br> Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</p> </blockquote> <p>I see this error message is in <a href="http://msdn.microsoft.com/en-us/library/ms187382.aspx" rel="noreferrer"><code>sys.messages</code></a>:</p> <pre><code>select * from sys.messages where message_id = 1205 and language_id = 1033 message_id language_id severity is_event_logged text 1205 1033 13 0 Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction. </code></pre> <p>I can't raise this error using <a href="http://msdn.microsoft.com/en-us/library/ms178592.aspx" rel="noreferrer"><code>RAISERROR</code></a>:</p> <pre><code>raiserror(1205, 13, 51) </code></pre> <blockquote> <p>Msg 2732, Level 16, State 1, Line 1<br> Error number 1205 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.</p> </blockquote> <p>Our deadlock retry logic checks if the error number is 1205. The deadlock needs to have the same message ID, level, and state as a normal deadlock.</p> <p>Is there a way to simulate a deadlock (with RAISERROR or any other means) and get the same message number out <em>with just one process</em>?</p> <p>Our databases are using SQL 2005 compatibility, though our servers vary from 2005 through 2008 R2.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPaul Williams
    1. USPaul Williams
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to simulate a deadlock in SQL Server in a single process?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to simulate a deadlock in SQL Server in a single process?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to simulate a deadlock in SQL Server in a single process?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI don't think this is possible - the nature of a deadlock is two different processes vying for a lock on an object. A single process could take out the lock but then who is it going to deadlock with? I'll watch this question with interest. If you are calling from C# code, then you could simulate this for testing purposes by raising a SqlException with the same values (`SqlException.Number = 1205`) as the deadlock SqlException.
      singulars
      1. POHow to simulate a deadlock in SQL Server in a single process?
      1. USdash
    2. COI have seen intra-query deadlocks in parallel queries, and I have seen a query deadlock itself when updating a single row in a transaction twice with two separate indexes. I think both of these deadlocks were SQL Server bugs that have been fixed in various service packs. But yes, it would be extremely handy to simulate this easily from inside a single stored procedure.
      singulars
      1. POHow to simulate a deadlock in SQL Server in a single process?
      1. USPaul Williams
    3. COIt's a shame that RaiseError forces the msg id over 50,000 otherwise you could simulate it quite easily. In .Net you could, of course raise your own error in SQL Server, which your app recatches then rethrows as a SqlException with number 1250; or you could catch this error and treat it as a deadlock.
      singulars
      1. POHow to simulate a deadlock in SQL Server in a single process?
      1. USdash
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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