mysql 5.7 線程阻塞處理

標籤：update   erro   dap   rom   5.7   key   repeat   gbk   table   

出現的錯誤：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

解決辦法：查看sleep的進程

mysql> show full processlist;+-------+----------+-------------------+----------+---------+-------+----------+-----------------------------------------------------------------------------+| Id    | User     | Host              | db       | Command | Time  | State    | Info                                                                        |+-------+----------+-------------------+----------+---------+-------+----------+-----------------------------------------------------------------------------+| 32579 | official | 127.0.0.1:42690   | official | Sleep   | 20926 |          | NULL                                                                        || 32715 | erp      | 127.0.0.1:2078    | NULL     | Sleep   |  4570 |          | NULL                                                                        || 32716 | erp      | 127.0.0.1:2079    | erp      | Sleep   |  3590 |          | NULL                                                                        || 32717 | erp      | 127.0.0.1:2080    | erp      | Sleep   |  3577 |          | NULL                                                                        || 32732 | erp      | 127.0.0.1:2081    | erp      | Sleep   |  3590 |          | NULL                                                                        || 32733 | erp      | 127.0.0.1:2082    | NULL     | Sleep   |  1412 |          | NULL                                                                        || 32734 | erp      | 127.0.0.1:2083    | erp      | Sleep   |  1160 |          | NULL                                                                        || 32735 | erp      | 127.0.0.1:2084    | erp      | Sleep   |   985 |          | NULL                                                                        || 32736 | erp      | 127.0.0.1:2085    | erp      | Sleep   |  1160 |          | NULL                                                                        || 32739 | erp      | 127.0.0.1:2088    | erp      | Sleep   |   695 |          | NULL                                                                        || 32740 | erp      | 127.0.0.1:2089    | erp      | Sleep   |   768 |          | NULL                                                                        || 32741 | erp      | 127.0.0.1:2090    | erp      | Sleep   |   688 |          | NULL                                                                        || 32742 | erp      | 127.0.0.1:2091    | erp      | Sleep   |   686 |          | NULL                                                                        || 32743 | erp      | 127.0.0.1:2092    | erp      | Sleep   |   687 |          | NULL                                                                        || 32745 | erp      | 127.0.0.1:2093    | erp      | Sleep   |   686 |          | NULL                                                                        || 32746 | erp      | 127.0.0.1:2094    | erp      | Sleep   |   686 |          | NULL                                                                        || 32763 | root     | localhost         | erp      | Query   |     0 | starting | show full processlist                                                       || 32765 | erp      | 127.0.0.1:58216   | erp      | Sleep   |    18 |          | NULL                                                                        || 32766 | erp      | 127.0.0.1:58218   | erp      | Sleep   |    41 |          | NULL                                                                        || 32767 | erp      | 127.0.0.1:58220   | erp      | Sleep   |    41 |          | NULL                                                                        || 32768 | erp      | 127.0.0.1:58222   | erp      | Sleep   |    41 |          | NULL                                                                        || 32769 | erp      | 127.0.0.1:58224   | erp      | Sleep   |     2 |          | NULL                                                                        || 32770 | erp      | 127.0.0.1:2106    | NULL     | Sleep   |   153 |          | NULL                                                                        || 32771 | erp      | 127.0.0.1:2107    | erp      | Sleep   |   148 |          | NULL                                                                        || 32772 | erp      | 127.0.0.1:2108    | erp      | Query   |    18 | updating | UPDATE `erp_user` SET `pwd`=‘OyUHgt21gTP2/5uFgbKZtq==‘ WHERE (`id`=‘10113‘) |+-------+----------+-------------------+----------+---------+-------+----------+-----------------------------------------------------------------------------+

沒有看到正在執行的慢SQL記錄線程，再去查看innodb的事務表INNODB_TRX，看下裡面是否有正在鎖定的事務線程，看看ID是否在show full processlist裡面的sleep線程中，如果是，就證明這個sleep的線程事務一直沒有commit 或者 rollback而是卡住了，我們需要手動kill掉。

mysql> SELECT * FROM information_schema.INNODB_TRX\G;*************************** 1. row ***************************                    trx_id: 20342                 trx_state: RUNNING               trx_started: 2018-01-29 16:48:13     trx_requested_lock_id: NULL          trx_wait_started: NULL                trx_weight: 4       trx_mysql_thread_id: 32735                 trx_query: NULL       trx_operation_state: NULL         trx_tables_in_use: 0         trx_tables_locked: 1          trx_lock_structs: 2     trx_lock_memory_bytes: 1136           trx_rows_locked: 1         trx_rows_modified: 2   trx_concurrency_tickets: 0       trx_isolation_level: REPEATABLE READ         trx_unique_checks: 1    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000          trx_is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.00 sec)ERROR: No query specified

看到有這條 32735 的sql，kill掉，執行kill 32735;

kill 32735;

然後再去查詢INNODB_TRX表，就沒有阻塞的事務sleep線程存在了，如下所示：

mysql> SELECT * FROM information_schema.INNODB_TRX\G;Empty set (0.00 sec)

現在可以正常執行 sql 語句了

mysql 5.7 線程阻塞處理