某日,路上收到用户征询,为了革除空间,想删除某200多G大表数据,且曾经确认此表不再有业务拜访,于是执行了一条命令‘delete from bigtable’,但好长时间也没删完,通过征询后,获知drop table删除表速度快,而且能彻底开释空间,于是又在另外一个session中执行了‘drop table bigtable’命令,然而这个命令并没有疾速返回后果,光标始终hang在原地不动。最初找咱们帮助,在登录数据库执行‘show processlist’后发现drop语句的状态是‘waiting for table metadata lock’,而之前执行的另外一个delete语句仍旧能看到,状态为‘updating’,截图如下:

到底什么是metadata lock?这个锁期待是如何产生的?会带来什么影响?最初又如何来解决?明天咱们挑6个常见问题给大家解答一下。

一、什么是metadata lock?

在MySQL5.5.3之前,有一个驰名的bug#989,大抵如下:

 session1:   BEGIN; INSERT INTO t ... ; COMMIT;   session2:   DROP TABLE t;

然而下面的操作流程在binlog记录的程序是

 DROP TABLE t;  BEGIN;   INSERT INTO t ... ;  COMMIT;

很显然备库执行binlog时会先删除表t,而后执行insert 会报1032 error,导致复制中断。为了解决该bug,MySQL 在5.5.3引入了MDL锁(metadata lock),来爱护表的元数据信息,用于解决或者保障DDL操作与DML操作之间的一致性。

再举一个简略的例子,如果你在查问一个表的过程中,另外一个session对该表删除了一个列,那后面的查问到底该显示什么呢?如果在RR隔离级别下,事物中再次执行雷同的语句还会和之前后果统一吗?为了避免这种状况,表查问开始MySQL会在表上加一个锁,来避免被别的session批改了表定义,这个锁就叫‘metadata lock’,简称MDL,翻译成中文也叫‘元数据锁’。

二、MDL和行锁有什么区别?

metadata lock是表级锁,是在server层加的,实用于所有存储引擎。所有的dml操作都会在表上加一个metadata读锁;所有的ddl操作都会在表上加一个metadata写锁。读锁和写锁的阻塞关系如下:

读锁和写锁之间互相阻塞,即同一个表上的dml和ddl之间相互阻塞。
写锁和写锁之间相互阻塞,即两个session不能对表同时做表定义变更,须要串行操作。
读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞,能够并发执行,日常工作中大家看到的dml之间的锁期待是innodb行锁引起的,和metadata lock无关。

相熟innodb行锁的同学这里可能有点困惑,因为行锁分类和metadata lock很相似,也次要分为读锁和写锁,或者叫共享锁和排他锁,读写锁之间阻塞关系也统一。二者最重要的区别一个是表锁,一个是行锁,且行锁中的读写操作对应在metadata lock中都属于读锁。

大家兴许会奇怪,以前据说一般查问不加锁的,怎么这里又说要加表锁,咱们做一个简略测试:

session1:查问前,先看一下metadata_locks表,这个表位于performance_schema下,记录了metadata lock的加锁信息。

mysql> select * from performance_schema.metadata_locks ;+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| TABLE       | performance_schema | metadata_locks | NULL        |       139776223308432 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              54 |             12 |+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+1 row in set (0.00 sec)

session2:执行简略查问,为了让表处于执行状态,这里应用了sleep函数。

mysql> select sleep(10) from t1;+-----------+| sleep(10) |+-----------+|         0 ||         0 ||         0 |+-----------+3 rows in set (30.00 sec)

session1:

mysql> select * from performance_schema.metadata_locks ;+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+| TABLE       | db1                | t1             | NULL        |       139776154308336 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              53 |             22 || TABLE       | performance_schema | metadata_locks | NULL        |       139776223308432 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6014 |              54 |             13 |+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+2 rows in set (0.00 sec)

此时再次查看metadata_lock表,发现多了一条t1的加锁记录,加锁类型为SHARED_READ,且状态是已授予(GRANTED)。大家通常了解的查问不加锁,是指不在表上加innodb行锁。

如果在执行sleep期间,另外一个session执行了一个加字段操作,此时就会产生metadata lock锁期待:

session2:

mysql> select sleep(10) from t1;

执行中......

session3:

mysql> alter table t1 add col1 int;

阻塞中......

session1:

mysql> show processlist;+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+| Id | User            | Host      | db   | Command | Time   | State                           | Info                        |+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+|  4 | event_scheduler | localhost | NULL | Daemon  | 861577 | Waiting on empty queue          | NULL                        || 18 | root            | localhost | db1  | Sleep   |     50 |                                 | NULL                        || 19 | root            | localhost | NULL | Query   |      0 | starting                        | show processlist            || 20 | root            | localhost | db1  | Query   |     11 | Waiting for table metadata lock | alter table t1 add col1 int |+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+4 rows in set (0.00 sec)

显然,id为20的线程还未执行alter操作,状态为‘Waiting for table metadata lock’,也就是在期待session2的sleep操作实现。

三、MDL为什么会造成零碎解体?

举一个简略例子:

  • session1启动一个事务,对表t1执行一个简略的查问;
  • session2对t1加一个字段;
  • session3来对t1做一个查问;
  • session4来对t1做一个update;
  • 各个session串行操作。

session1:

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t1 where id=1;+----+------+------+-------+| id | name | age  | birth |+----+------+------+-------+|  1 | aa   |   10 | NULL  |+----+------+------+-------+1 row in set (0.00 sec)

session2:

mysql> alter table t1 add col1 int;

阻塞中...

session3:

mysql> select sleep(10) from t1 ;

阻塞中...

session4:

mysql> update t1 set name='aaaa' where id=2;

阻塞中...

也就是因为session1的一个事务没有提交,导致session2的ddl操作被阻塞,session3和session4自身不会被session1阻塞,但因为在锁队列中,session2排队更早,它筹备加的是metadata lock写锁,阻塞了session3和session4的读锁。如果t1是一个执行频繁的表,show processlist会发现大量‘waiting for table metadata lock’的线程,数据库连贯很快就会耗费完,导致业务零碎无奈失常响应。

此时如果session1提交,是session2的alter语句先执行还是session3和session4先执行呢?之前始终认为先到的先执行,当然是session2先执行,但通过测试,在5.7中,session3和session4先执行,session2最初执行,也就会呈现alter长时间无奈执行的状况;而在8.0中,session2先执行,session3和session4后执行,因为5.6当前ddl是online的,session2并不会阻塞session3和session4,感觉这样才是正当的,alter不会被‘饿死’。

四、MDL的生命周期有多长?

事务!事务!事务! 重要的事件说三遍,表上的metadata lock的生命周期从事务中的第一条波及本身的语句开始,到整个事务完结而完结。而5.5之前是基于语句的,事务中执行完语句就开释,如果此时另外一个session对表做了一个删字段操作,那么就会造成两个问题:

  • ddl操作如果先于事务实现,那么binlog中ddl就会排在事务之前,显著和逻辑不符,触发了本文开始提到的bug。
  • 如果是RR隔离级别,那么事务中此表第二次执行将无奈返回同样的后果,无奈满足可反复读的要求。

所以,如果要升高metadata lock的锁等待时间,最好要及时提交事务,同时尽量避免大事务。

那么如果产生metadata lock锁期待,期待锁的session会期待多长时间呢?大家都晓得MySQL外面行锁期待有个超时工夫(参数innodb_lock_wait_timeout),默认50s。metadata lock也有相似参数管制:

mysql> show variables like 'lock_wait_timeout'      ;+-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| lock_wait_timeout | 31536000 |+-------------------+----------+1 row in set (0.00 sec)

这么长的数字,掰着指头算了半天,竟然真的是......一年,环游世界一圈回来还得接着等!!!

当然,生产环境中,咱们很少会期待metadata lock超时,更多的是要想方法把产生metadata lock的源头找到,疾速提交或者回滚,或者想方法kill掉。那么如何找到阻塞的源头呢?

五、如何疾速找到阻塞源头?

疾速解决问题永远是第一位的,一旦呈现长时间的metadata lock,尤其是在拜访频繁的业务表上产生,通常会导致表无法访问,读写全被阻塞,此时找到阻塞源头是第一位的。这里最重要的表就是后面提到过的
performance_schema.metadata_locks表。

metadata_locks是5.7中被引入,记录了metadata lock的相干信息,包含持有对象、类型、状态等信息。但5.7默认设置是敞开的(8.0默认关上),须要通过上面命令关上设置:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';

如果要永恒失效,须要在配置文件中退出如下内容:

[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

单纯查问这个表无奈得出具体的阻塞关系,也无奈得悉什么语句造成的阻塞,这里要关联另外两个表performance_schema.thread和
performance_schema.events_statements_history,thread表能够将线程id和show processlist中id关联,events_statements_history表能够失去事务的历史sql,关联后的残缺sql如下:

SELECT    locked_schema,    locked_table,    locked_type,    waiting_processlist_id,    waiting_age,    waiting_query,    waiting_state,    blocking_processlist_id,    blocking_age,    substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,    sql_kill_blocking_connectionFROM    (        SELECT            b.OWNER_THREAD_ID AS granted_thread_id,            a.OBJECT_SCHEMA AS locked_schema,            a.OBJECT_NAME AS locked_table,            "Metadata Lock" AS locked_type,            c.PROCESSLIST_ID AS waiting_processlist_id,            c.PROCESSLIST_TIME AS waiting_age,            c.PROCESSLIST_INFO AS waiting_query,            c.PROCESSLIST_STATE AS waiting_state,            d.PROCESSLIST_ID AS blocking_processlist_id,            d.PROCESSLIST_TIME AS blocking_age,            d.PROCESSLIST_INFO AS blocking_query,            concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection        FROM            performance_schema.metadata_locks a        JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA        AND a.OBJECT_NAME = b.OBJECT_NAME        AND a.lock_status = 'PENDING'        AND b.lock_status = 'GRANTED'        AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID        AND a.lock_type = 'EXCLUSIVE'        JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID        JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID    ) t1,    (        SELECT            thread_id,            group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text        FROM           performance_schema.events_statements_history        GROUP BY thread_id    ) t2WHERE    t1.granted_thread_id = t2.thread_id \G

对于后面的例子执行此sql,失去一个清晰的阻塞关系:

 locked_schema: db1  locked_table: t1   locked_type: Metadata Lock      waiting_processlist_id: 28   waiting_age: 227 waiting_query: alter table t1 add cl3 int waiting_state: Waiting for table metadata lock     blocking_processlist_id: 27  blocking_age: 252blocking_query: select * from t1sql_kill_blocking_connection: KILL 271 row in set, 1 warning (0.00 sec)

依据显示后果,processlist_id为27的线程阻塞了28的线程,咱们须要kill 27即可解锁。

实际上,MySQL也提供了一个相似的视图来解决metadata lock问题,视图名称为sys.schema_table_lock_waits,但此视图查问后果有bug,不是很精确,倡议大家还是参考下面sql。

六、本文开始的案例最终如何解决?

通过后面的介绍,本文开始的案例产生的过程就很简略了:用户执行了一个全表delete,在指标表上加了metadata读锁,因为表很大,读锁长时间无奈开释,起初另外一个session执行了drop table操作,又须要在表上加metadata写锁,因为读写锁相互阻塞,drop操作只能期待delete操作实现能力取得写锁,因而从外表来看,二个命令都长时间没有响应,其实外部一个在执行,一个在期待。

那怎么来解决呢?因为从show processlist以及客户形容能够很分明的晓得故障机制,过后倡议客户将delete操作kill掉,等数据回滚完后再执行drop操作因为delete曾经执行了一段时间,回滚过程可能会较长,客户最终kill delete后顺利drop胜利。

小结

生产环境大多是dml操作,metadata读锁之间不会产生锁期待,而目前MySQL的ddl操作大多能够online执行,因而即便有写锁,也会很快降级为读锁,所以ddl执行期间阻塞dml的几率也很小。最容易呈现的状况是因为有未实现的事务,导致ddl metadata 写锁无奈加上,只能在锁队列期待,而一旦进入锁队列,写锁又会阻塞其余的读锁,导致数据库连贯快速增长,直至耗费殆尽,最终业务受到影响。

为了尽可能防止相似问题,上面是几个小倡议:

  • 生产环境的任何大表或频繁操作的小表,ddl都要十分谨慎,最好在业务低峰期执行。
  • 设计上要尽可能防止大事务,大事务不仅仅会带来各种锁问题,还好引起复制提早/回滚空间爆满等各类问题。
  • 要及时提交事务,常常发现客户端设置了事务手工提交,但sql执行后遗记点击提交按钮,导致事务长时间无奈提交。倡议监控实例中的长事务,防止因为各种起因导致事务没有及时提交。

作者:翟振兴