mysql metadata lock

最新推荐文章于 2023-11-01 14:59:01 发布

weixin_33753845

最新推荐文章于 2023-11-01 14:59:01 发布

阅读量143

点赞数

文章标签：数据库操作系统 php

原文链接：https://my.oschina.net/liting/blog/423417

版权

为什么80%的码农都做不了架构师？>>>

想必玩过mysql的人对Waiting for table metadata lock肯定不会陌生，一般都是进行alter操作时被堵住了，导致了我们在show processlist 时，看到线程的状态是在等metadata lock。本文会对mysql 的metadata lock做一个小小的总结，希望对大家有所帮助。

MDL是在5.5才引入到mysql，之前也有类似保护元数据的机制，只是没有明确提出MDL概念而已。但是5.5之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是，5.1对于元数据的保护是语句级别的，5.5对于metadata的保护是事务级别的。所谓语句级别，即语句执行完成后，无论事务是否提交或回滚，其表结构可以被其他会话更新；而事务级别则是在事务结束后才释放MDL。

引入MDL后，主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。

5.5以后，什么情况下会碰到MDL锁，我结合实际情况举3个会出现MDL的场景，来分析MDL加锁时机。下文的.测试都是以mysql 5.5这个版本为基准，没有考虑到online ddl，下一篇博文会详细介绍5.6的online ddl。

1.大查询或mysqldump导致alter等待MDL

时间点	会话A	会话B	会话C
1	Select count(*) from t;
2		alter table t add column c3 int;
3			Show processlist； B：copy to tmp table
4		阻塞	Show processlist; B：Waiting for table metadata lock
5	A：执行完毕
6			Show processlist; B：rename table
7	Select count(*) from t;
8		B：执行完毕
9			Show processlist; A: Sending data
10	A:执行完毕

表1

从表1可以看到，会话A先执行select ,B后执行alter,在会话A执行完毕前，会话B拿不到MDL锁，从表格上面来看，主要阻塞在rename阶段。A会话在时间点5执行完毕后，会话B拿到MDL锁，变为rename table状态，这个操作持续时间非常短，时间点7，A会话再次执行查询，当B执行完后，此时A正常执行。这说明对于MDL锁而言，select会阻塞alter，而alter不会阻塞select。在rename的瞬间，alter是会阻塞select的，详细请参考《mysql metadata lock(二)》

2.表上存在未提交的事务，导致alter等待MDL

时间点	会话A	会话B	会话C
1	set autocommit=0; update t set c2='9999' where c1=4;
2		alter table t drop column c3;
3			Show processlist; B：Waiting for table metadata lock
4	A：提交事务 commit
5			Show processlist； B：copy to tmp table
6		B：继续执行
7	update t set c2='9999' where c1=4;阻塞
8			Show processlist; A: Waiting for table metadata lock B: copy to tmp table
9		B执行完毕
10	A执行完毕

表2

从表2可以看到，会话A第一次执行update语句后，未提交，导致后面会话B执行alter语句时需要等待MDL锁；时间点4，A会话提交事务，此时会话B获取MDL锁，开始执行；时间点7，A会话再次发起update操作，此时A会话被阻塞住，这说明对于MDL锁而言，update会阻塞alter，同样alter也会阻塞update。

PS：时间点3由于通过show processlist只看到alter被阻塞了，但不清楚被谁阻塞，可以通过查看information_schema.innodb_trx可以找到活动的事务。

3.这种情况是第1种情况的特例，存在一个查询失败的语句，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。

时间点	会话A	会话B	会话C
1	Start transaction； Select c99 from t； Unknown column 'c99' in 'field list'
2		alter table t drop column c3;
3			Show processlist； B：copy to tmp table
4			Show processlist; B：Waiting for table metadata lock
5	A：提交事务 commit
6		执行完毕

表3

这里注意时间1，会话A要显示开启一个事务，否则查询会隐式回滚结束，无法重现上面的场景。会话B执行alter后，没有立即阻塞住，而是立马开始copy to tmp table，这个过程结束后，才进行了MDL锁等待。这怎么解释呢，应该是执行alter操作主要分为创建临时新表->插入老表的数据->临时新表rename to老表三个步骤，在这种情况下，到最后一步才需要MDL锁，所以copy过程中不会阻塞。由于没有查询在进行，而且查询也没有进入innodb层 (失败返回)，所以show processlist和information_schema.innodb_trx没有可以参考的信息。

这里有一个小疑点，对于第二种情况，alter在开始时就立马堵住了，第一种和第三种情况是copy结束后，才堵住。通过多次实验，确实发现第二种情况在opening tables就堵住了。为什么要这样具体原因还没弄清楚，有兴趣的同学可以去debug源码看看究竟。

root@chuck 11:57:41>show profile for query 4;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000050 |
| checking permissions | 0.000004 |
| checking permissions | 0.000005 |
| init | 0.000007 |
| Opening tables | 19.068828 |
| System lock | 0.000011 |
| setup | 0.000034 |
| creating table | 0.005047 |
| After create | 0.000056 |
| copy to tmp table | 89.574539 |
| rename result table | 1.101672 |
| end | 0.000040 |
| query end | 0.000004 |
| closing tables | 0.000009 |
| freeing items | 0.000021 |
| logging slow query | 0.000002 |
| logging slow query | 0.000090 |
| cleaning up | 0.000004 |
+----------------------+-----------+

参考：

http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/

http://ctripmysqldba.iteye.com/blog/1938150

，MDL作用以及MDL锁导致阻塞的几种典型场景，文章的最后还留下了一个小小的疑问。本文将更详细的介绍MDL，主要侧重介绍MDL的原理和实现。一般而言，商业数据库系统实现锁，一般将锁划分为读锁(共享锁)和写锁(排它锁)，为了进一步提高并发性，还会加入意向共享锁和意向排它锁。但是偏偏mysql的MDL搞地比较复杂，但目的也是为了提高并发度。MDL包含有9种类型，详细参考表1。主要其实也是两大类，只是对共享锁做了进一步细分。

一、MDL的锁类型

锁名称	锁类型	说明	适用语句
MDL_INTENTION_EXCLUSIVE	共享锁	意向锁，锁住一个范围	任何语句都会获取MDL意向锁，然后再获取更强级别的MDL锁。
MDL_SHARED	共享锁，表示只访问表结构
MDL_SHARED_HIGH_PRIO	共享锁，只访问表结构	show create table 等只访问INFORMATION_SCHEMA的语句
MDL_SHARED_READ	访问表结构并且读表数据	select语句 LOCK TABLE ... READ
MDL_SHARED_WRITE	访问表结构并且写表数据	SELECT ... FOR UPDATE DML语句
MDL_SHARED_UPGRADABLE	可升级锁，访问表结构并且读写表数据	Alter语句中间过程会使用
MDL_SHARED_NO_WRITE	可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。	Alter语句中间过程会使用
MDL_SHARED_NO_READ_WRITE	可升级锁，访问表结构并且读写表数据，并且禁止其它事务读写。	LOCK TABLES ... WRITE
MDL_EXCLUSIVE	写锁	禁止其它事务读写。	CREATE/DROP/RENAME TABLE等DDL语句。

　　　　　　　　　　　　　　　　　　　　表1

二、MDL的兼容性矩阵

	IX	S	SH	SR	SW	SU	SNW	SNRW	X
IX	1	1	1	1	1	1	1	1	1
S	1	1	1	1	1	1	1	1	0
SH	1	1	1	1	1	1	1	1	0
SR	1	1	1	1	1	1	1	0	0
SW	1	1	1	1	1	1	0	0	0
SU	1	1	1	1	1	1	0	0	0
SNW	1	1	1	1	0	0	0	0	0
SNRW	1	1	1	0	0	0	0	0	0
X	1	0	0	0	0	0	0	0	0

说明：横向表示其它事务已经持有的锁，纵向表示事务想加的锁

三、几种典型语句的加(释放)锁流程

1.select语句操作MDL锁流程

1）Opening tables阶段，加共享锁

a) 加MDL_INTENTION_EXCLUSIVE锁

b) 加MDL_SHARED_READ锁

2）事务提交阶段，释放MDL锁

a) 释放MDL_INTENTION_EXCLUSIVE锁

b) 释放MDL_SHARED_READ锁

2. DML语句操作MDL锁流程

1）Opening tables阶段，加共享锁

a) 加MDL_INTENTION_EXCLUSIVE锁

b) 加MDL_SHARED_WRITE锁

2）事务提交阶段，释放MDL锁

a) 释放MDL_INTENTION_EXCLUSIVE锁

b) 释放MDL_SHARED_WRITE锁

3. alter操作MDL锁流程

1）Opening tables阶段，加共享锁

a) 加MDL_INTENTION_EXCLUSIVE锁

b) 加MDL_SHARED_UPGRADABLE锁，升级到MDL_SHARED_NO_WRITE锁

2）操作数据，copy data，流程如下：

a) 创建临时表tmp，重定义tmp为修改后的表结构

b) 从原表读取数据插入到tmp表

3）将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁

a) 删除原表，将tmp重命名为原表名

4）事务提交阶段，释放MDL锁

a) 释放MDL_INTENTION_EXCLUSIVE锁

b) 释放MDL_EXCLUSIVE锁

四、典型问题分析。

一般而言，我们关注MDL锁，大部分情况都是线上出现异常了。那么出现异常后，我们如何去判断是MDL锁导致的呢。监视MDL锁主要有两种方法，一种是通过show processlist命令，判断是否有事务处于“Waiting for table metadata lock”状态，另外就是通过mysql的profile，分析特定语句在每个阶段的耗时时间。

抛出几个问题：

select 与alter是否会相互阻塞
dml与alter是否会相互阻塞
select与DML是否会相互阻塞

结合第三节几种语句的上锁流程，我们很容易得到这三个问题的答案。语句会在阻塞在具体某个环节，可以通过profile来验证我们的答案是否正确。

第一个问题，当执行select语句时，只要select语句在获取MDL_SHARED_READ锁之前，alter没有执行到rename阶段，那么select获取MDL_SHARED_READ锁成功，后续有alter执行到rename阶段，请求MDL_EXCLUSIVE锁时，就会被阻塞。rename阶段会持有MDL_EXCLUSIVE锁，但由于这个过程时间非常短(大头都在copy数据阶段)，并且是alter的最后一个阶段，所以基本感觉不到alter会阻塞select语句。由于MDL锁在事务提交后才释放，若线上存在大查询，或者存在未提交的事务，则会出现ddl卡住的现象。这里要注意的是，ddl卡住后，若再有select查询或DML进来，都会被堵住，就会出现threadrunning飙高的情况。

第二个问题，alter在opening阶段会将锁升级到MDL_SHARED_NO_WRITE，rename阶段再将升级为MDL_EXCLUSIVE，由于MDL_SHARED_NO_WRITE与MDL_SHARED_WRITE互斥，所以先执行alter或先执行DML语句，都会导致语句阻塞在opening tables阶段。结合第一个和第二个问题，就可以回答《mysql metadata lock(一)》的疑问了。

第三个问题，显然，由于MDL_SHARED_WRITE与MDL_SHARED_READ兼容，所以它们不会因为MDL而导致等待的情况。

环境说明：

MySQL 5.6.16

OS:Linux RedHat 6.2 64bit

1.问题描述

目前新上一个使用MySQL数据库项目，在数据库中，每隔5分钟做truncate某个表操作，经常出现metadata lock锁等待，导致后面的对这个表的所有操作（包括读）全部metadata lock等待。严重影响了数据库运行。

且metadata lock锁等待不同于普通的行级锁，等待超时时间默认为365天，而普通的行级锁超时是120s

mysql> show variables like '%lock_wait%';

+--------------------------+----------+

| Variable_name | Value |

+--------------------------+----------+

| innodb_lock_wait_timeout | 120 |

| lock_wait_timeout | 31536000 |

+--------------------------+----------+

2 rows in set (0.00 sec)

因此如果metadata lock锁的源头不释放，则会一直阻塞，必须需要人为干预。

2.为什么需要Metadata lock

Metadata lock介绍：参考官方手册： http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

MySQL 5.5.3 and up uses metadata locking to manage access to objects (tables, triggers, and so forth). Metadata locking is used to ensure data consistency but does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.

参考MySQL bug989： http://bugs.mysql.com/bug.php?id=989

该bug是一个比较著名的问题：

我们知道，binlog内操作的记录是基于事务的提交顺序进行的，如果有一个事务未执行完成，而这个时候drop了表，这样在从库的执行顺序就会出现问题。

因此MySQL在5.5.3版本后引入了Metadata lock锁，事务释放后才会释放Metadata lock，这样在事务完成期间，是不能进行DDL操作的。

3.Metadata lock监控

当对表的DDL操作很慢的时候，可以通过如下方法查看当前是否是在等待Metadata lock：

mysql> select * from information_schema.processlist where state = 'Waiting for table metadata lock';

+----+------+-----------+------+---------+------+---------------------------------+--------------------------+

+----+------+-----------+------+---------+------+---------------------------------+--------------------------+

+----+------+-----------+------+---------+------+---------------------------------+--------------------------+

2 rows in set (0.00 sec)

其中线程ID为7的truncate被其他事务阻塞，而线程ID为9的被truncate table阻塞，因此该查询主要看哪个会话在做DDL操作，其他的会话的state为metadata lock均为被该DDL阻塞。

那么问题来了，怎么去判断DDL被什么锁住了？

这个从目前来看比较困难，不能直观的去判断，网上有人做了一个插件可以实现（在MariaDB 10中默认已提供类似功能）：

http://xiezhenye.com/2013/07/mysql-plugin-mdl-locks.html

[root@mysql-db101 tmp]# tar -xzvf mysql-5.6.16.tar.gz

[root@mysql-db101 tmp]# unzip mysql-plugin-mdl-info-master.zip

[root@mysql-db101 tmp]# cp -r ./mysql-plugin-mdl-info-master/src ./mysql-5.6.16/plugin/mdl_info

[root@mysql-db101 tmp]# cd ./mysql-5.6.16

[root@mysql-db101 mysql-5.6.16]# cmake \

-DCMAKE_INSTALL_PREFIX=/home/mysql/mysql \

-DMYSQL_DATADIR=/home/mysql/data \

-DMYSQL_TCP_PORT=3306

#注意，mysql是以源代码编译出的debug版本，那编译插件的时候不要加 -DBUILD_CONFIG=mysql_release

[root@mysql-db101 mysql-5.6.16]# cd plugin/mdl_info/

[root@mysql-db101 mdl_info]# make

[root@mysql-db101 mdl_info]# make install

mysql> INSTALL PLUGIN MDL_LOCKS SONAME 'mdl_info.so';

ERROR 1127 (HY000): Can't find symbol 'MDL_LOCKS' in library

mysql>

mysql> show variables like '%plugin%';

+---------------+-------------------------------+

| Variable_name | Value |

+---------------+-------------------------------+

| plugin_dir | /home/mysql/mysql/lib/plugin/ |

+---------------+-------------------------------+

1 row in set (0.00 sec)

mysql> INSTALL PLUGIN MDL_info SONAME 'mdl_info.so';

Query OK, 0 rows affected (0.02 sec)

#这里plugin_name为MDL_INFO，而非文档中说的MDL_LOCKS

mysql> select * from information_schema.mdl_info;

+-----------+-------------+---------------------+-----------+----------+------+

+-----------+-------------+---------------------+-----------+----------+------+

+-----------+-------------+---------------------+-----------+----------+------+

3 rows in set (0.00 sec)

这里可以看到线程6阻塞了线程7的truncate操作

通过如下语句查看相应的会话情况：

mysql> select

-> a.*,b.user,b.host,b.command,b.time,b.state,b.info

-> from

-> information_schema.mdl_info a,

-> information_schema.PROCESSLIST b

-> where

-> a.thread_id = b.id\G;

*************************** 1. row ***************************

THREAD_ID: 6

DURATION: TRANSACTION

TYPE: SHARED_READ

NAMESPACE: TABLE

DATABASE: baofeng

NAME: a

user: root

host: localhost

command: Sleep

time: 1035

state:

info: NULL

*************************** 2. row ***************************

THREAD_ID: 7

DURATION: STATEMENT

TYPE: INTENTION_EXCLUSIVE

NAMESPACE: GLOBAL

DATABASE:

NAME:

user: root

host: localhost

command: Query

time: 990

state: Waiting for table metadata lock

info: truncate table baofeng.a

*************************** 3. row ***************************

THREAD_ID: 7

DURATION: TRANSACTION

TYPE: INTENTION_EXCLUSIVE

NAMESPACE: SCHEMA

DATABASE: baofeng

NAME:

user: root

host: localhost

command: Query

time: 990

state: Waiting for table metadata lock

info: truncate table baofeng.a

3 rows in set (0.00 sec)

ERROR:

No query specified

4.导致Metadata Lock的场景

参考： http://www.tuicool.com/articles/ja6zia

场景1：

会话1正在对表a进行DML操作（包括query），这个时候会话2执行DDL操作，需要获取metadata独占锁，因此等待会话1。

这个时候可以通过show processlist能查看该会话（该会话的state不会waiting for table metadata lock）

场景2：

会话1对表a进行DML（包括query）事务操作后，没有commit/rollback，这个时候show processlist是看到的只是会话处于sleep状态，执行的SQL显示为空。而这个时候会话2执行DDL操作，同样获取不到metadata独占锁，就会等待。可以通过查询系统事务表有体现：

mysql> select * from information_schema.innodb_trx\G;

*************************** 1. row ***************************

trx_id: 11134

trx_state: RUNNING

trx_started: 2014-12-23 10:23:44

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 0

trx_mysql_thread_id: 9

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 0

trx_lock_memory_bytes: 360

trx_rows_locked: 0

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: READ COMMITTED

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

1 row in set (0.00 sec)

ERROR:

No query specified

但是如果事务很多，则没办法判断是哪个会话导致。

该场景最为普遍，而且是最频发，后面重点会对该场景进行测试。

场景3：

通过show processlist看不到表A上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对表A进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

官方手册上对此的说明如下：

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志

5.如何快速处理Metadata lock

如果是手工执行的DDL操作，例如加字段、drop表等，可以手工Cancel，先查找对当前该表是否在执行长事务操作，或者有未提交事务。确认没问题后再执行DDL操作。

如果是应用程序中执行的DDL操作，例如truncate，这个时候没办法调整应用，则可以利用MDL插件，去查询当前的DDL被哪个会话阻塞，kill掉该会话线程。这个方法前提是需要安装MDL插件，目前我们的环境还都没有安装。

6.如何避免Metadata Lock

6.1.关注autocommit

autocommit分成2个层次：

数据库autocommit
客户端工具的autocommit

MySQL默认的autocommit为1，即自动提交，这种方式不太安全，因为事务默认不受人为控制，因此建议关闭autocommit。咨询了支付宝的MySQL DBA，支付宝的MySQL的autocommit全部是关闭的。

客户端工具分为2种，一种是继承数据库的autocommit模式，例如SQLyog、Mysql命令行接口；还有一种是自己独立的autocommit，例如MySQL workbench，设置工具本身的autocommit，而无视数据库层面autocommit。

无论是开发还是维护，一定要弄清楚自己的客户端的autocommit模式。

无论是使用哪种客户端工具，首先要弄清楚当前环境下的autocommit方式是什么，如果不是autocommit，一定要确保所有的操作都需要显示的commit/rollback，否则即使是select查询某个表，甚至是语义（select一个错误的字段）报错，也会造成对其他会话对该表的DDL的metadata lock等待。