核心数据库关键问题回顾与思考.doc

? ? ? ? ? ? ? ? 核心数据库关键问题回顾与思考 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 摘要： 踩坑不可怕，而避免重复踩坑，是以为程序员的基本修养，本文将抽取过去几个常见而严重的mysql问题进行分析，并给出深度解答。藉以大家思考写出合格的程序。 一. 问题回顾和思考 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.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 Amysql> insert into yetest2 select * from yetest1;# SESSION Bmysql> alter table yetest2 add yeColumn int; //等待SE