mysql：ERROR 1205 (HY000): Lock wait timeout exceeded； try restarting transaction

系统管理员 2022-11-26 05:00 97阅读 0赞

运行sql到一半时计算机进入睡眠状态，打开后停止pycharm重新运行，出现如下错误：

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

参考：[https://blog.csdn.net/qq\_32447301/article/details/78688032?utm\_medium=distribute.pc\_relevant.none-task-blog-baidulandingword-3&spm=1001.2101.3001.4242][https_blog.csdn.net_qq_32447301_article_details_78688032_utm_medium_distribute.pc_relevant.none-task-blog-baidulandingword-3_spm_1001.2101.3001.4242]

解决了问题。

*  终端打开mysql后，查看数据库的隔离级别, 为重复读

mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)

*  查看线程情况：

mysql> show full processlist;
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    2324
    Current database: *** NONE ***
    
    +------+------+------------------+------+---------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------省略n行+n行的sql语句
    | 2324 | root | localhost        | NULL | Query   |    0 | init     | show full processlist     
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    6 rows in set (0.08 sec)

*  显示了正在执行的慢SQL记录线程，尝试kill2324，提示：

mysql> kill 2324
        -> ;
    ERROR 1317 (70100): Query execution was interrupted

大概就是说我之前做的查询被中断了

*  查看innodb的事务表INNODB\_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    2325
    Current database: *** NONE ***
    
    *************************** 1. row ***************************
                        trx_id: 47450
                     trx_state: RUNNING
                   trx_started: 2020-08-07 12:25:19
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 345173
           trx_mysql_thread_id: 2320
                     trx_query: update goods set goods.name_acronym = case when goods.id = 1 then 'putao' when goods.id = 2 then 'hamigua' when goods.id = 3 then 'mangguo' when goods.id = 4 then 'mangguo' when goods.id = 5 then 'mangguo' when goods.id = 6 then 'xiangjiao' when goods.id = 7 then 'mugua' when goods.id = 8 then 'taozi' when goods.id = 9 then 'taozi' when goods.id = 10 then 'xiahei' when goods.id = 11 then 'liulian' when goods.id = 12 then 'shanzhu' when goods.id = 13 then 'aijicheng' when goods.id = 14 then 'jinkouhongxiyou' when goods.id = 15 then 'aogan' when goods.id = 16 then 'longyan' when goods.id = 17 then 'sanliulian' when goods.id = 18 then 'lanmei' when goods.id = 19 then 'jinkouhongti' when goods.id = 20 then 'jinkouwuzihongti' when goods.id = 21 then 'xingziliyanyun' when goods.id = 22 then 'xiaobaixing' when goods.id = 23 then 'xiaheili' when goods.id = 24 then 'jiangbo' when goods.id = 25 then 'putao' when goods.id = 26 then 'ceshi' when goods.id = 27 then 'youtao' when goods.id = 28 then 'xiahei' when goods.id =
           trx_operation_state: NULL
             trx_tables_in_use: 1
             trx_tables_locked: 1
              trx_lock_structs: 3973
         trx_lock_memory_bytes: 390696
               trx_rows_locked: 345171
             trx_rows_modified: 341200
       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.02 sec)
    
    ERROR: 
    No query specified

*  kill掉trx\_mysql\_thread\_id：2320

mysql> kill 2320;
    Query OK, 0 rows affected (0.00 sec)

*  再去查询INNODB\_TRX表，就没有阻塞的事务sleep线程存在了

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
    Empty set (0.00 sec)
    
    ERROR: 
    No query specified

*  再次查看线程情况

mysql> show full processlist;
    +------+------+------------------+------+---------+------+-------+-----------------------+
    | Id   | User | Host             | db   | Command | Time | State | Info                  |
    +------+------+------------------+------+---------+------+-------+-----------------------+
    | 2316 | root | 172.20.0.1:36104 | ??   | Sleep   |   47 |       | NULL                  |
    | 2317 | root | 172.20.0.1:36108 | ??   | Sleep   |   47 |       | NULL                  |
    | 2318 | root | 172.20.0.1:36112 | ??   | Sleep   |   47 |       | NULL                  |
    | 2319 | root | 172.20.0.1:36116 | ??   | Sleep   |   47 |       | NULL                  |
    | 2325 | root | localhost        | NULL | Query   |    0 | init  | show full processlist |
    +------+------+------------------+------+---------+------+-------+-----------------------+
    5 rows in set (0.00 sec)

[https_blog.csdn.net_qq_32447301_article_details_78688032_utm_medium_distribute.pc_relevant.none-task-blog-baidulandingword-3_spm_1001.2101.3001.4242]: https://blog.csdn.net/qq_32447301/article/details/78688032?utm_medium=distribute.pc_relevant.none-task-blog-baidulandingword-3&spm=1001.2101.3001.4242