Why Simple MySQL Queries Stall: Unraveling Table Locks, Deadlocks, and Metadata Locks

Background

In multi‑user environments MySQL must keep data consistent while allowing high concurrency; most databases use locks and transactions to achieve this, but developers still encounter various locking issues.

2. Table‑lock induced slow query

A simple SELECT * FROM user WHERE id=6; on a MyISAM table with only three rows took 13 seconds because the query waited for a table lock.

Checking the process list shows the SELECT is blocked by a table lock, which is typically created by an UPDATE on a MyISAM table.

The user table uses the MyISAM engine, which creates a write lock for write operations and a read lock for reads. A write lock blocks all other operations, while a read lock allows concurrent reads but blocks writes.

Explicit lock control can be demonstrated with:

LOCK TABLE user READ;   -- allow concurrent reads
UNLOCK TABLES;

3. Online DDL and metadata locks

Altering a column length on the same user table caused the ALTER to wait for a metadata lock held by a SELECT.

From MySQL 5.6 onward, online DDL allows many DDL statements to run concurrently with DML. See the official documentation for supported operations.

Recommended steps for safe online schema changes include performing them during low‑traffic periods, verifying compatibility, and using tools such as pt-online-schema-change which copy data to a new table, capture changes via triggers, and then rename the table.

4. Deadlock analysis

A reproducible deadlock scenario on MySQL 5.7.20 (RR isolation) involves two transactions each locking a different row and then trying to update the other's row, leading InnoDB to roll back one transaction.

InnoDB reports the deadlock via SHOW ENGINE INNODB STATUS\\G, showing the waiting and holding locks for each transaction.

The engine uses a wait‑for‑graph algorithm to detect cycles and abort one transaction immediately, avoiding long wait times.

5. Lock‑wait analysis

Lock‑wait timeouts occur when a transaction holds a lock for too long; the waiting transaction receives error 1205 after innodb_lock_wait_timeout expires.

Monitoring tables information_schema.INNODB_LOCKS, INNODB_TRX, and INNODB_LOCK_WAITS helps identify waiting and blocking transactions.

Example query to list waiting and blocking transactions:

SELECT r.trx_id AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

6. Summary and recommendations

Avoid MyISAM tables; use InnoDB which provides row‑level locking.

If MyISAM is required, minimize write duration.

Prefer online DDL tools (e.g., pt-online-schema-change) for schema changes.

Implement application logic to retry transactions when a deadlock error occurs.

Monitor lock waits and deadlocks using InnoDB status and metadata tables.

References

姜承尧《InnoDB存储引擎》

李宏哲、杨挺《MySQL排查指南》

何登成 http://hedengcheng.com