Simulate a dead lock on SQL server using single client and single session-CodePudding

I'm trying to simulate a deliberate deadlock on sql server, where I could test a piece of code which would do retries. Need a query/SP/Func which I can execute, later this query fails with 1205/deadlock and trigger my retry logic.

constraints: -Within a single client n single session. (Kind of reading a meta data n locking itself in a single session, may be)

Tried with success: -mocking custom SQL exception n successful recovery. -multithreaded approaches.

Now, need a SQL component which does this in a single session.

Edit: reframed the question for better suggestions.

CodePudding user response：

This is currently possible.

The following code deadlocks itself

BEGIN TRAN
CREATE TYPE dbo.OptionIDs AS TABLE( OptionID INT PRIMARY KEY )
EXEC ('DECLARE @OptionIDs dbo.OptionIDs;')
ROLLBACK

This is a long standing issue due to the use of internal system transactions when creating the instance of the TVP that can't access the lock taken by the user transaction.

At some point it may be fixed though