DrupalCon Baltimore: 161 sessions, many voices, infinite possibilities. Earlybird rate ends Friday.
I believe I watched this happen this morning, although don't have a reproducible test case yet.
process 1 - does a variable_set(), clears the cache process 2 - does another variable_set() with a different variable process 3 - gets a cache miss from the variables cache, the SELECT * is blocked on the SELECT ... FOR UPDATE from process 2 process 4 - does another variable_set() - continues to block process 2.
This is excerbated by the fact that process 3 can also acquire a lock before hitting the SELECT * - which means any further requests which get a variable cache miss are waiting on that one process before they can continue executing. If you have something else going on which is causing UPDATE queries to take a long time, and a few are issued at once, this situation could last for some time.
I don't see any reason why we have to use SELECT ... FOR UPDATE here instead of SELECT ... LOCK IN SHARE MODE, the latter issues exactly the same write lock on the row, without blocking reads, so in this case process 2's SELECT * would issue immediately.
I can only see one reference to IN SHARE MODE on the original issue that introduced this - http://drupal.org/node/715108#comment-3093918 and that wasn't discussed further. Attaching a patch which does that for MySQL here.