Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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'
share|improve this question
    
This question may be more appropriate for DBA.SE, but check their FAQ and previous questions before asking there. –  mgorven Mar 1 '13 at 18:01
    
This thread covers similar problem: dba.stackexchange.com/questions/21075/… –  Meriadoc Brandybuck Mar 1 '13 at 18:53

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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 1 '13 at 20:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.