Understanding MySQL Metadata Locks and ALTER Table Delays with Foreign Keys

This article examines a case where an ALTER TABLE on a child table with foreign‑key references to a busy parent table stalled for hours due to MySQL metadata lock contention during load testing.

When an ALTER is issued, MySQL obtains a SHARED_UPGRADABLE lock on the child and a SHARED_READ_ONLY lock on the parent. Locks are granted based on priority: write locks outrank read locks, and the system satisfies the highest‑priority request first, as described in the MySQL documentation.

The article demonstrates the lock ordering with a simple example: three statements—INSERT into parent, ALTER child, INSERT into parent—are expected to execute in that order, but because the ALTER acquires a read lock on the parent, a later write transaction on the parent can preempt the ALTER, causing the ALTER to wait.

Sample schema and data initialization:

CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `val` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;

INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");

Three concurrent sessions illustrate the problem:

-- Session 1
START TRANSACTION;
UPDATE parent SET val = "four-new" WHERE id = 4;

-- Session 2
ALTER TABLE child ADD INDEX `idx_new` (val);

-- Session 3
START TRANSACTION;
UPDATE parent SET val = "three-new" WHERE id = 3;

Session 1 holds a write metadata lock on the parent, Session 2 holds an upgradeable lock on the child and waits for a read lock on the parent, and Session 3 acquires another write lock on the parent. The performance_schema.metadata_locks table shows that only Session 2’s ALTER remains pending.

The root cause is the default value of the system variable max_write_lock_count , which is set to an extremely high number (18446744073709551615). With such a high limit, the lock manager continues granting write locks before checking for pending read locks, causing the ALTER to be starved.

Reducing max_write_lock_count to a modest number (e.g., 10) forces the lock subsystem to pause after every few write locks, check for pending read locks, and grant them, allowing the ALTER to complete. This variable can be changed at runtime, providing a quick fix for the described deadlock scenario.

In summary, the article highlights how metadata lock priority can block ALTER statements on tables with foreign keys under heavy write load, and offers a practical solution by tuning max_write_lock_count to improve concurrency.