How to simulate a deadlock in SQL Server in a single process?
===

Our client side code detects deadlocks, waits for an interval, then retries the request up to 5 times.  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.

A deadlock raises the following error message:

> Msg 1205, Level 13, State 51, Line 1  
> Transaction (Process ID 66) was
> deadlocked on lock resources with another process and has been chosen
> as the deadlock victim. Rerun the transaction.

I see this error message is in [`sys.messages`][1]:

    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.

I can't raise this error using [`RAISERROR`][2]:

    raiserror(1205, 13, 51)

> Msg 2732, Level 16, State 1, Line 1  
> Error number 1205 is invalid.
> The number must be from 13000 through 2147483647 and it cannot be 50000.

I would like to simluate the deadlock as precisely as possible, so I would prefer it to have the same message ID, level, and state.

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*?

Our databases are using SQL 2005 compatibility, though our servers vary from 2005 through 2008 R2.


  [1]: http://msdn.microsoft.com/en-us/library/ms187382.aspx
  [2]: http://msdn.microsoft.com/en-us/library/ms178592.aspx