Working on some vBulletin performance issues, I ran into this situation, where everything is stuck waiting on a table-level lock:
Id Command Time State Info
83 Query 47 Writing to net SELECT /*!40001 SQL_NO_CACHE */ * FROM `post`
87 Query 117 Waiting for table level lock UPDATE session SET lastactivity = 1362132185, location = '/for
89 Query 116 Waiting for table level lock SELECT * FROM session WHERE userid = 0 AND host = '178.1
90 Query 113 Waiting for table level lock SELECT * FROM session WHERE userid = 0 AND host = '66.24
94 Query 108 Waiting for table level lock select userid from session where sessionhash = '2269de072969ab9d42
96 Query 102 Waiting for table level lock SELECT * FROM session WHERE sessionhash = 'b0e3d290e9f609160
129 Query 15 Waiting for table level lock SELECT * FROM session WHERE userid = 0 AND host = '65.55
130 Query 14 Waiting for table level lock SELECT * FROM session WHERE userid = 0 AND host = '71.19
132 Query 13 Waiting for table level lock SELECT * FROM session WHERE userid = 0 AND host = '178.1
Normally the pattern for diagnosing lock issues is to figure out which query isn't locked, and and generally there's your culprit. But in this case, it's reading an unrelated table, and the query that's been running the longest (which is certainly part of the problem since it's the only update query) is ALSO locked.
So the question is, what additional conditions can cause a table level lock to be applied that you might expect from a situation like seen here.
Add'l Details
This is about a standard mysql install; no partitioning or other shenanigans involved
Table posts
is type MyISAM
Table session
is type MEMORY
Other issues (such as the glaring inefficiency of query 83 or the inadvisability of using MyISAM) are interesting, but not what is being asked.
The full text of query 87 looks like this:
Query UPDATE session SET lastactivity = 1362132185, location = '/forums/forumdisplay.php?f=421', inforum = 421, inthread = 0, incalendar = 0, badlocation = 0 WHERE sessionhash = 'e6322935fe2df18106878473f310d91f'