0

While trying to get write lock of some table with:

LOCK TABLE infoTable write;

My Python3 script is blocked, and show processlist indicates the script is blocked by waiting to get the write lock:

Waiting for table metadata lock

However, the target table infoTable is not locked by any session, as the result of show open table tells:

mysql> show open tables where In_use > 0;
Empty set (0.00 sec)

So how comes that getting the write lock is blocked while the table is not locked by any other session?

Follow up1: As suggested by comment, I tried to get lock status of transaction, no transaction is locking the target table.

mysql> SELECT * FROM INNODB_LOCK_WAITS;
Empty set, 1 warning (0.00 sec)

Additional info for your reference: mysql version: mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper

OS info: Linux 4.15.0-42-generic #45-Ubuntu SMP Thu Nov 15 19:32:57 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

FaceBro
  • 787
  • 2
  • 13
  • 29
  • 1
    Transactions, long queries and ddl queries (alter table) can also lock the table/prevent a new write lock, but are not marked as "in_use". Maybe try [How do I find which transaction is causing a “Waiting for table metadata lock” state?](https://stackoverflow.com/q/13148630) – Solarflare Jun 09 '19 at 18:42
  • @Solarflare what is long queries ? Seems long queries are those queries that are long ? And will ddl queries lock the table implicitly? – FaceBro Jun 10 '19 at 02:11
  • I meant long running queries. The usual cause is transactions though. Locks are symmetrical. Everything another session cannot do while you hold the lock, prevents you from getting a lock if another session is currently doing that - but bascially nothing will announce it by increasing "in_use". So you need to look elsewhere for the cause. (Or maybe try not to use "lock" at all, it is usually not really needed anymore, and is a very extensive lock compared to what transactions can do nowadays.) – Solarflare Jun 10 '19 at 06:53
  • @Solarflare It's indeed blocked by one transaction, but I did'nt explicitly start the transaction. All I do in the offending script is select * from infoTable, and one transaction is implicitly started for the query? – FaceBro Jun 10 '19 at 08:56
  • This will depend on your [autocommit setting](https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html) (maybe implicitly or explicitly set by your framework/your script environment). If you already identified the offending query, check the surrounding code, maybe remove that part for testing purposes (as it might not be causing the problem). It's hard to tell you more details without knowing anything about your code though. – Solarflare Jun 10 '19 at 11:38

0 Answers0