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)))

CodePudding user response：

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.

CodePudding user response：

InnoDB row locks have a short timeout. 50 seconds by default, or since you have changed it, 5 seconds.

But LOCK TABLES acquires 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_timeout is 31536000 seconds -- i.e. 1 year.