`
blue2048
  • 浏览: 182633 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql metadata lock

阅读更多

转载:http://www.sqlparty.com/mysql-metadata-lock%E6%B7%B1%E5%85%A5/
参考:http://www.cnblogs.com/cchust/p/3826398.html

metadata lock的超时时间是lock_wait_timeout,并不是innodb_lock_wait_timeout

MySQL 5.5.3版本中引入了Metadata lock: DDL语句打破了事务的隔离级别
    在5.5.3版本之前,MySQL事务对于表结构元数据(Metadata)的锁定是语句(statement)粒度
            即语句执行完成后,不管事务是否可以完成,其表结构就可以被其他会话更新掉!
    引入Metadata lock后,表结构元数据(Metadata)的锁定变成了事务(transaction)粒度
            即只有事务结束时才会释放Metadata lock。

    现象:
        显式开启事务后start transaction; 该事务内的query语句(包含select)会占用相关表的metadata lock(profile:Opening tables阶段)
        导致DDL语句被阻塞,因为获取不到表的metadata lock

MySQL 5.6.6版本后 优化
    metadata lock不阻塞DDL语句,但原有session再访问此表时会返回Error信息“Table definition has changed, please retry transaction”



具体案例:

 

引入Metadata lock之前(5.5.3版本以前)

5.5.3之前的Metadata处理方式,有如下问题:

1)隔离级别会被破坏

例如:理论上REPEATABLE-READ隔离级别下,一个事务内同样的查询能够读到同样的内容,如果没有新记录的话。但是由于没有事务级别的Metadata锁,这个隔离级别下就不能满足这样的约定:

5.1.51版本中运行:

Session1 mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation  |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)Session1 mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

 

Session1 mysql> select * from table1 where id=1;
+——+——+——+——-+
| id   | one  | two  | three |
+——+——+——+——-+
|    1 |   34 |   45 |    80 |
+——+——+——+——-+
1 row in set (0.00 sec)

Session2 mysql> alter table table1 add column c1 int;
Query OK, 4 rows affected (0.23 sec)
Records: 4  Duplicates: 0  Warnings: 0

Session1 mysql> select * from table1 where id=1;
Empty set (0.00 sec)  

可以看到Session1的事务内部,虽然隔离级别是“可重复读”,但是由于并发的Session2中途成功更改了table1的结构(因为前一个SQL查完table1后就释放了对表结构的约束),两次查询的结果不同!

遗留问题1.为什么第二次查询,记录没有发生变化,但结果集返回空?

2)可能导致复制的失败

上例的影响还比较明显,这种机制还有隐藏的危害,即可能影响复制的成功进行!

我们知道,binlog内操作的记录是基于事务的提交顺序进行的。与上例类似,ALTER语句后执行但可能先提交,这影响到了执行中的其他事务,而那些事务后提交。而SLAVE看来完全是ALTER先执行,这很可能导致被打断的事务在SLAVE上重现时导致失败,要么表结构不对,要么数据导致不一致。

引入Metadata lock之后(5.5.3版本及以后)

而5.5.3中引入了Metadata lock后,由于事务结束后才会释放,避免了这样的问题。还是上例,在5.5.30版本中运行:

Session1 mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation  |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)Session1 mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

 

Session1 mysql> select * from table1 where id=1;
+——+——+——+——-+
| id   | one  | two  | three |
+——+——+——+——-+
|    1 |   34 |   45 |    80 |
+——+——+——+——-+
1 row in set (0.00 sec)

Session2 mysql> alter table table1 add column c1 int;
这个语句一直等待。

遗留问题2:为什么没有锁等待超时??

我们再开一个会话查看当前状况:

Session3 mysql>show full processlist;
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| Id     | User      | Host                 | db            | Command | Time | State                           | Info                                 |
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| 163612 | sup       | 192.168.2.213:1844   | dbadb         | Sleep   |   37 |                                 | NULL                                 |
| 163613 | sup       | 192.168.2.213:1883   | dbadb         | Query   |   21 | Waiting for table metadata lock | alter table table1 add column c3 int |
| 163614 | sup       | 192.168.2.213:1884   | dbadb         | Query   |    0 | NULL                            | show full processlist                |
+——–+———–+———————-+—————+———+——+———————————+————————————–+

这里的示例中,我们可以看到ALTER TABLE会应Metadata lock而阻塞。一旦Session 1中的事务提交或者回滚,即释放了table1表的Metadata lock,Session2的操作立即可以执行。

那么究竟是怎样的操作会被Metadata lock影响到呢?我们可以借助SHOW PROFILE看看到底ALTER命令在哪里卡住了:

Session2 mysql> show profile;
+——————————+———-+
| Status                       | Duration |
+——————————+———-+
| starting                     | 0.000093 |
| checking permissions         | 0.000062 |
| checking permissions         | 0.000058 |
| init                         | 0.000058 |
| Opening tables               | 0.000078 |
| System lock                  | 0.000061 |
| setup                        | 0.000077 |
| creating table               | 0.053156 |
| After create                 | 0.000111 |
| copy to tmp table            | 0.000231 |
| rename result table          | 5.780077 |
| end                          | 0.000545 |
| Waiting for query cache lock | 0.000064 |
| end                          | 0.000126 |
| query end                    | 0.000090 |
| closing tables               | 0.000110 |
| freeing items                | 0.000118 |
| logging slow query           | 0.000095 |
| logging slow query           | 0.000125 |
| cleaning up                  | 0.000090 |
+——————————+———-+
20 rows in set (0.02 sec)

可以看到,卡住的那一步是在rename result table,MySQL后台将ALTER保存变成连续操作“创建临时新表->插入老表的数据->临时新表取到老表(RENAME)”,即实际的变化老表的操作在RENAME阶段才发生。而这一步即是被阻塞的那一步。所以可以确认,Metadata lock影响到的是要真实修改表结构的动作。

基于此,我们可以验证ALTER TABLE,RENAME TABLE,DROP TABLE都会被Metadata lock影响,验证步骤与上类似,省略。

值得注意的是,创建一个已存在的表也会被Metadata lock影响。如果在一个事务中用到了表A的Metadata lock(如SELECT了该表)且事务未完成,那么创建同名的表也会被卡住。所参考的文章中提及这一点。有兴趣可参看:讨论页  ,这里不作讨论。

metadata lock的另一个副作用:如上例中,ALTER TABLE应metadata lock被阻塞后,这导致后续其他事务针对该表的SELECT也会被阻塞!即ALTER TABLE的操作会影响到其他SELECT操作。根据上述示例:

Session2 mysql> alter table table1 add column c1 int;

此步一直处于等待状态时,新会话中查询table1。

Session3 mysql> select * from table1 where id=1;  #一直等待

Session4 mysql>show full processlist;
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| Id     | User      | Host                 | db            | Command | Time | State                           | Info                                 |
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| 163612 | sup       | 192.168.2.213:1844   | dbadb         | Sleep   |   45 |                                 | NULL                                 |
| 163613 | sup       | 192.168.2.213:1883   | dbadb         | Query   |   35 | Waiting for table metadata lock | alter table table1 add column c1 int |
| 163614 | sup       | 192.168.2.213:1884   | dbadb         | Query   |    0 | NULL                            | show full processlist                |
| 163615 | sup       | 192.168.2.213:2142   | dbadb         | Query   |   16 | Waiting for table metadata lock | select * from table1 where id=1      |
+——–+———–+———————-+—————+———+——+———————————+————————————–+

Session1 mysql> commit; 

第一个会话提交后,Session2、Session3也成功执行。看看Session3的select在哪个步骤需要等待:

Session3 mysql> show profile;
+——————————–+———–+
| Status                         | Duration  |
+——————————–+———–+
| starting                       |  0.000018 |
| Waiting for query cache lock   |  0.000003 |
| checking query cache for query |  0.000037 |
| checking permissions           |  0.000007 |
| Opening tables                 | 22.502591 |
| System lock                    |  0.000013 |
| Waiting for query cache lock   |  0.000024 |
| init                           |  0.000022 |
| optimizing                     |  0.000009 |
| statistics                     |  0.000011 |
| preparing                      |  0.000015 |
| executing                      |  0.000003 |
| Sending data                   |  0.000036 |
| end                            |  0.000009 |
| query end                      |  0.000005 |
| closing tables                 |  0.000006 |
| freeing items                  |  0.000007 |
| Waiting for query cache lock   |  0.000003 |
| freeing items                  |  0.000015 |
| Waiting for query cache lock   |  0.000002 |
| freeing items                  |  0.000002 |
| storing result in query cache  |  0.000004 |
| logging slow query             |  0.000002 |
| logging slow query             |  0.000028 |
| cleaning up                    |  0.000003 |
+——————————–+———–+

可以看到SELECT语句首先检查查询缓存,没有命中,然后在Opening tables阶段卡住。

这引出了另一个特点,基于上述场景,如果后续的SELECT能够在查询缓存中命中,那么不会被ALTER TABLE卡住,因为不需要Opening tables操作。命中缓存SELECT的SHOW PROFILE如下:

mysql>  show profile;
+——————————–+———-+
| Status                         | Duration |
+——————————–+———-+
| starting                       | 0.000017 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000004 |
| checking permissions           | 0.000007 |
| sending cached result to clien | 0.000011 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+——————————–+———-+
8 rows in set (0.00 sec)

遗留问题1.为什么第二次查询,记录没有发生变化,但结果集返回空?

这个问题在这个页面进行了讨论,确认原因是:

MySQL中ALTER操作实际后台是“创建新表”->”拷贝老表数据”->”RENAME新表成老表”。InnoDB引擎针对客户端的REPEATABLE READ隔离级别,采用的是基于多版本(Multi-version)功能的”一致性读”,即在事务中第一个查询时,获取的是当时版本的表的快照,而ALTER操作后实际是创建的新表,这个新表对于之前的快照而言是没有数据的,所以第二次查询新表基于快照的数据,返回空。在MySQL 5.6.6版本后,这样的第二个查询会返回Error信息“Table definition has changed, please retry transaction”。详细请看这里

遗留问题2.为什么没有锁等待超时??

我们知道InnoDB行锁的等待时间超过innodb_lock_wait_timeout就会报超时错误。这里的metadata lock一直等待着,为什么没有超时呢?其实metadata lock的超时设置是另一个系统变量lock_wait_timeout。

lock_wait_timeout控制着所有涉及到metadata lock的操作,包括DML and DDL,以及在表、视图、存储过程、存储函数上的操作,以及LOCK TABLES、FLUSH TABLES WITH READ LOCK、HANDLER等。

lock_wait_timeout的默认设置时一年,可以动态设置,根据实际场景进行调整。

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

分享到:
评论

相关推荐

    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

    - **LOCK TABLES下的操作**:当表被显式地锁定时,元数据锁的行为会有所不同。 - **非法的锁升级情况**:自动修复和自动发现功能在某些情况下可能导致锁升级的非法状态,需要注意。 - **预锁定(Pre-locking)**:在...

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

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

    MySQL运维-故障处理手册

    1. **Waiting for table metadata lock:** - **含义:**表明当前线程正在等待获取表的元数据锁。 - **解决方法:** - 检查是否有其他线程正在对同一张表进行结构修改(如ALTER TABLE)。 - 尽量减少对表结构的...

    mysql-常见问题,索引优化

    `STATE`列显示了每个线程当前的活动状态,如`Waiting for table metadata lock`、`Sending data`等,有助于诊断阻塞和性能问题。 17. 松散索引和紧凑索引 松散索引(也称为非聚簇索引)不包含主键数据,只存储索引...

    详细分析mysql MDL元数据锁

    当你看到 waiting for table metadata lock 时,那就是遇到MDL元数据锁了。本篇文章将会介绍MDL锁的产生与排查过程。 1.什么是MDL锁 MDL全称为metadata lock,即元数据锁。MDL锁主要作用是维护表元数据的数据一致性...

    mysql show processlist 显示mysql查询进程

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

    mdl——lock.docx

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

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

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

    MySQL8.0锁机制和事务

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

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

    使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。 这个状态表示的是,现在有一个线程正在表 t 上请求或者持有 MDL 写锁,把 select 语句堵住了。在第 6 篇文章《全局锁和表锁:给表加个...

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

    当查询长时间不返回时,可能是由于表级元数据锁(Metadata Lock, MDL)导致的。MDL锁用于保护表结构的安全,确保在结构变更期间数据的一致性。当一个线程试图获取MDL锁时,如果表已经被其他线程持有写锁,那么请求读...

Global site tag (gtag.js) - Google Analytics