解决mysql中出现的Err] 1205 - Lock wait timeout exceeded; try restarting transaction问题

解决问题

1：mysql 命令行进入相应库

先当前库的线程情况：

输入命令：show full processlist; 显示如下

show full processlist;

显示如下：

+--------+------+-------------------+-------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------+----------+
| Id     | User | Host              | db    | Command | Time | State        | Info                                                                                                           | Progress |
+--------+------+-------------------+-------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------+----------+
| 156117 | root | jackfull-pc:51416 | sipai | Query   | 2265 | Sending data | UPDATE 01_new_auction_info nai,last_status ls SET
ls.house_type = nai.house_type WHERE ls.bid_id = nai.bid_id |    0.000 |
| 156133 | root | jackfull-pc:54629 | NULL  | Sleep   |  700 |              | NULL                                                                                                           |    0.000 |
| 156134 | root | jackfull-pc:54630 | sipai | Sleep   |  692 |              | NULL                                                                                                           |    0.000 |
| 156135 | root | jackfull-pc:54633 | sipai | Sleep   |  700 |              | NULL                                                                                                           |    0.000 |
| 156136 | root | jackfull-pc:54634 | sipai | Sleep   |  700 |              | NULL                                                                                                           |    0.000 |
| 156137 | root | jackfull-pc:54636 | sipai | Sleep   |  700 |              | NULL                                                                                                           |    0.000 |
| 156138 | root | jackfull-pc:54637 | sipai | Sleep   |  700 |              | NULL                                                                                                           |    0.000 |
| 156139 | root | jackfull-pc:54654 | sipai | Sleep   |  692 |              | NULL                                                                                                           |    0.000 |
| 156140 | root | jackfull-pc:54765 | sipai | Sleep   |  632 |              | NULL                                                                                                           |    0.000 |
| 156141 | root | jackfull-pc:54832 | sipai | Sleep   |  594 |              | NULL                                                                                                           |    0.000 |
| 156142 | root | jackfull-pc:54870 | sipai | Sleep   |  495 |              | NULL                                                                                                           |    0.000 |
| 156143 | root | jackfull-pc:55143 | sipai | Sleep   |  394 |              | NULL                                                                                                           |    0.000 |
| 156144 | root | localhost         | NULL  | Query   |    0 | init         | show full processlist                                                                                          |    0.000 |
+--------+------+-------------------+-------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------+----------+
13 rows in set (0.01 sec)

2：查询事务

查看innodb的事务表

SELECT * FROM information_schema.INNODB_TRX\G;

显示如下：

MariaDB [(none)]> SELECT * FROM information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 9523412
                 trx_state: RUNNING
               trx_started: 2018-04-02 17:47:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 44861
       trx_mysql_thread_id: 156117
                 trx_query: UPDATE 01_new_auction_info nai,last_status ls SET
ls.house_type = nai.house_type WHERE ls.bid_id = nai.bid_id
       trx_operation_state: fetching rows
         trx_tables_in_use: 2
         trx_tables_locked: 2
          trx_lock_structs: 44009
     trx_lock_memory_bytes: 4634152
           trx_rows_locked: 342444
         trx_rows_modified: 852
   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.04 sec)

ERROR: No query specified

找到这行代码（锁定的进程id）：

 trx_mysql_thread_id: 156117

3：kill 事务进程

kill掉进程

kill 156117

显示如下：

Query OK, 0 rows affected (0.00 sec)

4.查询事务，看是否有未杀死的进程

SELECT * FROM information_schema.INNODB_TRX\G;

显示如下：

Empty set (0.00 sec)

ERROR: No query specified

问题解决