How to deal with MetaData Lock

Written by Pythian Marketing | May 5, 2016 4:00:00 AM

What is MetaData Lock?

MySQL uses metadata locking to manage concurrent access to database objects, and to ensure data consistency when performing modifications to the schema: DDL operations. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events). In this post I am going to cover metadata locks on tables and triggers, that are usually seen by DBAs during regular operations/maintenance. Kindly refer to these 4 different connections to MySQL Instance:

The screenshot shows that the uncommitted transaction may cause metadata lock to ALTER operations. The ALTER will not proceed until the transaction is committed or rolled-back. What is worse, after the ALTER is issued, any queries to that table (even simple SELECT queries) will be blocked. If the ALTER operation is an ONLINE DDL operation available in 5.6+, queries will proceed as soon as the ALTER begins. Refer to this video tutorial on MySQL Metadata Locks for further context. These days we have a “DBAs” favourite tool “pt-online-schema-change” (osc). Let's have a look what will happen If we run osc instead of ALTER.

OSC gets stuck at metadata lock at the point of creating triggers on table. Let's jump on the second topic how can we mitigate MDL issues:

Mitigating the MetaData Lock Issues

There are various solutions to tackling MDL:

Appropriate setting of wait_timeout variable which will kill stuck/sleep threads after a certain time.
Configure pt-kill to get rid of stuck/sleep threads
Fix code where transactions are not committed after performing DB queries

How to kill Sleep Connections in RDS which are causing MDL

If you are on RDS and your MySQL is having bunch of Sleep threads and you don’t know which connection is causing metadata lock, then you have to kill all the Sleep queries which are in mysql for more than a certain time. As we know “kill thread_id” is not permitted in RDS, but you can use the query below to get the exact queries to kill Sleep threads. Example Output:

mysql> SELECT CONCAT('CALL mysql.rds_kil ( ',id,')',';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Sleep' AND TIME > 10 ;
 +---------------------------------------------+
 | CONCAT('CALL mysql.rds_kill ( ',id,')',';') |
 +---------------------------------------------+
 | CALL mysql.rds_kill ( 5740758); |
 | CALL mysql.rds_kill ( 5740802); |
 | CALL mysql.rds_kill ( 5740745); |
 | CALL mysql.rds_kill ( 5740612); |
 | CALL mysql.rds_kill ( 5740824); |
 | CALL mysql.rds_kill ( 5740636); |
 | CALL mysql.rds_kill ( 5740793); |
 | CALL mysql.rds_kill ( 5740825); |
 | CALL mysql.rds_kill ( 5740796); |
 | CALL mysql.rds_kill ( 5740794); |
 | CALL mysql.rds_kill ( 5740759); |
 | CALL mysql.rds_kill ( 5740678); |
 | CALL mysql.rds_kill ( 5740688); |
 | CALL mysql.rds_kill ( 5740817); |
 | CALL mysql.rds_kill ( 5740735); |
 | CALL mysql.rds_kill ( 5740818); |
 | CALL mysql.rds_kill ( 5740831); |
 | CALL mysql.rds_kill ( 5740795); |
 | CALL mysql.rds_kill ( 4926163); |
 | CALL mysql.rds_kill ( 5740742); |
 | CALL mysql.rds_kill ( 5740797); |
 | CALL mysql.rds_kill ( 5740832); |
 | CALL mysql.rds_kill ( 5740751); |
 | CALL mysql.rds_kill ( 5740760); |
 | CALL mysql.rds_kill ( 5740752); |
 | CALL mysql.rds_kill ( 5740833); |
 | CALL mysql.rds_kill ( 5740753); |
 | CALL mysql.rds_kill ( 5740722); |
 | CALL mysql.rds_kill ( 5740723); |
 | CALL mysql.rds_kill ( 5740724); |
 | CALL mysql.rds_kill ( 5740772); |
 | CALL mysql.rds_kill ( 5740743); |
 | CALL mysql.rds_kill ( 5740744); |
 | CALL mysql.rds_kill ( 5740823); |
 | CALL mysql.rds_kill ( 5740761); |
 | CALL mysql.rds_kill ( 5740828); |
 | CALL mysql.rds_kill ( 5740762); |
 | CALL mysql.rds_kill ( 5740763); |
 | CALL mysql.rds_kill ( 5740764); |
 | CALL mysql.rds_kill ( 5740773); |
 | CALL mysql.rds_kill ( 5740769); |
 | CALL mysql.rds_kill ( 5740770); |
 | CALL mysql.rds_kill ( 5740771); |
 | CALL mysql.rds_kill ( 5740774); |
 | CALL mysql.rds_kill ( 5740784); |
 | CALL mysql.rds_kill ( 5740789); |
 | CALL mysql.rds_kill ( 5740790); |
 | CALL mysql.rds_kill ( 5740791); |
 | CALL mysql.rds_kill ( 5740799); |
 | CALL mysql.rds_kill ( 5740800); |
 | CALL mysql.rds_kill ( 5740801); |
 | CALL mysql.rds_kill ( 5740587); |
 | CALL mysql.rds_kill ( 5740660); |
 +---------------------------------------------+
 53 rows in set (0.02 sec)

Capture sql queries to kill Sleep threads

mysql -htest-server.us-west-2.rds.amazonaws.com. --skip-column-names -e 'SELECT CONCAT("CALL mysql.rds_kill ( ",id,")",";") FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND="Sleep" AND TIME > 10' > kill_sleep_threads.sql 2.Execute queries from mysql prompt

mysql -htest-server.us-west-2.rds.amazonaws.com.
 
 mysql> source kill_sleep_threads.sql

Improvements in MySQL 5.7 related to MDL

Generally, we would want to kill as few connections as possible. But the trouble with metadata locks prior to 5.7 is that there is no insight available into which threads are taking the metadata lock. In MySQL 5.7, there are several improvements in getting insight into metadata lock information. “ The Performance Schema now exposes metadata lock information:

Locks that have been granted (shows which sessions own which current metadata locks)
Locks that have been requested but not yet granted (shows which sessions are waiting for which metadata locks).
Lock requests that have been killed by the deadlock detector or timed out and are waiting for the requesting session's lock request to be discarded

This information enables you to understand metadata lock dependencies between sessions. You can see not only which lock a session is waiting for, but which session currently holds that lock. The Performance Schema now also exposes table lock information that shows which table handles the server has open, how they are locked, and by which sessions. ” To check who holds the metadata lock in MySQL 5.7, We have to enable global_instrumentation and wait/lock/metadata/sql/mdl. Below is the example to enable global_instrumentation and wait/lock/metadata/sql/mdl

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';
 
 Query OK, 0 rows affected (0.00 sec)
 
 Rows matched: 1 Changed: 0 Warnings: 0
 
 mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
 
 Query OK, 1 row affected (0.00 sec)
 
 Rows matched: 1 Changed: 1 Warnings: 0

Once global_instrumentation and wait/lock/metadata/sql/mdl are enable, below query will show the locks status on connections.

mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID();
 +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------------------------------+
 | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO |
 +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------------------------------+
 | TABLE | sbtest | sbtest1 | SHARED_READ | GRANTED | 29 | 4 | NULL |
 | GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 5 | alter table sbtest1 add key idx_pad(pad) |
 | SCHEMA | sbtest | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 5 | alter table sbtest1 add key idx_pad(pad) |
 | TABLE | sbtest | sbtest1 | SHARED_UPGRADABLE | GRANTED | 30 | 5 | alter table sbtest1 add key idx_pad(pad) |
 | TABLE | sbtest | sbtest1 | EXCLUSIVE | PENDING | 30 | 5 | alter table sbtest1 add key idx_pad(pad) |
 | TABLE | sbtest | sbtest1 | SHARED_READ | PENDING | 31 | 6 | select count(*) from sbtest1 |
 +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------------------------------+
 6 rows in set (0.00 sec)

Here PROCESSLIST_ID 4 is GRANTED and other PROCESSLIST_IDs are in PENDING state.

Conclusion

Best-practice when running any DDL operation, even with performance schema changes in 5.7, it to make sure to check processlist for presence of MDL waits, check SHOW ENGINE INNODB STATUS for long active transactions. Kill DDL operation while resolving the MDL issue so as to prevent query pileup. For a temporary fix implement pt-kill or wait_timeout. Review and fix application code/scripts for any uncommitted transactions to solve metadata lock issue.

MySQL Database Consulting

Looking to optimize your MySQL use?

View full post