踩坑不可怕,而避免重复踩坑,是以为程序员的基本修养,本文将抽取过去几个常见而严重的mysql问题进行分析,并给出深度解答。藉以大家思考写出合格的程序。
1.1 SQLException: Lock wait timeout exceeded; try restarting transaction,please rollback!
再发生这样的错误时,别很自豪的说数据库出问题了,呼叫DBA ...(uat多次出现)
第一个问题目前发生的原因有:
1.2 条件查询卡住了,怎么重跑都通不过,怎么办,急死人了(迁移后比对实际出现)。
Truncate table过程中CTRL +C 终止了。 有分片上存在truncate 事务一直存在,进而对该表的所有操作均会超时。
1.3 查询卡住,更新卡住...殊不知,你前面的Alter table都没成功......
DBProxy的问题不在此文讨论,查询事务没有正常提交而占据共享锁时,同样会造成alter table获取不到MDL锁,而造成一直等待。 提示为: Waiting fortable metadata lock (show processlist中可查)。
为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此从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,是非常危险的。
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:
很多时候发生数据库报错时,不一定就是数据库的问题,不一定非得急着呼叫数据库人员解决。 我们要形成这样一种意识,我们不只是写应用的,我们是写金融系统的,我们理应具备一定的问题排查解决能力。如果只做问题的搬运工,这是否也是对我们自身水平的一种质疑? 只有当大家提的问题都很有水平了,卡中心的系统才可能稳如泰山。
当然, mysql 本身和 oracle 对比存在不少的缺陷和不完善之处。而分布式的引入又进一步引入了系统的复杂性。 这也对我们运维人员的水平提出了更大的考验。运维人员同样应该踏踏实实从实践中学习和思考,遇到问题才能从容不乱,提出行之有效的解决方法。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞6
添加新评论0 条评论