How to force a worker thread deadlock

Question

question

Robin asked Dec 7, '09

How to force a worker thread deadlock

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.

deadlock

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-07T11:39:08Z

sp_lock answered Dec 7, '09

CREATE TABLE tablea (i int)
CREATE TABLE tableb (i int);

INSERT tablea SELECT 1
INSERT tableb SELECT 101;


-- in first session
BEGIN TRAN
UPDATE tablea SET i = 11 WHERE i = 1
WAITFOR DELAY '00:00:10'
UPDATE tableb SET i = 99 WHERE i = 101

COMMIT

-- in second session
BEGIN TRAN
UPDATE tableb SET i = 99 WHERE i = 101
WAITFOR DELAY '00:00:10'
UPDATE tablea SET i = 11 WHERE i = 1

COMMIT

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

http://ronsi-simple-thing.blogspot.com/2007/10/how-to-get-mail-notification-on-sql.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question