linux设置mysql innodb_lock_wait_timeout的值

调整MySQL InnoDB锁等待超时配置

最新推荐文章于 2025-04-27 14:17:22 发布

最新推荐文章于 2025-04-27 14:17:22 发布 · 1.2k 阅读

文章标签：

#mysql

Linux 同时被 2 个专栏收录

18 篇文章

订阅专栏

MySQL

10 篇文章

订阅专栏

本文详细介绍了如何在root权限下通过vi编辑器修改MySQL配置文件中innodb_lock_wait_timeout参数，并重启服务生效的方法。同时解释了该参数的作用、默认值、范围以及可能出现的错误情况。

root权限下:

vi /etc/my.cnf

在[mysqld]配置下面加入

innodb_lock_wait_timeout=value # value是你想设置的值

重启mysql

/etc/init.d/mysqld restart

reference：

http://dev.mysql.com/doc/refman/5.0/en/option-files.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

http://dev.mysql.com/doc/refman/5.0/en/command-line-options.html

以上是mysql官方文档，以下是摘录：

innodb_lock_wait_timeout

Command-Line Format	`--innodb_lock_wait_timeout=#`
Option-File Format	`innodb_lock_wait_timeout`
Option Sets Variable	Yes, `innodb_lock_wait_timeout`
Variable Name	`innodb_lock_wait_timeout`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`numeric`
	Default	`50`
	Range	`1 .. 1073741824`

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

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

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on_timeout option, available as of MySQL 5.0.32. See also Section 13.2.12, “InnoDB Error Handling” .)

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

innodb_lock_wait_timeout不能动态的runtime修改，但是可以在启动时添加启动参数和在配置文件中修改。

但是试了好几次，按文档

mysql -u user --innodb_lock_wait_timeout=#value

mysql -u user --innodb_lock_wait_timeout=value

...

不成功，不知道是不是没读明白文档。