I'm trying to test an application's behavior when one of its queries encounters a lock.
I'm using InnoDB, so I ran set global innodb_lock_wait_timeout = 5;
Then I locked a table in one client: lock tables ``my_table`` write;
And tried to read the table in a second client: select count(*) from my_table;
The second client hangs indefinitely -- it never times out.SHOW PROCESSLIST shows the SELECT query is "Waiting for table metadata lock".
Why isn't the second client's query ever timing out?
(Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu)))
2条答案
按热度按时间zqdjd7g91#
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout :
The length of time in seconds an InnoDB transaction waits for a row lock before giving up.
innodb_lock_wait_timeout applies to InnoDB row locks. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.
rbl8hiat2#
InnoDB row locks have a short timeout. 50 seconds by default, or since you have changed it, 5 seconds.
But
LOCK TABLESacquires a different type of lock. It's a metadata lock, which uses a different timeout option, lock_wait_timeout. This is implemented as a table lock, outside the storage engine layer (notice the lack of "innodb" in the variable name).The default value of
lock_wait_timeoutis 31536000 seconds -- i.e. 1 year.