MySQL表不能修改、删除等操作，卡死、锁死情况的处理办法

 

show full processlist;

 //列出当前的操作process，看到很多处于waiting的process，说明已经有卡住的proces。

这里我们看到是sending data的这条语句卡住了mysql

+---------+--------+-----------+-------+------------------+---------+-------------------------------------+--------------------------------------+-------------+----------------------+-----------+
| Id      | User   | Host      | db    | Command          | Time    | State                               | Info                                 | Memory_used | Memory_used_by_query | CPU_time  |
+---------+--------+-----------+-------+------------------+---------+-------------------------------------+--------------------------------------+-------------+----------------------+-----------+
|      28 | rdsRepl| xxx:51284 | NULL  | Binlog Dump GTID | 1220403 | Master has sent all binlog to slave;| NULL                                 |       18117 |                 8208 |    239225 |
| 1676492 | root   | xxx:9183  | sync  | Sleep            |    5394 | (这条上面的waiting for more updates)| NULL                                 |      895362 |                 8208 |  32741806 |
| 1688104 | root   | xxx:11900 | sync  | Sleep            |    3766 |                                     | NULL                                 |      268480 |                 8208 | 155952228 |
| 1689762 | root   | xxx:12057 | xtemp | Query            |    1116 | Waiting for table metadata lock     | SHOW TABLE STATUS                    |      813027 |                11024 |   5439530 |
| 1690466 | root   | xxx:12632 | xtemp | Query            |     950 | Waiting for table metadata lock     | SELECT TABLE_NAME, CHECK_OPTION, IS_U|      114628 |                 9776 |   5811522 |
| 1693651 | root   | xxx:14234 | sync  | Query            |    1338 | Sending data                        | CREATE TABLE xtemp.yxy_transfer AS  S|     1338135 |                15384 |   2542049 |
| 1693702 | root   | xxx:14342 | sync  | Query            |    1302 | Waiting for table metadata lock     | DROP TABLE IF EXISTS xtemp.yxy_transf|       17149 |                 8208 |   2428293 |
| 1694006 | root   | xxx:14584 | sync  | Query            |    1085 | Waiting for table metadata lock     | DROP TABLE IF EXISTS xtemp.yxy_transf|       17149 |                 8208 |   2397726 |
| 1694108 | root   | xxx:14594 | xtemp | Query            |     936 | Waiting for table metadata lock     | show create table xtemp.yxy_transfer |       37856 |                 8208 |    953411 |
| 1694417 | root   | xxx:14771 | xtemp | Query            |     762 | Waiting for table metadata lock     | SELECT TABLE_NAME, CHECK_OPTION, IS_U|      111067 |                 9776 |    892068 |
| 1694515 | root   | xxx:14869 | xtemp | Query            |     683 | Waiting for table metadata lock     | SELECT TABLE_NAME, CHECK_OPTION, IS_U|      127905 |                 9776 |    831436 |
| 1694540 | root   | xxx:14875 | xtemp | Query            |     651 | Waiting for table metadata lock     | DROP TABLE IF EXISTS xtemp.yxy_transf|       16638 |                 8208 |    138945 |
| 1694650 | root   | xxx:1032  | xtemp | Query            |     572 | Waiting for table metadata lock     | SELECT TABLE_NAME, CHECK_OPTION, IS_U|      117220 |                 9776 |    937266 |
| 1694666 | root   | xxx:1033  | xtemp | Query            |       0 | starting                            | show full processlist                |       16470 |                 8208 |    973631 |
+---------+--------+-----------+-------+------------------+---------+-------------------------------------+--------------------------------------+-------------+----------------------+-----------+

 

再执行：

kill processid;  

kill 1693651;

//processid表示process的id，比如kill 3301，就会将id为3301的process杀死。

 

+---------+---------+------+-------+------------------+---------+---------------------------------------------------------------+-----------------------+-------------+----------------------+-----------+
| Id      | User    | Host | db    | Command          | Time    | State                                                         | Info                  | Memory_used | Memory_used_by_query | CPU_time  |
+---------+---------+------+-------+------------------+---------+---------------------------------------------------------------+-----------------------+-------------+----------------------+-----------+
|      28 | rdsRepl |xxx   | NULL  | Binlog Dump GTID | 1220453 | Master has sent all binlog to slave; waiting for more updates | NULL                  |       18117 |                 8208 |    239225 |
| 1676492 | root    |xxx   | sync  | Sleep            |    5444 |                                                               | NULL                  |      895362 |                 8208 |  32741806 |
| 1688104 | root    |xxx   | sync  | Sleep            |    3816 |                                                               | NULL                  |      268480 |                 8208 | 155952228 |
| 1689762 | root    |xxx   | xtemp | Sleep            |    1166 |                                                               | NULL                  |      428709 |                 8208 |   6319722 |
| 1690466 | root    |xxx   | xtemp | Sleep            |    1000 |                                                               | NULL                  |       20046 |                 8208 |   6517271 |
| 1694108 | root    |xxx   | xtemp | Sleep            |     986 |                                                               | NULL                  |       39912 |                 8208 |   1055550 |
| 1694417 | root    |xxx   | xtemp | Sleep            |     812 |                                                               | NULL                  |       16485 |                 8208 |   1390755 |
| 1694515 | root    |xxx   | xtemp | Sleep            |     733 |                                                               | NULL                  |       27155 |                 8208 |   1320086 |
| 1694540 | root    |xxx   | xtemp | Sleep            |     701 |                                                               | NULL                  |       85811 |                 8208 |  16689270 |
| 1694650 | root    |xxx   | xtemp | Sleep            |     622 |                                                               | NULL                  |       16470 |                 8208 |   1428922 |
| 1694666 | root    |xxx   | xtemp | Query            |       0 | starting                                                      | show full processlist |       16470 |                 8208 |   1351151 |
+---------+---------+------+-------+------------------+---------+---------------------------------------------------------------+-----------------------+-------------+----------------------+-----------+