MYSQL 锁:metadata lock

nihaogirl

　　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>　　+——+——+——+——-+

　　|>　　+——+——+——+——-+
　　|    1 |   34 |   45 |    80 |
　　+——+——+——+——-+
　　1 row in set (0.00 sec)

　　Session2 mysql>>　　Query OK, 4 rows affected (0.23 sec)
　　Records: 4  Duplicates: 0  Warnings: 0

　　Session1 mysql> select * from table1 where>　　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>　　+——+——+——+——-+

　　|>　　+——+——+——+——-+
　　|    1 |   34 |   45 |    80 |
　　+——+——+——+——-+
　　1 row in set (0.00 sec)

　　Session2 mysql>>　　这个语句一直等待。
　　遗留问题2：为什么没有锁等待超时？？
　　我们再开一个会话查看当前状况：
　　Session3 mysql>show full processlist;
　　+——–+———–+———————-+—————+———+——+———————————+————————————–+

　　|>　　+——–+———–+———————-+—————+———+——+———————————+————————————–+
　　| 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 |>　　| 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>>　　此步一直处于等待状态时，新会话中查询table1。

　　Session3 mysql> select * from table1 where>　　Session4 mysql>show full processlist;
　　+——–+———–+———————-+—————+———+——+———————————+————————————–+

　　|>　　+——–+———–+———————-+—————+———+——+———————————+————————————–+
　　| 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 |>　　| 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>　　+——–+———–+———————-+—————+———+——+———————————+————————————–+
　　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的默认设置时一年，可以动态设置，根据实际场景进行调整。