I’ve recently faced problem of table being completely locked from read and write while trying to apply create index on a table for MySQL 5.7 database. The issue was caused by DDL falling into Waiting for table metadata lock state by unfinished transaction on the target table. This caused all subsequent DMLs on the table to also wait for metadata lock. Let’s investigate what a metadata lock is, how it affects DDL, and why it affects DDL.
Problem Statement
In an effort to address slow query performance, I decided to create an index on a MySQL table. Aware of the potential for creating an index to lock the entire table, I attempted an online Data Definition Language (DDL) to enable lock-free index creation. This approach is generally effective in avoiding disruptions during index creation by allowing concurrent modifications (reads and writes) on the table.
For demonstration purpose, let’s create a users table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
+---------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| registered_at | timestamp | YES | | CURRENT_TIMESTAMP | |
+---------------+--------------+------+-----+-------------------+----------------+Create index statement would look something like this
CREATE INDEX IDX__users__email on users (email) ALGORITHM=INPLACE, LOCK=NONE; However, shortly after executing the DDL statement, I encountered unexpected problems. The database connection timeout monitor began to issue alerts specifically for the table undergoing the index creation. Upon inspecting the database activity using the SHOW PROCESSLIST command, it was clear that the DDL operation was in a “Waiting for table metadata lock” state. Alarmingly, subsequent queries targeting the same table also entered a waiting state for the metadata lock causing client timeouts. This resulted in the table not being accessible until I finally killed the create index process.
This behavior is reproducible when running a DDL while ongoing transaction exists
| Transaction 1 | Transaction 2 |
| begin | |
| select * from users | |
| create index … |
processlist shows DDL waiting for table metadata lock. In this state, queries on user table from other sessions will also wait for metadata lock. Either the blocking transaction or the DDL itself must be killed for the users table to be accessible again.
mysql> show full processlist;
+----+------+------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------------------+
| 4 | root | 172.18.0.1:55950 | test | Query | 9 | Waiting for table metadata lock | CREATE INDEX IDX__users__email on users (email) ALGORITHM=INPLACE LOCK=NONE |
| 5 | root | 172.18.0.1:56694 | test | Sleep | 70 | | NULL |
| 7 | root | 172.18.0.1:47470 | test | Query | 0 | starting | show full processlist |
+----+------+------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------------------+
3 rows in set (0.01 sec)MySQL Online DDL
MySQL 5 introduced a significant enhancement to the InnoDB engine—the Online Data Definition Language (DDL) feature. This feature marks a pivotal advancement in how MySQL handles table alterations, particularly benefiting operations that modify table structure, like adding or dropping columns, or creating and deleting indexes.
Before the advent of online DDL, altering tables typically required locking the table or creating a full copy of it, which considerably hindered database performance and availability during the operation. The online DDL capabilities are designed to mitigate these disruptions by allowing table alterations to be performed “in-place” and enabling concurrent Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE.
A crucial aspect of leveraging online DDL effectively involves the use of the ALGORITHM and LOCK clauses in DDL statements. These clauses provide control over how table alterations are executed:
ALGORITHM Options
Determines how the DDL operation is processed, whether it’s INPLACE (modifying the table directly while allowing concurrent DML) or COPY (creating a temporary copy of the table, which can impact database performance). If not specified, MySQL will try to run INPLACE first and fallback to COPY if INPLACE is not applicable. If specified, MySQL will try to run using the algorithm, and fail with error if not applicable.
| Option | Description |
|---|---|
DEFAULT | MySQL automatically chooses the algorithm (either INPLACE or COPY). |
INPLACE | Attempts to perform the DDL operation directly on the table without copying it, allowing concurrent DML operations. This method minimizes the impact on database availability. |
COPY | Creates a temporary copy of the table. The original table is locked until the new table is ready, then the old table is replaced. This method can be disruptive as it locks the table during the operation. |
Refer to MySQL documentation on which algorithm is applicable for each DDL operations – https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
LOCK Options
Specifies the level of locking required during the DDL operation, with options ranging from NONE (no locks, allowing full concurrent DML) to EXCLUSIVE (full table lock). Similar to ALGORITHM option, MySQL by default will try to use little locking as possible. If the LOCK clause specifies a less restrictive level of locking than is permitted for a particular DDL operation, the statement fails with an error.
| Option | Description |
|---|---|
DEFAULT | MySQL automatically chooses the lock level based on the specific operation and table conditions. |
NONE | No locks are placed on the table, allowing other sessions to perform DML operations on the table concurrently with the DDL operation. |
SHARED | Permits other sessions to read from the table during a DDL operation but not perform any DML operations that modify it. |
EXCLUSIVE | Prevents all other sessions from performing both read and write operations on the table during the DDL operation. |
It is crucial to explicitly specify these options when performing DDL operations. Failing to do so may result in MySQL defaulting to an unintended algorithm or lock level, depending on the specific operation and table characteristics, potentially leading to unwanted locking behavior or performance issues.
Metadata Lock
MySQL implements metadata locking to manage concurrent access to database objects, ensuring data consistency and the integrity of operations. Metadata locks are a fundamental component of MySQL’s architecture that helps coordinate between multiple queries and prevent conflicts during execution.
performance_schema.metadata_locks Table
The information of metadata lock can be retrieved from metadata_locks of the Performance Schema. The table contains information on granted locks, requested locks, locks killed by deadlock detector, and lock requests that have timed out. To retrieve the table simply query like below.
select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | 281471554375280 | SHARED_READ | TRANSACTION | GRANTED | | 29 | 36 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+Refer to MySQL documentation on what each column represents – https://dev.mysql.com/doc/refman/5.7/en/performance-schema-metadata-locks-table.html
Going back to our demonstration example from the problem statement, the metadata_locks table would look like below while create index ddl is waiting for the metadata lock. As can be seen from the result, thread id 30 is in pending status for exclusive lock on users table. However, the lock won’t be granted as it is held by uncommitted transaction that has run select statement on the user table.
select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| TABLE | test | users | 281471352990752 | SHARED_READ | TRANSACTION | GRANTED | | 30 | 35 |
| GLOBAL | NULL | NULL | 281471554375280 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 29 | 38 |
| SCHEMA | test | NULL | 281471554310336 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 29 | 38 |
| TABLE | test | users | 281471554315984 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 29 | 38 |
| TABLE | test | users | 281471554355904 | EXCLUSIVE | TRANSACTION | PENDING | | 29 | 38 |
| TABLE | performance_schema | metadata_locks | 281471218765984 | SHARED_READ | TRANSACTION | GRANTED | | 35 | 3 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+``
select * from performance_schema.threads;
+-----------+----------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+-----------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+-----------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
| 29 | thread/sql/one_connection | FOREGROUND | 4 | root | 172.18.0.1 | test | Query | 656 | Waiting for table metadata lock | CREATE INDEX IDX__users__email on users (email) ALGORITHM=INPLACE LOCK=NONE | NULL | NULL | YES | YES | SSL/TLS | 194 |
| 30 | thread/sql/one_connection | FOREGROUND | 5 | root | 172.18.0.1 | test | Sleep | 659 | NULL | NULL | 1 | NULL | YES | YES | SSL/TLS | 195 |
| 35 | thread/sql/one_connection | FOREGROUND | 10 | root | 172.18.0.1 | NULL | Query | 0 | Sending data | select * from performance_schema.threads | NULL | NULL | YES | YES | SSL/TLS | 196 |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+-----------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+Metadata Lock Priority
When there are multiple waiters for a given lock, the highest priority lock request is satisfied first. The EXCLUSIVE lock type has higher priority than SHARED_READ type, thus any subsequent select queries will halt waiting for metadata lock while lock request with EXCLUSIVE lock type is in pending status. This was the exact reason why I was unable to read from my table.
# from another session
select * from users;
select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| TABLE | test | users | 281471352990752 | SHARED_READ | TRANSACTION | GRANTED | | 30 | 39 |
| GLOBAL | NULL | NULL | 281471554310336 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 29 | 39 |
| SCHEMA | test | NULL | 281471554315984 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 29 | 39 |
| TABLE | test | users | 281471554375280 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 29 | 39 |
| TABLE | test | users | 281471554355904 | EXCLUSIVE | TRANSACTION | PENDING | | 29 | 39 |
| TABLE | test | users | 281471218766688 | SHARED_READ | TRANSACTION | PENDING | | 35 | 24 |
| TABLE | performance_schema | metadata_locks | 281471285876000 | SHARED_READ | TRANSACTION | GRANTED | | 37 | 3 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+