备案控制台
探索云世界
开发者社区 数据库 文章 正文

metadata lock的解决方案

2017-09-03 4518
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： Metadata-Lock的引入是为了在并发条件下，防止session1的查询事务未结束的情况下，session2对表结构进行修改，以保护元数据的一致性。

文章
https://yq.aliyun.com/articles/175039?spm=5176.100239.0.0.jNFOsC 提出了会发生Metadata-lock场景以及如何在数据库运维方面避免产生的建议，但是其实5.6/5.7版本已经提供了一种直接定位该问题的方法：启用performance_schema功能，5.7更是针对Metadata-lock，提供了表来定位SQL。
再来回顾一下：
Metadata-Lock的引入是为了在并发条件下，防止session1的查询事务未结束的情况下，session2对表结构进行修改，以保护元数据的一致性。在session1持有 metadata-lock的情况下，session2处于等待状态：Waiting for table metadata lock
image

(1)sesion1:S1 S3; session2:S2

(2)在没有metadata-lock 锁的情况下，session2在session1执行过程中对元数据进行了修改，将导致session1 两次返回结果集结构不同

哪些操作会获取metadata-lock?

  • 表结构的更改（alter ）
  • 创建删除索引
  • 删除表
  • 获取表上表级写锁（lock table tab_name write）

哪些操作会引起其他事务获取metadata-lock

  • 慢查询
  • 显示或者隐式的开启事务后未提交或回滚：begin开始的事务没有及时提交
  • 表上有失败的查询事务（？）
### session1
root@10.20.200.190:working 03:05:30> begin;
Query OK, 0 rows affected (0.00 sec)

root@10.20.200.190:working 03:05:33> select * from test;
+------+---------------------+
| id   | gmt_modified        |
+------+---------------------+
|    1 | 2015-04-01 00:00:00 |
|    1 | 2015-04-02 00:00:00 |
|    1 | 2015-03-02 00:00:00 |
|    1 | 2015-03-05 00:00:00 |
|    1 | 2015-02-05 00:00:00 |
|    1 | 2014-02-05 00:00:00 |
+------+---------------------+
6 rows in set (0.02 sec)
### session2
root@localhost:working 03:05:20> alter table test change id id int(20);
root@localhost:performance_schema 03:04:52> show processlist;
+-------+----------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------+-----------+---------------+
| Id    | User     | Host                 | db                 | Command     | Time  | State                                                         | Info                                  | Rows_sent | Rows_examined |
+-------+----------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------+-----------+---------------+                            |         0 |             0 |
| 18225 | root     | localhost            | performance_schema | Query       |     0 | starting                                                      | show processlist                      |         0 |             0 |
| 20981 | root     | localhost            | working            | Query       |  1003 | Waiting for table metadata lock                               | alter table test change id id int(20) |         0 |             0 |                                                              | NULL                                  |         0 |             0 |
| 25323 | root     | 10.20.200.170:63407  | working            | Sleep       |  1087 |                                                               | NULL                                  |         0 |             0 |
+-------+----------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------+-----------+---------------+
3 rows in set (0.00 sec)

怎么处理 metadata_lock?

(1)找到并杀掉引起medatdata_lock的process id(不能精确定位)

select concat('kill ',id,';') from information_schema.processlist where time> (select time from information_schema.processlist where state = 'Waiting for table metadata lock') and db=(select db from information_schema.processlist where state = 'Waiting for table metadata lock') and user = 'root';

也可以通过show engine innodb status查询到持锁的会话信息，例如：
mysql -uroot -p* -hxxx -P3309 -e "show engine innodb status G;"|grep cleaning

(2)5.6.6之后提供了performance_schema性能库，用户监测数据库性能

mysql> select a.SQL_TEXT,a.CURRENT_SCHEMA,b.PROCESSLIST_USER,b.PROCESSLIST_HOST,b.PROCESSLIST_TIME,b.PROCESSLIST_ID from performance_schema.events_statements_current a join performance_schema.threads b on a.THREAD_ID = b.THREAD_ID where b.PROCESSLIST_TIME > 10\G
*************************** 1. row ***************************
        SQL_TEXT: update t1 set name = 'c' where id = 1
  CURRENT_SCHEMA: test
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_TIME: 390
  PROCESSLIST_ID: 38
1 row in set (0.00 sec)

(3)因为无法获取引起metadata_lock的具体SQL内容，所以要求开发人员：

  • 提交逻辑简单的SQL
  • 及时关闭事务
  • DBA及时发现并优化慢查询SQL
  • 对频繁执行且更改不频繁的数据做缓存

(4)MySQL5.7 新增performance_schema
5.7新增动态性能视图，记录数据库运行状态。
该功能以插件的形式存在于数据库，需要在编译的时候添加参数或者运行时打开该功能。

配置文件设置：

[mysqld]
performance_schema=ON
performance-schema-consumer-*consumer_name*=*value*
### *consumer_name* 可以在 setup_consumers 中找到

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+---------+
| Variable_name                                          | Value   |
+--------------------------------------------------------+---------+
| performance_schema                                     | ON      |
| performance_schema_accounts_size                       | 100     |
| performance_schema_digests_size                        | 200     |
| performance_schema_events_stages_history_long_size     | 10000   |
| performance_schema_events_stages_history_size          | 10      |
| performance_schema_events_statements_history_long_size | 10000   |
| performance_schema_events_statements_history_size      | 10      |
| performance_schema_events_waits_history_long_size      | 10000   |
| performance_schema_events_waits_history_size           | 10      |
| performance_schema_hosts_size                          | 100     |
| performance_schema_max_cond_classes                    | 80      |
| performance_schema_max_cond_instances                  | 1000    |
...
###如：
[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

编译时开启：

shell> cmake . -DWITH_PERFSCHEMA_STORAGE_ENGINE=1
###或者只使用部分功能
shell> cmake . -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
        -DDISABLE_PSI_STAGE=1 \
        -DDISABLE_PSI_STATEMENT=1
        
###验证是否安装成功
shell> mysqld --verbose --help
...
  --performance_schema
                      Enable the performance schema.
  --performance_schema_events_waits_history_long_size=#
                      Number of rows in events_waits_history_long.
...
###查看engine performance_schema是否安装成功
mysql> SHOW ENGINES\G
...
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
...

运行时开启：

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'performance_schema'
       AND TABLE_NAME LIKE 'setup%';
+-------------------+
| TABLE_NAME        |
+-------------------+
| setup_actors      |
| setup_consumers   |
| setup_instruments |
| setup_objects     |
| setup_timers      |
+-------------------+
###可以使用update 对上述表进行设置

以下是为了收集metadata_lock 的配置，配置之后需要重启！！！

[mysqld]
performance_schema=1
performance-schema-instrument='wait/lock/metadata/sql/mdl=YES'
performance-schema-consumer-global_instrumentation=YES

mysql> update  performance_schema.setup_consumers set ENABLED = 'NO' ;
mysql> update performance_schema.setup_consumers set ENABLED = 'YES' where name in ('global_instrumentation');

mysql> update performance_schema.setup_instruments set ENABLED = 'NO',TIMED='NO' ;
mysql> update performance_schema.setup_instruments set ENABLED = 'YES',TIMED='YES' where name = 'wait/lock/metadata/sql/mdl';

重启之后，需要等待一段时间，等数据库收集完信息后会在 setup_* 表中查看到相关的信息

###session1
root@localhost:(none) 07:17:46> begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost:(none) 07:17:48> select * from working.test;
+------+---------------------+
| id   | gmt_modified        |
+------+---------------------+
|    1 | 2015-04-01 00:00:00 |
|    1 | 2015-04-02 00:00:00 |
|    1 | 2015-03-02 00:00:00 |
|    1 | 2015-03-05 00:00:00 |
|    1 | 2015-02-05 00:00:00 |
|    1 | 2014-02-05 00:00:00 |
+------+---------------------+
6 rows in set (0.00 sec)

root@localhost:(none) 07:17:50> 

###session2
root@localhost:(none) 07:14:10> 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 |       140320629563808 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           57552 |              4 |
| TABLE       | working            | test           |       140320558091968 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           49983 |              8 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)

root@localhost:(none) 07:18:06> 

###session3
root@localhost:working 07:19:45> alter table test change id id varchar(20);

###session2
oot@localhost:(none) 07:18:06> 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 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140320582895264 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5559  |           58161 |              7 |
| SCHEMA      | working            | NULL           |       140320582895504 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5544  |           58161 |              7 |
| TABLE       | working            | test           |       140320582895664 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           58161 |              7 |
| BACKUP      | NULL               | NULL           |       140320582895744 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | lock.cc:1382      |           58161 |              7 |
| TABLE       | working            | test           |       140320582896144 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:3927       |           58161 |              7 |
| TABLE       | performance_schema | metadata_locks |       140320629563808 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           57552 |              5 |
| TABLE       | working            | test           |       140320558091968 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           49983 |              8 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
7 rows in set (0.00 sec)

root@localhost:(none) 07:20:12> show processlist;
+-------+-------------+---------------------+--------------------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+
| Id    | User        | Host                | db                 | Command     | Time  | State                                                         | Info                                      | Rows_sent | Rows_examined |
+-------+-------------+---------------------+--------------------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+
| 49946 | root        | localhost           | NULL               | Sleep       |   171 |                                                               | NULL                                      |         6 |             6 |
| 57515 | root        | localhost           | NULL               | Query       |     0 | starting                                                      | show processlist                          |         0 |             0 |
| 58124 | root        | localhost           | working            | Query       |    49 | Waiting for table metadata lock                               | alter table test change id id varchar(20) |         0 |             0 |
+-------+-------------+---------------------+--------------------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+
8 rows in set (0.00 sec)

以上为通过性能视图查询到的元数据锁信息，找到之后需要尽快释放锁，使用以下SQL，快速定位引起锁的SQL

select concat('kill ',b.PROCESSLIST_ID,';') from (select OWNER_THREAD_ID from performance_schema.metadata_locks where OBJECT_SCHEMA = '' and OBJECT_NAME = '' group by LOCK_STATUS having count(*) = 1 )a join performance_schema.threads b on a.OWNER_THREAD_ID = b.THREAD_ID ;

元数据锁是为了保证查询的一致性而加的，并无坏处；但是锁的存在影响了并发的性能；SQL上线前需要严格审计，做足预防措施；线上触发元数据锁之后，尽快定位问题，杀掉问题session。

打开performance_schema，对性能的影响还需要测试

文章标签：
云数据库 RDS MySQL 版
关系型数据库
数据库
SQL
MySQL
Shell
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
笱局长
目录
相关文章
算精通
|
3月前
|
SQL 监控 关系型数据库
MySQL Metadata Locking（MDL）机制的实现与获取机制分析
MySQL Metadata Locking（MDL）机制的实现与获取机制分析 为了满足数据库在并发请求下的事务隔离性和一致性要求，同时针对MySQL插件式多种存储引擎都能发挥作用，MySQL在Server层实现了 Metadata Locking（MDL）机制。这种机制可以灵活自定义锁的对象、锁的类型以及不同锁类型的优先级，甚至可以做到在系统不同状态时动态调整不同锁类型的兼容性。本篇文章将详细介绍MDL系统中的常用数据结构及含义，从实现角度讨论MDL的获取机制与死锁检测，以及在实践中如何监控MDL状态。
算精通
36 2
jerrywangsap
|
6月前
|
调度 数据库
什么是 SAP Lock 机制中的 Lock Table
什么是 SAP Lock 机制中的 Lock Table
jerrywangsap
26 0
jerrywangsap
|
6月前
关于 SAP Lock Owner 问题的讨论
关于 SAP Lock Owner 问题的讨论
jerrywangsap
17 0
道长王jj
|
9月前
|
存储 Web App开发 移动开发
📕Local Storage、Session Storage和Cache Storage之间的区别
你知道什么是Cache Storage、Local Storage和Session Storage吗？它们都是一些可以在你的浏览器里保存信息的介质，但是它们有什么不同呢？🤔
道长王jj
356 0
📕Local Storage、Session Storage和Cache Storage之间的区别
姚远OracleACE
|
11月前
|
SQL 存储 缓存
MySQL 8中新增的轻量级备份锁lock instance for backup---发表到爱可生开源社区
mysql 8中新增了一个轻量级的备份锁
姚远OracleACE
176 0
javaedge
|
Java
JVM 的Lock Record简介
JVM 的Lock Record简介
javaedge
545 0
jerrywangsap
get metadata in QHD - still has cache logic
Created by Wang, Jerry, last modified on Dec 26, 2014
jerrywangsap
84 0
get metadata in QHD - still has cache logic
晨曦美
|
大数据 BI 数据库
Unlock SAP Data
上次举办了主题为：“UnlockSAP Data”的webinar后，很多小伙伴都很感兴趣（要是还没看赶紧去看录像啊~~），但是因为是英文的，不是很明白。这里跟大家再具体介绍一下。 SAP的ERP产品经过了多年的发展， 目前主要有2个版本， 老的ECC和新发布的S4HANA。
晨曦美
2005 0
嗯哼9925
如何应对 Another app is currently holding the yum lock;
嗯哼9925
1012 0
余二五
|
监控 关系型数据库 MySQL
MySQL metadata lock阻塞问题
余二五
1115 0

热门文章

最新文章

  • 1
    Mac安装并使用telnet命令操作
  • 2
    OSS回源的几种方式和应用场景
  • 3
    [剑指offer] 孩子们的游戏(圆圈中最后剩下的数)
  • 4
    网络安全系列之二十二 Windows用户账号加固
  • 5
    我理解的一个程序员如何学习前端开发
  • 6
    《社交网站界面设计（原书第2版）》——1.9　为设备之间的空间进行设计
  • 7
    《Microduino实战》——1.2　为什么要开源
  • 8
    .Net函数Math.Round你会用吗？
  • 9
    麻省理工大学新发明：暗黑WiFi透视技术
  • 10
    2014秋C++第19周 补充代码 哈希法的存储与查找
  • 1
    R语言关联规则模型(Apriori算法)挖掘杂货店的交易数据与交互可视化
    25
  • 2
    R语言近似贝叶斯计算MCMC（ABC-MCMC）轨迹图和边缘图可视化
    21
  • 3
    r语言中对LASSO回归，Ridge岭回归和弹性网络Elastic Net模型实现-4
    31
  • 4
    Sentieon | 每周文献-Multi-omics-第四十一期
    26
  • 5
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-2
    16
  • 6
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-1
    23
  • 7
    基于RT-Thread摄像头车牌图像采集系统
    22
  • 8
    R语言极值理论：希尔HILL统计量尾部指数参数估计可视化
    17
  • 9
    【视频】R语言中的分布滞后非线性模型（DLNM）与发病率，死亡率和空气污染示例
    22
  • 10
    sql语句创建数据库
    18

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）