mysql 线程阻塞 定位_MySQL 5.7中如何定位DDL被阻塞的问题

测试场景

下面结合一个简单的Demo，来看看在MySQL 5.7中如何定位DDL操作的阻塞问题。

session1> begin;

Query OK, 0 rows affected (0.00 sec)

session1> delete from slowtech.t1 where id=2;

Query OK, 1 row affected (0.00 sec)

session1> select * from slowtech.t1;

+------+------+

| id  | name |

+------+------+

|    1 | a    |

+------+------+

1 row in set (0.00 sec)

session1> update slowtech.t1 set name='c' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

session2> alter table slowtech.t1 add c1 int; ##被阻塞

session3> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

| Id | User | Host      | db  | Command | Time | State                          | Info                              |

+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

|  2 | root | localhost | NULL | Sleep  |  51 |                                | NULL                              |

|  3 | root | localhost | NULL | Query  |    0 | starting                        | show processlist                  |

|  4 | root | localhost | NULL | Query  |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |

+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

3 rows in set (0.00 sec)

session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;

+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+

| object_type | object_schema      | object_name    | lock_type          | lock_duration | lock_status | owner_thread_id |

+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+

| TABLE      | slowtech          | t1            | SHARED_WRITE        | TRANSACTIO