MySQL Variables lock_wait_timeout 数据库 参数变量解释及正确配置使用

本站中文解释

lock_wait_timeout 是MySQL的一个全局系统变量，它用来决定MySQL超时时间，即让MySQL等待获取表锁的最大时间。

此参数的设置优先级从高到低从下列顺序：：

1. 会话级别: SET SESSION lock_wait_timeout = ;
2. 全局级别: 通过 SET GLOBAL lock_wait_timeout = ;
3. 启动参数: –lock-wait-timeout=
4. 默认值: lock_wait_timeout = 31536000

如果指定值小于等于 0, 则MySQL会持续等待, 直到拿到表锁或者调用kill.如果MySQL等待超时，则会出现LOCK WAIT TIMEOUT错误。

设置 lock_wait_timeout 时, 需要注意控制这个值相关的性能参数, 否则可能引发性能问题。

官方英文解释

lock_wait_timeout

This variable specifies the timeout in seconds for attempts to
acquire metadata locks. The permissible values range from 1 to
31536000 (1 year). The default is 31536000.

This timeout applies to all statements that use metadata
locks. These include DML and DDL operations on tables, views,
stored procedures, and stored functions, as well as
LOCK TABLES,
FLUSH TABLES WITH READ LOCK,
and HANDLER statements.

This timeout does not apply to implicit accesses to system
tables in the mysql database, such as grant
tables modified by GRANT or
REVOKE statements or table
logging statements. The timeout does apply to system tables
accessed directly, such as with
SELECT or
UPDATE.

The timeout value applies separately for each metadata lock
attempt. A given statement can require more than one lock, so
it is possible for the statement to block for longer than the
lock_wait_timeout value
before reporting a timeout error. When lock timeout occurs,
ER_LOCK_WAIT_TIMEOUT is
reported.

lock_wait_timeout does not
apply to delayed inserts, which always execute with a timeout
of 1 year. This is done to avoid unnecessary timeouts because
a session that issues a delayed insert receives no
notification of delayed insert timeouts.