This doesn't happen very often, but... why does it need another thread? why can't it use the one it already has (the one executing when it hits the WAITFOR DELAY)?

In this case, dbsrv12 is using -gn 120 -gna 0 which might not be too bright of me... perhaps -gnl 120 would be a better choice :)

Nevertheless, the question remains: what's with the extra thread?

PS yes, there is no doubt, it is the WAITFOR DELAY that throws the error.

asked 27 Nov '11, 15:35

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 27 Nov '11, 15:36

First, what build are you using? There was one build that had a problem with WAITFOR DELAY erroneously reporting thread deadlock but I don't think it ever went out.

Second, there is no "other thread" really. The error means that all other threads are currently blocked (on row locks, doing a WAITFOR, etc) and if this current thread went to sleep, there would be no threads available to execute actions that could wake up the other threads. See the "all workers are blocked" section of*d5e6131

permanent link

answered 28 Nov '11, 00:14

John%20Smirnios's gravatar image

John Smirnios
accept rate: 39%

Just to add from the WAITFOR documentation:

The implementation of the WAITFOR statement causes the worker servicing the statement to block while it is waiting. This reduces the number of available workers in the worker pool.

Personally, I had not expected WAITFOR to block (as it could also wait "actively" - with some kind of polling) but the behaviour is apparently

  1. better w.r.t. resource usage
  2. and fully documented:)
(28 Nov '11, 02:49) Volker Barth
Replies hidden

Polling wouldn't make the behaviour any better: there's nothing that thread could do if it woke up and before the timeout was over. Every request from a client uses up a request task until the request completes. That task maintains the state of the SQL that is being executed. We also can't service another request on that thread since that SQL might block too.

(28 Nov '11, 07:58) John Smirnios

Sorry, I didn't want to "know better" (and obviously, I couldn't...). Your reasoning makes perfect sense.

I had just thought of some kind of "home-brewn" polling without giving up the control, i.e. without sleeping (or being suspended) - of course a thread can use CPU resources and intermittedly check if enough time has passed... but that would be a complete waste of time and resources, and of course a system programmer won't do that:)

(28 Nov '11, 08:09) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 27 Nov '11, 15:35

question was seen: 1,100 times

last updated: 28 Nov '11, 08:09