What conditions trigger a table level lock

Question

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'

This question may be more appropriate for [DBA.SE](http://dba.stackexchange.com/), but check their [FAQ](http://dba.stackexchange.com/faq) and previous questions before asking there. — mgorven, Mar 01 '13 at 18:01
This thread covers similar problem: http://dba.stackexchange.com/questions/21075/way-to-prevent-queries-from-waiting-for-table-level-lock — Meriadoc Brandybuck, Mar 01 '13 at 18:53

score 2 · Accepted Answer · answered Mar 01 '13 at 18:10

2

Is mysqldump running at the time of the locking? Looks like thread 83 might be currently exporting post but may have called LOCK TABLES on the DB to get a consistent position on all tables.

answered Mar 01 '13 at 18:10

smin

771
4
5

That's in interesting possibility. I don't have the captured events to tell for certain, but I see how that could be possible. – tylerl Mar 01 '13 at 20:01

What conditions trigger a table level lock

1 Answers1