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

最新推荐文章于 2024-07-12 09:58:50 发布

annyangya

最新推荐文章于 2024-07-12 09:58:50 发布

阅读量474

点赞数

分类专栏： python 文章标签： mysql

本文链接：https://blog.csdn.net/ayangann915/article/details/107916441

版权

python 专栏收录该内容

21 篇文章 1 订阅

订阅专栏

运行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

解决了问题。

终端打开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)