Why does WAITFOR DELAY throw SQLCODE -307, SQLSTATE 40W06, All threads are blocked

[Breck_Carter]

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.

[johnsmirnios]

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 http://dcx.sybase.com/index.html#1201/en/dbusage/deadlock.html*d5e6131

[VolkerBarth]

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:)

[johnsmirnios]

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.

[VolkerBarth]

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:)