背景：今天在用python往MySQL中写入数据时，出现程序启动后运行很久，发现没动静，一去客户端查询数据库，也发现查询语句出现停滞状态，怀疑是表被锁了，下面是这次问题的解决过程。
问题现象：
等了很长时间，实在受不了，直接Ctrl+C退出。
第一步：先查看MySQL的进程
执行show processlist;
3306[bi]>show processlist;
+------+------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 1520 | lrr2 | 10.110.17.81:51932 | bi | Sleep | 2118 | | NULL |
| 1524 | lrr2 | localhost | bi | Query | 0 | starting | show processlist |
| 1619 | lrr2 | 10.110.17.81:52158 | bi | Prepare | 1530 | Waiting for table metadata lock | select * from `pmc_project_milestone_info` |
| 1624 | lrr2 | 10.110.17.81:52172 | bi | Sleep | 3086 | | NULL |
| 1627 | lrr2 | 10.110.17.81:52180 | bi | Sleep | 2710 | | NULL |
| 1632 | lrr2 | 10.66.9.178:59670 | bi | Query | 1741 | Waiting for table metadata lock | DROP TABLE IF EXISTS pmc_project_milestone_info |
| 1633 | lrr2 | 10.66.9.178:59695 | bi | Query | 1699 | Waiting for table metadata lock | DROP TABLE IF EXISTS pmc_project_milestone_info |
| 1634 | lrr2 | 10.66.9.178:59757 | bi | Query | 1618 | Waiting for table metadata lock | DROP TABLE IF EXISTS pmc_project_milestone_info |
| 1635 | lrr2 | 10.66.9.178:59821 | bi | Query | 1575 | Waiting for table metadata lock | DROP TABLE IF EXISTS pmc_project_milestone_info |
| 1636 | lrr2 | 10.110.17.81:52196 | bi | Sleep | 1499 | | NULL |
| 1645 | lrr2 | 10.66.9.178:61232 | bi | Query | 382 | Waiting for table metadata lock | CREATE TABLE `pmc_project_milestone_info` (
`ERP项目编码` varchar(255) CHARACTER SET gbk COLL |
| 1647 | lrr2 | 10.66.9.178:61397 | bi | Sleep | 58 | | NULL |
| 1648 | lrr2 | 10.66.9.178:61411 | NULL | Sleep | 58 | | NULL |
+------+------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
发现好几个Waiting for table metadata lock。拿这个去百度一下。说是因为alter table 语句无法获取到metadata 独占锁，所以会进行等待。
解决方法：kill 掉DDL所在的session。
如下图所示，kill掉所有的id：
3306[bi]>kill 1645;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1635;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1634;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1633;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1632;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1647;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1636;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1627;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1624;
Query OK, 0 rows affected (0.00 sec)
3306[bi]>kill 1619;
Query OK, 0 rows affected (0.00 sec)
最后一个kill不掉。所以只能留着最后一个。
3306[bi]>show processlist;
+------+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------+---------+------+----------+------------------+
| 1524 | lrr2 | localhost | bi | Query | 0 | starting | show processlist |
+------+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
3306[bi]>kill 1524;
ERROR 1317 (70100): Query execution was interrupted
到处问题解决了。重新查询时已经ok了。
3306[bi]>select * from pmc_project_milestone_info limit 5;
+-----------------+--------------+--------------+--------------------------------+-----------------+---------------------+---------------------+---------------------+---------------------+
| ERP项目编码 | 项目名称 | 项目等级 | 项目类型 | 里程碑名称 | 计划开始时间 | 计划结束时间 | 实际结束时间 | 实际开始时间 |
+-----------------+--------------+--------------+--------------------------------+-----------------+---------------------+---------------------+---------------------+---------------------+
| B16****70389 | NULL | D | 生产制造类项目 | 验收 | 2018-09-13 00:00:00 | 2018-11-16 00:00:00 | 2018-11-16 00:00:00 | 2018-09-13 00:00:00 |
| B16****70389 | NULL | D | 生产制造类项目 | 里程碑-1 | 2017-11-16 00:00:00 | 2018-09-12 00:00:00 | 2018-09-12 00:00:00 | 2017-11-16 00:00:00 |
| B16****10071 | NULL | D | 集采及简单贸易类项目 | 物资采购 | 2021-11-22 00:00:00 | 2022-08-30 00:00:00 | NULL | NULL |
| B16****10071 | NULL | D | 集采及简单贸易类项目 | 供货 | 2022-09-01 00:00:00 | 2022-10-31 00:00:00 | NULL | NULL |
| B16****10071 | NULL | D | 集采及简单贸易类项目 | 验收 | 2022-11-01 00:00:00 | 2022-11-14 00:00:00 | NULL | NULL |
+-----------------+--------------+--------------+--------------------------------+-----------------+---------------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)
也顺便记录一下网上的其他场景及解决方法。
参照文章：https://www.jb51.net/article/161086.htm
show processlist 也并不能解决所有的问题，如果场景一解决不了的，还得再想其他的办法：
mysql>select * from information_schema.innodb_trx\G
处理方法：通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.
这个我也尝试了一下：
3306[bi]>select * from performance_schema.events_statements_current;
+-----------+----------+--------------+----------------------------+--------------------------+---------------------+---------------------+------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+------------------------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME | SOURCE | TIMER_START | TIMER_END | TIMER_WAIT | LOCK_TIME | SQL_TEXT | DIGEST | DIGEST_TEXT | CURRENT_SCHEMA | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | ERRORS | WARNINGS | ROWS_AFFECTED | ROWS_SENT | ROWS_EXAMINED | CREATED_TMP_DISK_TABLES | CREATED_TMP_TABLES | SELECT_FULL_JOIN | SELECT_FULL_RANGE_JOIN | SELECT_RANGE | SELECT_RANGE_CHECK | SELECT_SCAN | SORT_MERGE_PASSES | SORT_RANGE | SORT_ROWS | SORT_SCAN | NO_INDEX_USED | NO_GOOD_INDEX_USED | NESTING_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_LEVEL |
+-----------+----------+--------------+----------------------------+--------------------------+---------------------+---------------------+------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+------------------------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+
| 1645 | 82 | NULL | statement/com/Prepare | socket_connection.cc:101 | 4389262368273860000 | 4391061786095640000 | 1799417821780000 | 0 | NULL | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1650 | 15 | 15 | statement/sql/set_option | socket_connection.cc:101 | 4387706186034607000 | 4387706186054931000 | 20324000 | 0 | SET net_write_timeout=60 | fc120495994cc16ee15385519d012596 | SET `net_write_timeout` = ? | bi | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1653 | 14 | 14 | statement/sql/show_fields | socket_connection.cc:101 | 4388082513267602000 | 4388082513556628000 | 289026000 | 82000000 | SHOW FULL COLUMNS FROM `pmc_project_state_info` FROM `bi` LIKE '%' | 37a884f881e789a4ef41324103690536 | SHOW FULL FIELDS FROM `pmc_project_state_info` FROM `bi` LIKE ? | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 33 | 33 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | 0 |
| 1658 | 2 | NULL | statement/sql/drop_table | socket_connection.cc:101 | 4389050693276868000 | 4391061786154659000 | 2011092877791000 | 0 | DROP TABLE IF EXISTS pmc_project_milestone_info | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1659 | 2 | NULL | statement/sql/drop_table | socket_connection.cc:101 | 4389093584602721000 | 4391061786158577000 | 1968201555856000 | 0 | DROP TABLE IF EXISTS pmc_project_milestone_info | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1660 | 2 | NULL | statement/sql/drop_table | socket_connection.cc:101 | 4389174473736468000 | 4391061786161537000 | 1887312425069000 | 0 | DROP TABLE IF EXISTS pmc_project_milestone_info | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1661 | 2 | NULL | statement/sql/drop_table | socket_connection.cc:101 | 4389217530037390000 | 4391061786164953000 | 1844256127563000 | 0 | DROP TABLE IF EXISTS pmc_project_milestone_info | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1662 | 11 | 11 | statement/sql/set_option | socket_connection.cc:101 | 4389292743750344000 | 4389292743778441000 | 28097000 | 0 | SET autocommit=1 | 54b0116d21e24f638f94bd799148b397 | SET `autocommit` = ? | bi | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1671 | 8 | NULL | statement/sql/create_table | socket_connection.cc:101 | 4390409721224050000 | 4391061786172642000 | 652064948592000 | 0 | CREATE TABLE `pmc_project_milestone_info` (
`ERP项目编码` varchar(255) CHARACTER SET gbk COLLATE gbk_bin NULL DEFAULT NULL COMMENT 'ERP项目编码',
`项目名称` varchar(255) CHARACTER SET gbk COLLATE gbk_bin NULL DEFAULT NULL COMMENT '项目名称',
`项目等级` varchar(255) CHARACTER SET gbk COLLATE gbk_bin NULL DEFAULT NULL COMMENT '项目等级',
`项目类型` varchar(255) CHARACTER SET gbk COLLATE gbk_bin NULL DEFAULT NULL COMMENT '项目类型',
`里程碑名称` varchar(255) CHARACTER SET gbk COLLATE gbk_bin NULL DEFAULT NULL COMMENT '里程碑名称',
`计划开始时间` datetime(0) NULL DEFAULT NULL COMMENT '计划开始时间',
`计划结束时间` datetime(0) NULL DEFAULT NULL COMMENT '计划结束时间',
`实际结束时间` datetime(0) NULL DEFAULT NULL COMMENT '实际开始时间',
`实际开始时间` datetime(0) NULL DEFAULT NULL COMMENT '实际结束时间'
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic COMMENT='里程碑表' | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1673 | 8 | 8 | statement/sql/change_db | socket_connection.cc:101 | 4390733819649714000 | 4390733819680389000 | 30675000 | 0 | use `bi` | 7346fbd8793648d710d1a7df7490f09d | USE `bi` | bi | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1674 | 1 | 1 | statement/sql/set_option | socket_connection.cc:101 | 4390733658764177000 | 4390733658833996000 | 69819000 | 0 | /*!40101 set @@session.wait_timeout=28800 */ | 116d696ebecc3e22dd765fa4d2537f47 | SET @@SESSION . `wait_timeout` = ? | NULL | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 |
| 1547 | 218 | 218 | statement/com/Execute | socket_connection.cc:101 | 4388674374325941000 | 4388674374526509000 | 200568000 | 13000000 | NULL | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 100 | 100 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | 0 |
| 1550 | 149 | NULL | statement/sql/select | socket_connection.cc:101 | 4391061785862263000 | 4391061786196094000 | 333831000 | 152000000 | select * from performance_schema.events_statements_current | NULL | NULL | bi | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | 0 |
+-----------+----------+--------------+----------------------------+--------------------------+---------------------+---------------------+------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+------------------------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+
13 rows in set (0.00 sec)
但是我kill掉场景一的所有id后，问题就解决了，所以没涉及到场景3了。
其他的还可以参照：https://www.jb51.net/article/145599.htm
https://www.jb51.net/article/131383.htm
等