Skip to main content

moregeek program

一步步搞懂mysql元数据锁(mdl)_京东云官方的博客-多极客编程

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

一步步搞懂MySQL元数据锁(MDL)_数据库

到底什么是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_connection
FROM
(
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
) t2
WHERE
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: 252
blocking_query: select * from t1
sql_kill_blocking_connection: KILL 27
1 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执行后忘记点击提交按钮,导致事务长时间无法提交。建议监控实例中的长事务,避免由于各种原因导致事务没有及时提交。


作者:翟振兴

©著作权归作者所有:来自51CTO博客作者京东云官方的原创作品,如需转载,请与作者联系,否则将追究法律责任

事务基础知识_大鱼的博客-多极客编程

一、数据库事务概述 1.1 存储引擎支持情况 SHOW ENGINES命令来查看当前MySQL支持的存储引擎都有哪些,以及这些存储引擎是否支持事务。 1.2 基本概念 事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。 事务处理的原则:保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),

mysql 中的锁机制_飞鱼的博客-多极客编程

介绍锁机制 技术是为了解决问题而生的,锁被用来实现隔离性,保证并发事务的正确性。 两段锁 & 一次封锁 两段锁 数据库遵循的是两段锁协议,将事务分成两个阶段,加锁阶段和解锁阶段(所以叫两段锁) 加锁阶段:在加锁阶段只能进行加锁操作。 如果事务要读取对象,必须先获得共享锁。可以有多个事务同时获得一个对象的共享锁 如果事务要修改对象,必须先获得独占锁。只能有一个事务获得对象的独占锁。如果

mysql--sql优化案例_mysql dba攻坚之路的博客-多极客编程

问题现象遇到的一个case,case发现某一个用户的某个操作总是超时,排查发现超时的原因是因为sql超时。sql大概长这样:select * from 表 where userid =123 and sign='T' and type='M' order by id desc limit 0,20,执行发现这个sql执行并返回竟然需要四十多秒。问题分析为什么一个sql会突然变得这么慢呢,其实第一个

mysql优化案例--limit优化_mysql dba攻坚之路的博客-多极客编程

案例SQL生产有这样一条sql,其中val是辅助索引select * from test where val=4 limit 300000,5;​查询原理查询过程:查询到索引叶子节点数据。根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。类似于下面这张图:像上面这样,需要查询300005次索引节点,查询300005次聚簇索引的数据,最后再将结果过滤掉前300000条,取出最后5条。MySQL

mysql--gtid是什么_mysql dba攻坚之路的博客-多极客编程

GTID作用主从环境中主库的dump线程可以直接通过GTID定位到需要发送的binary log的位置,而不需要指定binary log的文件名和位置,因而切换极为方便。GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf),是一个MySQL实例

mysql参数优化_mysql dba攻坚之路的博客-多极客编程

max_connections(1)简介Mysql的最大连接数,如果服务器的并发请求量比较大,可以调高这个值,当然这是要建立在机器能够支撑的情况下,因为如果连接数越来越多,mysql会为每个连接提供缓冲区,就会开销的越多的内存,所以需要适当的调整该值,不能随便去提高设值。(2)判断依据show variables like 'max_connections'; +--------------

一招教你如何高效批量导入与更新数据_华为云开发者社区的博客-多极客编程

摘要:如果你的数据量很大,想尽快完成任务执行,可否有其他方案?那一定不要错过GaussDB(DWS)的MERGE INTO功能。本文分享自华为云社区《​​一招教你如何高效批量导入与更新数据​​》,作者: acydy。当前GaussDB(DWS)提供了MERGE INTO功能。本篇文章介绍MERGE INTO功能与基本用法。前言如果有一张表,我们既想对它更新,又想对它插入应该如何操作? 可以使用UP

理解elasticsearch的post_filter_github.com/zq2599的博客-多极客编程

欢迎访问我的GitHub 这里分类和汇总了欣宸的全部原创(含配套源码):https://github.com/zq2599/blog_demos 本篇概览 《Elasticsearch 权威指南中,post_filter出现在聚合章节,描述post_filter的作用为:只过滤搜索结果,不过滤聚合结果; 描述比较简单,还是用实例来加深理解吧。 环境信息 以下是本次实例的环境信息,

事务基础知识_大鱼的博客-多极客编程

一、数据库事务概述 1.1 存储引擎支持情况 SHOW ENGINES命令来查看当前MySQL支持的存储引擎都有哪些,以及这些存储引擎是否支持事务。 1.2 基本概念 事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。 事务处理的原则:保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),

安装oracle 11gr2(x64) (静默安装)_雍州无名的博客-多极客编程

安装前准备:1.内存(RAM)的最少 2GB 及以上2.虚拟内存 swap 建议:内存为 1GB~2GB 时建议swap大小为内存大小的 1.5 倍;内存为 2GB~16GB 时建议swap大小为内存的大小;内存超过 16GB 时swap保持16GB3.要求临时文件目录,如 /tmp 的磁盘空间至少 1GB4.由于是静默安装,所以不需要图形界面5.修改系统时间为北京时间 cp /usr/shar

在shell脚本中直接使用命令修改账户密码_雍州无名的博客-多极客编程

在日常工作中,我们修改用户密码都使用 passwd user 这样的命令来修改密码,但是这样会进入交互模式,即使使用脚本也不能很方便的批量修改,除非使用expect 这样的软件来实现,难道修改一下密码还需要单独安装一个软件包吗? 不,我们其实还有其他很多方法可以让我们避开交互的,下面具体写一下具体的实现方式: 创建测试用户useradd -d /usr/local/testfiles -s /bi

elasticsearch聚合学习之五:排序结果不准的问题分析_github.com/zq2599的博客-多极客编程

欢迎访问我的GitHub 这里分类和汇总了欣宸的全部原创(含配套源码):https://github.com/zq2599/blog_demos 本篇概览 Elasticsearch上的索引如果有多个分片,那么在聚合排序后取TopN时,返回的结果可能是不准的,今天我们就通过实战来研究分析此问题,并验证解决方法; 环境信息 以下是本次实战的环境信息,请确保您的Elasticsearch可以