This article walks through the background, reproduction steps, investigation methods, and concrete solution for MySQL metadata lock (MDL) issues that cause DDL statements to hang, and provides practical tips to prevent similar lock problems in production environments.
This article explains how MySQL table‑level locks and metadata locks (MDL) can block both reads and writes, describes the two lock implementations, their types, the interaction with LOCK TABLES and FLUSH TABLES, and provides practical queries for diagnosing lock‑related blocking issues.
This article explains how to identify when a MySQL DDL operation is blocked, locate the blocking session using the sys.schema_table_lock_waits view, and resolve the issue by enabling necessary instruments and killing the appropriate connection, with examples for MySQL 5.7 and 8.0.
Learn how to identify when a MySQL DDL statement is blocked, use SHOW PROCESSLIST and the sys.schema_table_lock_waits view to pinpoint the blocking session, and apply targeted KILL commands or transaction queries to safely unblock operations in both MySQL 5.7 and 8.0.
After upgrading a MySQL instance from 5.6 to 5.7, long‑running INSERT statements stalled due to MDL (metadata lock) contention and the default derived_merge optimizer setting, and the issue was resolved by disabling derived_merge or rewriting the SQL.
The article explains MySQL's metadata lock (MDL) mechanism, shows how to enable its instrumentation, demonstrates various lock scenarios with SELECT, INSERT and ALTER statements, and provides guidance on diagnosing and resolving MDL‑related blocking issues.
The article investigates how changing the operating system clock while MySQL is running influences functions such as SLEEP, metadata lock (MDL) wait calculations, and other timing‑related mechanisms, demonstrating that forward or backward adjustments can cause premature exits or inaccurate wait measurements.
This article examines MySQL’s locking mechanisms—including shared and exclusive row locks, gap and next‑key locks, table‑level and metadata locks—detailing how different SQL statements and index types affect lock acquisition, illustrating lock behavior with examples, and offering practical strategies to avoid deadlocks.
This article explains how to use MySQL's SHOW FULL PROCESSLIST command, interpret its output, kill problematic threads, and address metadata lock issues by inspecting InnoDB transactions and adjusting lock_wait_timeout, providing practical code examples for database administrators.
This article explains MySQL's metadata lock mechanism and demonstrates how to use the ext.check.get_locks() function from the MySQL Shell plugin—along with a supporting SQL query—to list lock owners, statements, and lock types, including setup steps for MySQL 5.7.
This article provides a comprehensive overview of MySQL's lock architecture, covering the two‑layer server/storage engine design, metadata (MDL) locks, InnoDB row and gap locks, transaction isolation levels, and practical debugging techniques using source‑code entry points and real‑world examples.
This article explains MySQL's architecture, details the various metadata lock types, their acquisition and release processes, lock levels, and relationships, and then examines InnoDB's transaction isolation levels, lock types, lock levels, gap locks, and relevant source‑code structures.
This article explains MySQL's Metadata Lock (MDL) subsystem, describing how MDL protects metadata, the lock types and modes, the transaction‑level implementation introduced in MySQL 5.5 and refined in 5.7, the lock acquisition and release algorithms, deadlock detection mechanisms, and the lock‑free hash table used to store lock objects.
This article details the evolution of MySQL's Metadata Lock subsystem, the implementation of lock‑free hash tables and fast‑path locking in MySQL 5.7, the hash_scan algorithm used in replication, as well as performance enhancements in TokuDB 7.5.0 and MariaDB's small‑LIMIT filesort optimization.
This article explains the evolution of MySQL's metadata lock (MDL) subsystem, the introduction of lock‑free hash tables and fast‑path locking in 5.7, the hash_scan replication optimization, TokuDB 7.5.0 enhancements, and MariaDB's small‑LIMIT filesort improvement, providing deep insight into modern database performance tuning.
This article explains why ALTER TABLE statements can become blocked by MySQL’s metadata locking, describes the underlying mechanisms, outlines common scenarios that cause “Waiting for table metadata lock,” and offers practical steps to diagnose and avoid such lock contention.
