一招解决MySQL中DDL语句被阻塞的问题场景

数据运维 2024-01-24 贤蛋大眼萌手机阅读

mysql> use test; Database changed mysql> CREATE TABLE `test` ( -> `id` int(11) AUTO_INCREMENT PRIMARY KEY, -> `name` varchar(10) -> ); Query OK, 0 rows affected (0.01 sec) # 插入数据 mysql> insert into test values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'); Query OK, 1 row affected (0.01 sec) mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ddd | +----+------+ 4 rows in set (0.00 sec) # 模拟元数据锁阻塞 # 会话 1 mysql> lock tables test read; Query OK, 0 rows affected (0.00 sec) # 会话 2 mysql> alter table test add c1 varchar(25); 阻塞中 # 会话 3 mysql> show processlist; +-------+-------------+---------------------+------+------------------+--------+---------------------------------------------------------------+-------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+-------------+---------------------+------+------------------+--------+---------------------------------------------------------------+-------------------------------------+ | 1 | universe_op | 127.0.0.1:28904 | NULL | Sleep | 12 | | NULL | | 2 | universe_op | 127.0.0.1:28912 | NULL | Sleep | 12 | | NULL | | 5752 | universe_op | 10.186.64.180:51808 | NULL | Binlog Dump GTID | 605454 | Master has sent all binlog to slave; waiting for more updates | NULL | | 28452 | root | 10.186.65.110:10756 | test | Sleep | 73 | | NULL | | 28454 | root | 10.186.64.180:45674 | test | Query | 7 | Waiting for table metadata lock | alter table test add c1 varchar(25) | | 28497 | root | 10.186.64.180:47026 | test | Query | 0 | starting | show processlist | +-------+-------------+---------------------+------+------------------+--------+---------------------------------------------------------------+-------------------------------------+