MySQL InnoDB等待锁超时错误

deltamaster posted @ Aug 12, 2011 10:01:45 PM in 数据库 with tags 数据库 MySQL 配置 , 19596 阅读

当一个事务在请求某个资源时,它将待这个资源被解锁后继续操作,或者停止等待并返回错误:

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

事务将为一个资源解锁等待多长的时间,取决于innodb_lock_wait_timeout参数的设定,默认设置为50秒,最小允许值为1秒,如果将这个值设定为100000000以上的话,将禁用超时(无论等待多久都不会返回超时错误)。如果因为超时而返回了错误,那么当前SQL语句将会被回滚(在MySQL 5.0.12及以前的版本中,整个事务都将直接被回滚),用户程序可以再次尝试执行这条语句直到成功(通常会等一段时间再重试这个SQL语句),或者回滚整个事务并重新开始。

在InnoDB插件1.0.2之前,更改innodb_lock_wait_timeout选项的唯一方法是修改my.cnf或者my.ini配置文件中的相应选项,并重新启动数据库实例。

在InnoDB插件1.0.2及之后的版本中,还可以在MySQL实例运行时使用SET GLOBAL或者SET SESSION命令来更改innodb_lock_wait_timeout选项。更改GLOBAL设置需要SUPER权限,更改会影响所有在此更改之后新创建的的客户端Session,更改SESSION则只对当前客户端Session有效。

* 本文在CC BY-SA(署名-相同方式共享)协议下发布。
charlly 说:
Jan 11, 2023 02:55:01 PM

If you're getting a MySQL InnoDB wait for lock timeout error, it means that your database is experiencing a high number of concurrent transactions and is struggling to keep up. This can be caused by a number of things, including poorly designed database tables, inefficient queries, or a lack of indexes. To fix this issue, you'll need to analyze your database structure and queries to see where the bottlenecks are. Once you've identified the problem areas, you can make the necessary changes to improve performance. This may involve redesigning Lab grown diamonds database tables, optimizing queries, or adding indexes.

10th Question Paper 说:
Feb 13, 2023 04:31:23 PM

10th Question Paper 2024 is Get Available by the Board of Secondary Education. The Model Question Paper for class 10th will deliver in online mode on the authority site. The Model Paper will comprise of the subject-wise New Question Paper, day, timings, 10th Question Paper 2024 and significant guidelines for test day. In this article, the up-and-comer will get the data about the 10th Class Latest Question Paper 2024.


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter