How to force a worker thread deadlock

Question

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.

Answer 1

0

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

answered Dec 07 '09 at 11:39 AM

sp_lock
8.1k ● 20 ● 26 ● 29

This isn't a worker thread deadlock, it is a lock resource deadlock. A worker thread deadlock would be caused when a worker locks a resource and then sleeps. While it sleeps other workers require the locked resource and are blocked holding up the workers. Meanwhile the sleeping process can't wake up because there are no workers available to bind to. You'd be lucky to trigger this kind of deadlock manually at demand. It would be very hard to reproduce.

Dec 14 '09 at 06:31 PM Jonathan Kehayias

(comments are locked)

How to force a worker thread deadlock

Follow this question

Related Questions