When Can MySQL Locks Block Queries? A Deep Dive into Table‑Level and Metadata Locks

In MySQL, locks are not limited to row‑level conflicts; under certain conditions even a simple SELECT can be blocked. The article examines why this happens and how to identify the root cause.

1. MySQL Table‑Level Lock Implementations

There are two layers of table‑level locking:

Server (SERVER) layer : manages metadata locks (MDL) that protect object definitions such as table structure and indexes.

Engine (ENGINE) layer : each storage engine implements its own strategy. MyISAM uses table‑level locks, while InnoDB provides row‑level locks and table‑level locks via intent locks.

MDL is controlled by the SERVER layer and prevents concurrent DDL changes, ensuring definition consistency.

Metadata Lock Types

When a table is accessed, MySQL acquires a metadata read lock . When the table definition is altered, a metadata write lock is taken. The locks are mutually exclusive for read/write and write/write, while read/read can coexist.

InnoDB Intent Locks

Intent locks coordinate requests for table‑level and row‑level locks:

Intention Shared (IS) : a transaction intends to acquire shared row locks.

Intention Exclusive (IX) : a transaction intends to acquire exclusive row locks.

2. Waiting for Table Metadata Lock

Unfinished read or write transactions hold MDL locks that can block other sessions.

Unfinished Read Transaction

Session 1 holds MDL_SHARED_READ (shared read lock). Session 2 attempts an ALTER statement, which requires MDL_EXCLUSIVE . The exclusive request is blocked, making the table unreadable and unwritable for other sessions.

Unfinished Write Transaction

Session 1 holds MDL_SHARED_WRITE (shared write lock). Session 2’s ALTER also needs MDL_EXCLUSIVE and is blocked, producing the same effect.

LOCK TABLES … READ/WRITE

LOCK TABLES explicitly acquires table locks. Example for a read lock:

lock tables db_version read;

This creates an MDL_SHARED_READ_ONLY lock, allowing other sessions only shared‑read locks. An ALTER from another session still requires MDL_EXCLUSIVE and will wait.

For a write lock:

lock tables db_version write;

The session obtains MDL_SHARED_NO_READ_WRITE , an exclusive lock that prevents any other session from acquiring any lock on the table, thus blocking all reads and writes.

3. FLUSH TABLES & Waiting for Table Flush

FLUSH TABLES closes all open tables and flushes caches. If a table is locked with LOCK TABLES , FLUSH TABLES is blocked, causing the table to become unreadable/unwritable.

When a session holds a read lock and another session issues ALTER , the ALTER must wait for an exclusive MDL, leading to a “Waiting for table flush” state visible in SHOW PROCESSLIST .

4. Diagnosing and Finding the Source SQL

To locate the blocking transaction, use the built‑in views in the sys schema:

sys.schema_table_lock_waits – shows metadata lock wait information.

information_schema.innodb_trx – lists long‑running uncommitted transactions.

Example query to list lock wait details:

SELECT
  b.PROCESSLIST_ID,
  b.THREAD_ID,
  a.OBJECT_NAME,
  a.LOCK_TYPE,
  a.LOCK_STATUS,
  b.PROCESSLIST_STATE
FROM performance_schema.metadata_locks a
LEFT JOIN performance_schema.threads b ON a.OWNER_THREAD_ID = b.THREAD_ID
WHERE a.OBJECT_SCHEMA = 'tmp';

To see recent statements of a specific thread:

SELECT THREAD_ID, event_id, sql_text
FROM performance_schema.events_statements_history
WHERE THREAD_ID = 14503
ORDER BY event_id;

5. Summary

Table objects become unreadable or unwritable in MySQL when:

Metadata lock contention (e.g., Waiting for table metadata lock ) blocks DDL or DML.

Flush‑related contention (e.g., Waiting for table flush ) caused by FLUSH TABLES or LOCK TABLES interactions.