metadata lock的解决方案

最新推荐文章于 2024-03-05 14:54:09 发布
最新推荐文章于 2024-03-05 14:54:09 发布
阅读量1k 收藏 1
点赞数
文章标签： python 数据库 运维
原文链接：https://yq.aliyun.com/articles/191384
版权

文章
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，对性能的影响还需要测试

weixin_34163741
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Java路径问题解决方案汇集
01-21
Java路径中的空格问题 　　1、 URLTest.class.getResource(/).getPath(); 　　URLTest.class.getResource(/).getFile(); 　　URLTest.class.getClassLoader().getResource().getPath(); 　　Thread.currentThread().getContextClassLoader().getResource().getPath();等多种相似方式获得的路径，不能被FileReader()和FileWriter()直接应用，原因是URL对空格，特
(mysql)table metadata lock原因及解决
zhangjunli的博客
01-07 1372
table metadata lock出现原因及解决方法
MySQL 元数据锁及问题排查（Metadata Locks MDL）
最新发布
Vincent的博客
03-05 1013
"元数据"是用来描述数据对象定义的，而元数据锁（Metadata Lock MDL）即是加在这些定义上。通常我们认为非锁定一致性读（简单select）是不加锁的，这个是基于表内数据层面，其依然会对表的元数据加锁，保证读取数据期间表结构不会变更。
MYSQL METADATA LOCK(MDL LOCK)MDL锁问题分析
09-09
主要介绍了MYSQL METADATA LOCK(MDL LOCK)MDL锁问题分析，并通过实例给大家例句的问题处理办法，需要的朋友参考学习下。
MySQL出现Waiting for table metadata lock的原因方法
09-09
在本篇内容里小编给大家整理了MySQL出现Waiting for table metadata lock的原因以及解决方法对此有需要的朋友们学习下。
MySQL表结构变更你不可不知的Metadata Lock详解
01-21
前言 想必玩过mysql的人对Waiting for table metadata lock肯定不会陌生，一般都是进行alter操作时被堵住了，导致了我们在show processlist 时，看到线程的状态是在等metadata lock。本文会对MySQL表结构变更的Metadata Lock进行详细的介绍。 在线上进行DDL操作时，相对于其可能带来的系统负载，其实，我们最担心的还是MDL其可能导致的阻塞问题。 一旦DDL操作因获取不到MDL被阻塞，后续其它针对该表的其它操作都会被阻塞。典型如下，如阻塞稍久的话，我们会看到Threads_running飙升，CPU告警。 mysql> s
为何存在Metadata Lock
juyang_08的专栏
06-29 265
为何会存在Metadata Lock？ -- 源自于一个mysql的一个bug，mysql这样描述：http://bugs.mysql.com/bug.php?id=989 大致意思就是：在还没有Metadata Lock时在主库中session A在做一个DML操作如 insert 而没有提交，同时有另一个session B对同一个对象进行了DDL操作，如drop table；这就会导致从库执行有误。因为mysql的binlog是根据事务提交的先后顺序来记录的，前面的操作会导致在写binlog时先记录
mysql MDL锁如何解决_MYSQL METADATA LOCK(MDL LOCK)MDL锁问题分析
weixin_35692916的博客
01-19 950
一、前言MYSQL中MDL锁一直是一个比较让人比较头疼的问题，我们谈起锁一般更加倾向于INNODB下层的gap lock、next key lock、row lock等，因为它很好理解，也很好观察，而对于MDL LOCK却了解得很少，因为它实在不好观察，只有出现问题查看show processlist勉强可以看到简单的所谓的Waiting for table metadata lock之类的状态，...
MySQL长事务导致的Table Metadata Lock
翡翠谷的博客
07-20 5321
为了防止对表进行操作时有DDL修改表结构，MySQL引入了Metadata Lock，在事务结束之前不能进行DDL操作；然而Metadata Lock有时也会产生一些问题，比如遇到长事务时。
MYSQL 持续踩坑之-metadata lock
qq_22351805的博客
02-03 1522
本人博客原地址：MYSQL 持续踩坑之-metadata lock 创作时间： 2019.12.07 16:54:42 起因 最近生产的MySQL数据库出现锁表的情况，通过show processlist;发现下图状态（模拟）waiting for table metadata lock 在metadata lock之前，先大致回顾下MySQL的锁 从锁的粒度和范围，大致分三类：全局锁，表锁，行锁。 共享锁S，与排它锁X均为行锁。innodb支持对更粗粒度（数据库级，表级，页级）加意向锁。MYSQL意
MySQL Metadata lock深入
qiuyepiaoling的专栏
06-23 678
原文http://www.sqlparty.com/mysql-metadata-lock%E6%B7%B1%E5%85%A5/ MySQL 5.5.3版本中引入了Metadata lock。在5.5.3版本之前，MySQL事务对于表结构元数据(Metadata)的锁定是语句(statement)粒度的，即语句执行完成后，不管事务是否可以完成，其表结构就可以被其他会话更新掉！而引入Met
Metadata Locking
u013469501的博客
07-11 151
MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas, stored programs (procedures, functions, triggers, and scheduled events), and table
MySQL事务与Metadata Lock问题
cishancui8546的博客
12-25 194
环境说明： MySQL 5.6.16 OS:Linux RedHat 6.2 64bit1.问题描述 目前新上一个使用MySQL数据库项目，在数据库中，每隔5分钟做truncate某个表操作，经常出现metad...
mysql锁机制之metadata lock
墨鱼丸子
01-24 1765
基于mysql5.6的metadata lock的介绍 1、max_write_lock_count 5.6官方文档解释： After this many write locks, permit some pending read lock requests to be processed in between. Write lock requests have higher priority t...
MySQL DDL操作触发Metadata Lock等待，怎么办？5个高效应对策略解析！
孤芳不自赏
09-19 237
本文介绍了MySQL数据库中DDL操作中触发MetadataLock的问题及解决方法。MetadataLock是保护数据库元数据的锁机制，但DDL操作可能导致其被长时间占用，引发性能问题和应用程序中断。文章介绍了使用工具、拆分DDL操作、调整等待时间、使用代理和升级数据库版本等方法。
MySQL笔记-MDL锁（metadata lock）
IT1995的博客
05-05 1594
MySQL5.5版本引入了MDL锁（metadata lock），用于解决或保证DDL操作与DML操作之间的一致性。在mysqldump的时候不能做DDL操作，会提示waiting for table metadata lock；做DDL操作没办法保护事务，因此引入了metadata lock。如下时序图： 如果没有MDL锁保护，那么session2，就会直接执行DDL操作，这样会导致ses...
mysql创建索引导致死锁，数据库崩溃，mysql的表级锁之【元数据锁（meta data lock，MDL)】全解
秃了也弱了
11-11 5400
COMMIT;# 如果user1在一个表上有一个活动的事务，然后user2删除这个表，那么user1会提交，那么在binlog中我们有如下内容(binlog记录的操作顺序顺序)： 1. DROP TABLE t;2. BEGIN;COMMIT;很显然mysql执行时会先删除表t，然后执行insert 会报1032 error。再举一个简单的例子，如果你在查询一个表的过程中，另外一个session对该表删除了一个列，那前面的查询到底该显示什么呢？
mysql元数据锁（MDL锁）手动杀死进程
m0_58590712的博客
01-21 181
mysql元数据锁（MDL锁）手动杀死进程
Waiting for table metadata lock怎么解决
09-18
总之，解决“Waiting for table metadata lock”错误需要综合考虑各种方面，包括查询优化、事务拆分、硬件升级等。根据具体情况选择适合的解决方法，以减少对元数据锁的竞争，并提高数据库的并发性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值