Common MySQL Concurrency Issues: Table Locks, Metadata Locks, Deadlocks, and Lock Waits

Background: In multi‑user environments a database must keep high concurrency while guaranteeing data consistency. MySQL, like most databases, relies on locks and transactions; however, various concurrency issues still arise during development.

1. Table lock causing slow query

An example query select * from user where id=6; on a MyISAM table with only three rows took 13 seconds. The processlist showed the SELECT waiting for a table lock, which was held by an UPDATE operation. MyISAM creates a table‑level lock before any operation; a write lock blocks all other reads and writes, while a read lock allows concurrent reads but blocks writes. The article demonstrates explicit lock control with lock table user read; and unlock tables; in two sessions, showing that Session 1’s read lock permits Session 2 to read concurrently but blocks writes, and that releasing the lock allows the blocked write to proceed.

2. Risks of online schema changes

Attempting to enlarge a column length with alter table user modify name varchar(500); was blocked by a metadata lock held by a concurrent SELECT. MySQL 5.6+ provides online DDL that can run concurrently with DML for many operations. The article recommends using InnoDB (MyISAM is deprecated) and tools such as Percona’s pt-online-schema-change to perform online schema changes safely.

3. Deadlock analysis

A simple deadlock scenario on MySQL 5.7.20 (RR isolation) is reproduced: Transaction 1 updates row id=4, Transaction 2 updates row id=3, each holding a row lock that the other needs. InnoDB’s show engine innodb status\G output shows the two transactions, the locks they hold, and the locks they are waiting for. InnoDB detects the cycle via a wait‑for‑graph algorithm and rolls back Transaction 2, allowing Transaction 1 to continue.

4. Lock‑wait analysis

Lock‑wait occurs when a transaction holds a row lock for a long time and another transaction attempts a conflicting operation. The article shows a monitoring query on information_schema.innodb_lock_waits that returns the waiting and blocking transaction IDs, threads, and SQL statements. It advises monitoring lock‑waits, handling timeout errors (e.g., ERROR 1205 (HY000): Lock wait timeout exceeded ), and implementing retry or rollback logic in the application.

5. Summary

The article summarizes that MySQL concurrency issues include table locks (especially with MyISAM), metadata locks during DDL, row‑level deadlocks, and lock‑wait timeouts. It recommends using InnoDB, leveraging online DDL, monitoring InnoDB status and metadata tables, and applying appropriate error‑handling strategies to maintain performance and reliability.