metadata lock的解决方案-阿里云开发者社区

文章
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

(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 ;

metadata lock的解决方案

哪些操作会获取metadata-lock?

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

怎么处理 metadata_lock?

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

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

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

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

metadata lock的解决方案

哪些操作会获取metadata-lock?

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

怎么处理 metadata_lock?

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

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

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

热门文章

最新文章

相关电子书