Question 21716 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL queries waiting for lock never actually time out

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search