With the introduction of metadata locking in MySQL 5.5, it is much more
common to see queries stuck in the “Waiting for table metadata
lock” state.
If you see a query stuck in the “Waiting for table metadata lock”
state, waiting for a MyISAM table, then it is because this table
has been included as part of some uncommitted transaction
(whether intentionally or not).
In fact, it could be as simple as issuing SET AUTOCOMMIT=0
followed by a SELECT against a MyISAM table (a transaction can be
started 3 ways: START TRANSACTION, BEGIN, and SET AUTOCOMMIT=0).
After that, the table will be locked to other DDL statements
until the transaction is committed (or ended).
This metadata lock is meant for DDL statements, and so that is
what it blocks. However, once a DDL statement is blocked and
waiting, then all queries …
One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.
The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).
Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):
------------------------ LATEST DETECTED DEADLOCK ------------------------ 111109 20:10:03 *** (1) TRANSACTION: TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1 MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating UPDATE parent SET age=age+1 WHERE id=1 *** (1) WAITING FOR THIS LOCK TO BE …[Read more]
This is another article in a series of articles titled "A few notes ..." in which I will be posting some important information about locking concepts, different types of locks and what locks table engines support. Just like the previous article, the purpose of this article is to highlight important aspects that you should have in the back of your mind when developing applications.