MySql Lock wait timeout exceeded； try restarting transaction

jinhuazhe2013

已于 2022-01-30 15:23:12 修改

阅读量463

点赞数

分类专栏： mysql 文章标签： mysql 数据库 database

于 2021-03-02 10:45:28 首次发布

本文链接：https://blog.csdn.net/u012386311/article/details/114278861

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

一般是因为数据库事务未提交或者有死锁存在。

Lock wait timeout exceeded; try restarting transaction

参考原文： https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im

mysql> update customer set account_import_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Breaking locks like this may cause atomicity in the database to not be enforced on the sql statements that caused the lock.

This is hackish, and the proper solution is to fix your application that caused the locks. However, when dollars are on the line, a swift kick will get things moving again.

Enter MySQL

mysql -u your_user -p

Let’s see the list of locked tables

mysql> show open tables where in_use>0;

Let’s see the list of the current processes, one of them is locking your table(s)

mysql> show processlist;

Kill one of these processes

mysql> kill <put_process_id_here>;

其他

mysql 查看事务

select * from information_schema.innodb_trx

SELECT * from information_schema.`PROCESSLIST` WHERE Time > 1000 AND USER = 'wonguser' ORDER BY TIME desc;

官网文档
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_on_timeout';
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

How to Fix a Lock Wait Timeout Exceeded Error in MySQL

https://severalnines.com/database-blog/how-fix-lock-wait-timeout-exceeded-error-mysql

SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_on_timeout';

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

https://smarttechways.com/2019/06/17/error-1205-hy000-lock-wait-timeout-exceeded-try-restarting-transaction/

MySql Lock wait timeout exceeded该如何处理？

https://ningyu1.github.io/site/post/75-mysql-lock-wait-timeout-exceeded/

MySQL事务锁问题-Lock wait timeout exceeded

https://cloud.tencent.com/developer/article/1356959

解决方法：

1、查看数据库当前的进程，看一下有无正在执行的慢SQL记录线程。

mysql> show  processlist;

2、查看当前的事务
当前运行的所有事务

mysql> SELECT * FROM information_schema.INNODB_TRX;

当前出现的锁

mysql> SELECT * FROM information_schema.INNODB_LOCKs;

锁等待的对应关系

mysql> SELECT * FROM information_schema.INNODB_LOCK_waits;

解释：看事务表INNODB_TRX，里面是否有正在锁定的事务线程，看看ID是否在show processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。
搜索的结果是在事务表发现了很多任务，这时候最好都kill掉。

3、批量删除事务表中的事务
我这里用的方法是：通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令。

mysql>  select concat('KILL ',id,';') from information_schema.processlist where user='cms_bokong';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 10508;            |
| KILL 10521;            |
| KILL 10297;            |
+------------------------+
18 rows in set (0.00 sec)

当然结果不可能只有3个，这里我只是举例子。参考链接上是建议导出到一个文本，然后执行文本。而我是直接copy到记事本处理掉 ‘|’，粘贴到命令行执行了。都可以。
kill掉以后再执行SELECT * FROM information_schema.INNODB_TRX; 就是空了。
这时候系统就正常了