MySQL中 metadata lock问题分析

  MySQL版本：5.6.12-log

 

 

Ø  场景一

 

 

Session1:

Session2:

Session3:

 

至此，我们会产生疑问，MySQL5.6说好的online DDL呢，怎么又会出现Waiting for table metadata lock？

 

我们先来做个alter动作执行过程中，insert的操作

 

Session1:

 

 

Session2:

Session3:

 

显然MySQL5.6中执行alter过程中，允许对表进行插入动作，正如官网所说的

 

MySQL 5.6 enhances many other types of ALTER TABLE operations to avoid copying the table. Another enhancement allows SELECT queries and INSERT, UPDATE, and DELETE (DML) statements to proceed while the table is being altered. This combination of features is now known as online DDL.

  从上述例子可以看出，我们在执行DDL语句的时候得事先看一下，进程中是否已经存在某些DML语句

占用了表的元数据锁，这样会导致DDL语句处于锁等待状态。

 

 

Ø  场景二

 

Session 1:

 

 

 

Session 2:

 

 

Session 3:

 

Session 4：

查看当前进程

 

alter,drop动作均等待获取元数据锁，原因在于会话一中未关闭的事务，占用了表t1,t2的元数据锁

并未释放。

 

查看当前事务可以发现，id为4的进程有未关闭的事务

 

 

 

Ø  场景三

 

Session 1:

 

Session 2:

 
Session 3:

Session 4:

 

 

查看当前打开事务

 

 

其实session1中的事务并未开启，但是由于select获取表元数据的语句，语法上是有效的，

虽然执行失败了，但是任然不会释放元数据锁，故而导致session2、3的alter,drop动作被阻塞。

 

 

对于场景二中的现象，官网MySQL5.6、MySQL5.5均中有解释如下：

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.

当出现场景二时，如何判断是哪个进程导致的呢，我们可以尝试查看

表performance_schema. events_statements_current，分析进程状态来进行判断。

 

 

为什么引入MetaData Locking？

 

从5.5.3开始引进的Metadata Locking相关的改动

 

MetaData lock类型如下：

 

MDL_INTENTION_EXCLUSIVE	An intention exclusive metadata lock. Used only for scoped locks.
MDL_SHARED	To be used in cases when we are interested in object metadata only and there is no intention to access object data (e.g. for stored routines or during preparing prepared statements).
MDL_SHARED_HIGH_PRIO	Used for cases when there is no intention to access object data (i.e.data in the table).（e.g. for.DESC TABLE）
MDL_SHARED_READ	A shared metadata lock for cases when there is an intention to read data from table.( To be used for tables in SELECTs, subqueries, and LOCK TABLE ...  READ)
MDL_SHARED_WRITE	A shared metadata lock for cases when there is an intention to modify (and not just read) data in the table.To be used for tables to be modified by INSERT, UPDATE, DELETE statements, but not LOCK TABLE ... WRITE or DDL). Also taken by SELECT ... FOR UPDATE
MDL_SHARED_NO_WRITE	An upgradable shared metadata lock which blocks all attempts to update,table data, allowing reads To be used for the first phase of ALTER TABLE, when copying data between tables, to allow concurrent SELECTs from the table, but not UPDATEs
MDL_SHARED_NO_READ_WRITE	An upgradable shared metadata lock which allows other connections to access table metadata, but not data.To be used for LOCK TABLES WRITE statement.
MDL_EXCLUSIVE	An exclusive metadata lock.To be used for CREATE/DROP/RENAME TABLE statements and for execution of certain phases of other DDL statements

 

 

 

 

Metadata lock锁的相互依存关系：

 

 

在5.5.3之前Meta Data Locking是怎么工作的：

 

尽管事务隔离级别是REPEATABLE-READ，还是不能重复SELECT。

5.5版本之前的这个表现意味着，SQL可以以不同的顺序写入到binlog文件，

这违反了锁定义以及串行化的概念。

 

5.5.3之后版本是如何处理的Metadata Locking：

 

从5.5.3开始DDL语句以一个隔离的事务行为方式执行元数据的修改。也就是说，任何已经开始

的事务将一直持有表的元数据锁直到事务提交。由于开始的事务会持有事务关联的所有表的

元数据锁，所以任何DDL操作在前面的事务提交前是不能够执行的。

 

 

存在一个事务持有元数据锁等待的情况下，分别查看5.5，5.6版本上语句执行消耗

 

5.5版本：

 

l  测试一：

 

Session1事务未关闭

 

session1:

 

 

session2:

 

session3:

 

session4:

 

 

Session1事务关闭

 

session1:

 

session2:

 

session3:

 

 

l  测试二：

 

Session1事务未关闭

 

session1:

session2:

 

session3:

 

session4:

 

 

Session1事务关闭

 

session2:

 

 从上述测试可以看出，alter是一个多步的操作，旧表加上SNW锁之后，执行下面操作，

session1执行select事务未提交占有SR锁，alter动作可以加上共享锁SNW，

但是在RENAME动作时无法获取独享排他锁，处于等待状态，session3需要获取SR锁，

但是由于锁的优先级导致只能处于等待状态；当session1执行delete事务未提交占有SW锁，

alter动作无法获取SNW锁，所以在opening tables时就开始等待。

 

 

5.6版本

 

l  测试一：

 

Session1事务未关闭

 

session1:

 

 

session2:

 

session3:

 

 

session4:

 

 

Session1事务关闭

 

session2:

 

 

session3:

 

 

 

l  测试二：

 

Session1事务未关闭

 

session1:

 

session2:

session3:

 

session4:

 

 

Session1事务关闭

 

场景与MySQL5.6版本测试一相同。

 

 

 

从上述测试可以看出，MySQL5.6的alter动作加锁机制与MySQL5.5有明显差别。

不论session1执行的是select还是delete，alter动作都会加一个独享元数据锁，所以都会影响session3的读取。