mysql 5.6 5.7引入了online ddl操作,今天研发在线添加了一个字段,导致了一个比较大的故障,大量操作 执行不了,等待信息是
Waiting for table metadata lock,当时是kill 了mysql解决的。怀疑是因为有事务没提交,阻塞了ddl操作,而ddl因为需要修改元数据,需要短暂的锁表,阻塞了后续所有关于该表的操作,但因为事务没有提交,ddl修改不了元数据,导致后续操作都执行不了。从而造成较大的故障。
测试过程如下
set @@autocommit=0;
update test set rule_id=2 where id=1;
session 1
root@passport_phrase 01:06:59>set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@passport_phrase 01:07:21>update test set rule_id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 2
mysql> use passport_phrase
Database changed
mysql> alter table `test` modify column `phrase` varchar(1100);
session 3
mysql> use passport_phrase
Database changed
mysql> select * from test where id=3;
session 4:
| 1475570 | passport_phrase | 11.162.156.241:35362 | passport_phrase | Sleep | 700 | | NULL |
| 1483369 | root | 127.0.0.1:41660 | passport_phrase | Query | 77 | Waiting for table metadata lock | alter table `test` modify column `phrase` varchar(1100) |
| 1483370 | root | 127.0.0.1:41917 | passport_phrase | Sleep | 124 | | NULL |
| 1483384 | root | 127.0.0.1:47256 | passport_phrase | Query | 30 | Waiting for table metadata lock | select * from test where id=3 |
| 1483391 | root | 127.0.0.1:49715 | NULL | Query | 0 | starting | show full processlist |
+---------+-----------------+----------------------+--------------------+-------------+----------+---------------------------------------------------------------+-----------------------------------------------------------+
Waiting for table metadata lock,当时是kill 了mysql解决的。怀疑是因为有事务没提交,阻塞了ddl操作,而ddl因为需要修改元数据,需要短暂的锁表,阻塞了后续所有关于该表的操作,但因为事务没有提交,ddl修改不了元数据,导致后续操作都执行不了。从而造成较大的故障。
测试过程如下
set @@autocommit=0;
update test set rule_id=2 where id=1;
session 1
root@passport_phrase 01:06:59>set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@passport_phrase 01:07:21>update test set rule_id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 2
mysql> use passport_phrase
Database changed
mysql> alter table `test` modify column `phrase` varchar(1100);
session 3
mysql> use passport_phrase
Database changed
mysql> select * from test where id=3;
session 4:
| 1475570 | passport_phrase | 11.162.156.241:35362 | passport_phrase | Sleep | 700 | | NULL |
| 1483369 | root | 127.0.0.1:41660 | passport_phrase | Query | 77 | Waiting for table metadata lock | alter table `test` modify column `phrase` varchar(1100) |
| 1483370 | root | 127.0.0.1:41917 | passport_phrase | Sleep | 124 | | NULL |
| 1483384 | root | 127.0.0.1:47256 | passport_phrase | Query | 30 | Waiting for table metadata lock | select * from test where id=3 |
| 1483391 | root | 127.0.0.1:49715 | NULL | Query | 0 | starting | show full processlist |
+---------+-----------------+----------------------+--------------------+-------------+----------+---------------------------------------------------------------+-----------------------------------------------------------+
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29033984/viewspace-2152192/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29033984/viewspace-2152192/