I need to test my C# code in a SQL Server deadlock scenario. Is there a way to create a deadlock "on demand"?
I have tried this:
Begin Transaction
Select * from TABLEA with (tablockx, holdlock)
Where 0 = 1
WaitFor Delay '00:02:00'
--Rollback Transaction
But the second connection just waits and until after the Delay, and then does its job.
In the second session I do
Update TABLEA
Set LastUpdatedBy = 'IT_TESTING'
Where OrderRefProviderId = 397685
WaitFor Delay '00:00:30'