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 …
MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server
Like Ovais said in Implications of Metadata Locking Changes in MySQL 5.5, the hot topic these days is MySQL 5.6, but there was an important metadata locking change in MySQL 5.5. As I began to dig into the Percona Toolkit bug he reported concerning this change apropos pt-online-schema-change, I discovered something about lock_wait_timeout …
[Read more]When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.
In fact, it’s the first thing I check when InnoDB tables are involved.
However, I just want to iterate how valuable SHOW FULL PROCESSLIST and/or mysqladmin debug outputs can be even when it seems mysqld is hung on on InnoDB table.
Two recent cases I’ve encountered illustrate why.
Case #1:
MySQL appeared hung on the following simple, single-row INSERT:
---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047, OS thread id 3069426592, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 ... INSERT INTO test (id, parent, text) VALUES (180370, 70122, 'test table')
At least that’s what it seemed per the INNODB STATUS, but unfortunately, there wasn’t any further information to go on.
The next time it occurred, SHOW FULL PROCESSLIST was captured at the time.
…
[Read more]