2021年04月25日阅读 85

几个常见而严重的 MySQL 问题分析

1 问题回顾和思考

1.1 SQLException: Lock wait timeout exceeded; try restarting transaction,please rollback!

再发生这样的错误时，别很自豪的说数据库出问题了，呼叫DBA ...（uat多次出现）

第一个问题目前发生的原因有：

磁盘空间满，事务无法提交成功。(磁盘满是一个很危险的操作，会引起binlog写坏，备库无法同步进而需要恢复备库)
更新事务未正常提交而产生排他锁，造成其他更新事务一直获取不到该锁而事务超时。

1.2 条件查询卡住了，怎么重跑都通不过，怎么办，急死人了(迁移后比对实际出现)。

Truncate table过程中CTRL +C 终止了。有分片上存在truncate 事务一直存在，进而对该表的所有操作均会超时。

1.3 查询卡住，更新卡住...殊不知，你前面的Alter table都没成功......

DBProxy的问题不在此文讨论，查询事务没有正常提交而占据共享锁时，同样会造成alter table获取不到MDL锁，而造成一直等待。提示为：Waiting fortable metadata lock (show processlist中可查)。

2 原理详细分析

2.1 什么是MDL锁?

为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。

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

所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadata lock wait 。例如下面的这种情形：

若没有MDL锁的保护，则事务2可以直接执行DDL操作，并且导致事务1出错，5.1版本即是如此。5.5版本加入MDL锁就在于保护这种情况的发生，由于事务1开启了查询，那么获得了MDL锁，锁的模式为SHARED_READ，事务2要执行DDL，则需获得EXCLUSIVE锁，两者互斥，所以事务2需要等待。

注：支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

MySQL的设计：在设置的autocommit=0；read_commited的时候，无论session的第一条语句是select还是dml，都开始一个事务，然后直到commit，所持有的MDL锁也一直维持到commit结束。

Oracle的设计：在session的第一条更新语句发起时，才创建transaction，在读多的系统上，减少了阻塞的发生可能性。特别是在开发人员发起select语句时，认为没有更新，就不再commit。但在MySQL上，发起select语句，而忘记commit，是非常危险的。

2.2 常见MDL锁场景和详细解释

1）当前有执行DML操作时执行ALTRE操作

2）当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住

3）显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住

4）表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住

详细测试解释说明：

1）当前有执行DML操作时执行ALTRE操作

# SESSION A
mysql> insert into yetest2 select * from yetest1;
# SESSION B
mysql> alter table yetest2 add yeColumn int;   //等待SESSION A执行完;
# SESSION C
mysql> show processlist;
+-----+------+-------+------+--------------+--------+-----------------------------------------+------------
| Id  | User | Host| db  | Command | Time | State                       | Info                              |+-----+------+------+----- +--------------+--------+------------------------------------------+------------
| 267 | root | localhost | sbtest | Query   |    7 | Sending data                    | insert into yetest2 select * from yetest1 |
| 271 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | alter table  yetest2 add yeColumn int         |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)
 
# SESSION D
mysql> select * from yetest2 limit 10;    //等待元数据锁;
# SESSION E
mysql> show processlist;
+-----+------+-------+------+--------------+--------+-----------------------------------------+------------
| Id  | User | Host| db  | Command | Time | State                       | Info                              |+-----+------+------+----- +--------------+--------+------------------------------------------+------------
| 267 | root | localhost | sbtest | Query   |   20 | Sending data                    | insert into sbtest2 select * from sbtest1 |
| 271 | root | localhost | sbtest | Query   |   13 | Waiting for table metadata lock | alter table yetest2 add  yeColumn int         |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          |
| 308 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | select * from yetest2 limit 10            |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
4 rows in set (0.00 sec)
复制代码

由于事务1开启了查询，那么获得了MDL锁，锁的模式为SHARED_READ，事务2要执行DDL，则需获得EXCLUSIVE锁，两者互斥，所以事务2需要等待。查询都能卡住，是不是很郁闷？我们上次迁移就是这种场景，truncate table属于DDL，会lock table metadata，甚至可以可以由锁表升级到锁库。

3）显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住




    
# SESSION A
mysql> begin;
mysql> select * from test2;
# SESSION B
mysql> alter table test2 add test3 int;   //等待SESSION A执行完;
# SESSION C
mysql> show processlist;
+-----+------+-------+------+--------------+--------+-----------------------------------------+------------
| Id  | User | Host| db  | Command | Time | State                       | Info                              |+-----+------+------+----- +--------------+--------+------------------------------------------+------------
| 267 | root | localhost | sbtest | Sleep   |   36 |                                 | NULL                              |
| 271 | root | localhost | sbtest | Query   |   30 | Waiting for table metadata lock | alter table test2 add test3 int |
| 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                  |
+-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
3 rows in set (0.00 sec)
复制代码

4 ) 表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住

# SESSION A
mysql> begin;
mysql> select error from  test2; 
ERROR 1054 (42S22): Unknown column 'error' in 'field list'
# SESSION B
mysql> alter table test2 add test3 int;    //等待SESSION A提交或回滚;
# SESSION C
mysql> show processlist;
+-----+------+-------+------+--------------+--------+-----------------------------------------+------------
| Id  | User | Host| db  | Command | Time | State                       | Info                              |+-----+------+------+----- +--------------+--------+------------------------------------------+------------
| 267 | root | local | test |  Sleep   |    7 |                             |NULL              
| 271 | root | local | test |  Query   |    3 | Waiting for table metadata lock | alter table test2 add test3 int |
| 272 | root | local | NULL| Query   |    0 | starting                      | show processlist             
| 311 | root | local | NULL | Sleep   |  413 |                            | NULL           
+-----+------+-----------+--------+---------+------+-------------------------------------------+--------------
4 rows in set (0.00 sec)
# SESSION D
mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)
复制代码

其实SESSION A中的事务并未开启，但是由于select获取表元数据的语句，语法上是有效的，虽然执行失败了，但是任然不会释放元数据锁，故而导致SESSION B的alter动作被阻塞。

通过SESSION D查看当前打开事务时，你会发现没有，从而找不到原因。所以当出现这种场景时，如何判断是哪个进程导致的呢，我们可以尝试查看表performance_schema. events_statements_current，分析进程状态来进行判断。

mysql> select * from performance_schema. events_statements_current\G
*************************** 1. row ***************************
              THREAD_ID: 293
               EVENT_ID: 32
           END_EVENT_ID: 32
             EVENT_NAME: statement/sql/select
                 SOURCE: socket_connection.cc:211
            TIMER_START: 212721717099954000
              TIMER_END: 212721717213807000
             TIMER_WAIT: 113853000
              LOCK_TIME: 0
               SQL_TEXT: select error from test2
                 DIGEST: 0bbb2d5d1be45e77debea68111264885
            DIGEST_TEXT: SELECT ERROR FROM `test2` 
         CURRENT_SCHEMA: test
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 1054
      RETURNED_SQLSTATE: 42S22
           MESSAGE_TEXT: Unknown column 'error' in 'field list'
                 ERRORS: 1
复制代码

然后找到其sid, kill掉该session，也可以kill掉DDL所在的session解决可以解决此问题。

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

出现以上几种情况时，这个时候如果进行如下操作就会引起MDL：

1.创建、删除索引。

2.修改表结构。

3.表维护操作（optimize table、repair table等）。

4.删除表。

5.获取表上表级写锁 (lock table tab_name write)。

三. 总结

很多时候发生数据库报错时，不一定就是数据库的问题，不一定非得急着呼叫数据库人员解决。我们要形成这样一种意识，我们不只是写应用的，我们是写金融系统的，我们理应具备一定的问题排查解决能力。如果只做问题的搬运工，这是否也是对我们自身水平的一种质疑？只有当大家提的问题都很有水平了，卡中心的系统才可能稳如泰山。

当然，MySQL本身和Oracle对比存在不少的缺陷和不完善之处。而分布式的引入又进一步引入了系统的复杂性。这也对我们运维人员的水平提出了更大的考验。运维人员同样应该踏踏实实从实践中学习和思考，遇到问题才能从容不乱，提出行之有效的解决方法。