I'd like to test a piece of software that detects deadlocks. There are several ways in which a deadlock can manifest in SQL Server. One of these is the 'worker thread deadlock'. Is there a simple way, via T-SQL, that could trigger such an event?
As per MSDN: A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result. For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. This results in a deadlock.
asked Dec 07 '09 at 11:29 AM in Default
For the deadlock notification I have turned on the trace flag(s) and I have a agent job that use a procedure to notify an operator tha a deadlock has occrus
answered Dec 07 '09 at 11:39 AM