出现的错误:
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: 1
- trx_last_foreign_key_error: NULL
- trx_adaptive_hash_latched: 0
- trx_adaptive_hash_timeout: 10000
- trx_is_read_only: 0
- trx_autocommit_non_locking: 0
- 1 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 语句了
Linux 公社的 RSS 地址: https://www.linuxidc.com/rssFeed.aspx
来源: http://www.linuxidc.com/Linux/2018-06/152719.htm