`
CtripMySQLDBA
  • 浏览: 57210 次
  • 来自: 上海
社区版块
存档分类
最新评论

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的元数据锁

并未释放。

 

查看当前事务可以发现,id4的进程有未关闭的事务

 

 

 

Ø  场景三

 

Session 1:



 

Session 2:



 

Session 3:



Session 4:



 

 

查看当前打开事务

 

 

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

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

 

 

对于场景二中的现象,官网MySQL5.6MySQL5.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.55.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.6alter动作加锁机制与MySQL5.5有明显差别。

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

 

 

 

 


 
 

 

 

分享到:
评论

相关推荐

    MySQL出现Waiting for table metadata lock的原因方法

    MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)...

    MySQL表结构变更你不可不知的Metadata Lock详解

    想必玩过mysql的人对Waiting for table metadata lock肯定不会陌生,一般都是进行alter操作时被堵住了,导致了我们在show processlist 时,看到线程的状态是在等metadata lock。本文会对MySQL表结构变更的Metadata ...

    mysql锁表解表

    MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)...

    MySQL Metadata Locking

    自MySQL早期版本以来,其设计一直围绕着`.frm`(格式)文件展开,这些文件包含了基础关系表的定义,并且每个表的定义都存储在独立的文件系统中的一个文件里。这种设计方式确保了模式中表名的独特性。服务器提供的...

    MySQL-metadata-audit:用于审核和监视架构更改,变量更改和用户权限更改的脚本

    MySQL元数据审核它是什么? MySQL Metadata Audit曾经是(dbRecorder)[ ]的...获取代码从github获取最新版本http://github.com/isartmontane/MySQL-metadata-audit创建一个MySQL用户GRANT SELECT, LOCK TABLES ON *.

    mysql-常见问题,索引优化

    MySQL数据库在日常使用中会遇到各种问题,其中索引优化是提升数据库性能的关键环节。本文将探讨MySQL的一些常见问题,特别是与索引相关的优化策略。 1. 关于MySQL `count(distinct)` 的逻辑bug 在执行`count...

    MySQL运维-故障处理手册

    通过上述分析,我们可以看到MySQL运维中的故障处理涉及多个方面,包括错误日志分析、锁机制理解以及死锁处理等。这些知识点对于确保数据库稳定运行至关重要。希望本手册能为MySQL运维人员提供实用的指导和支持。

    mysql show processlist 显示mysql查询进程

    7. **State**:这是线程执行SQL语句的具体阶段,例如Waiting for table metadata lock(等待表元数据锁)、Sending data(发送数据)或Locked(锁定)。这些状态可以帮助分析查询的执行流程。 8. **Info**:展示...

    解决MySQL 5.7中定位DDL被阻塞的问题

    在上篇文章《MySQL表结构变更,不可不知的Metadata Lock》中,我们介绍了MDL引入的背景,及基本概念,从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。 在MySQL 5.7中,针对MDL,...

    mdl——lock.docx

    MDL(Metadata Lock)锁是MySQL数据库中一种用于保护表元数据一致性的机制,自5.5版本开始引入。MDL锁的主要目的是确保在表上有活动事务时,不能对表的元数据进行写操作,从而避免事务隔离问题和数据复制错误。 1. ...

    MySQL8.0锁机制和事务

    信息模式表是 MySQL 8.0 中的系统表,用于存储数据库系统中的 metadata。其中,INFORMATION_SCHEMA.INNODB_TRX 表是非常重要的,它记录了 InnoDB 中每个正在执行的事务,包括该事务获得的锁信息,事务开始时间,事务...

    详细分析mysql MDL元数据锁

    当你在MySQL中执行一条SQL时,语句并没有在你预期的时间内执行完成,这时候我们通常会登陆到MySQL数据库上查看是不是出了什么问题,通常会使用的一个命令就是 show processlist,看看有哪些session,这些session在做...

    mysql45讲 19.为什么我只查一行的语句,也执行这么慢?1

    MySQL 性能优化 - 查询慢的原因分析 MySQL 查询性能优化是一个非常重要的话题,很多人在优化查询时,通常都会想到一些复杂的语句,认为查询需要返回大量的数据。但是,有些情况下,即使只查询一行数据,也可能执行...

Global site tag (gtag.js) - Google Analytics