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

╰+哭是因爲堅強的太久メ 2023-06-11 06:28 13阅读 0赞

### 解决问题 ###

1：mysql 命令行进入相应库

先当前库的线程情况：

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

show full processlist;

显示如下：

`+--------+------+-------------------+-------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------+----------+`

`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 |`

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