MYSQL 5.6.10 ANALYZE TABLE lock issue

Question

I ran the analyze table command on production mysql db without knowing it would prevent me from selecting the contents of the table. This caused production site to go down :( How long can it take for the lock to release? Also, would recreating the db from a backup solve the problem / get rid of the locks?

Please let me know.

Thanks.

score 2 · Answer 1 · answered Sep 17 '18 at 02:29

ANALYZE TABLE waits to acquire a metadata lock. While it's waiting, any SQL query against the table waits for ANALYZE TABLE.

ANALYZE TABLE is normally pretty quick, i.e. 1-3 seconds. But that quick operation doesn't start until it can acquire the metadata lock.

It can't acquire the metadata lock while you have long-running transactions going against the table. So if you want this to run faster, finish your transactions.

See my answer to MySQL failing to ALTER TABLE which is being actively written to for more information.

score 1 · Answer 2 · answered Sep 16 '18 at 23:50

1

ANALYZE TABLE quite clearly says 'During the analysis, the table is locked with a read lock for InnoDB and MyISAM'.

You can KILL {connection number} in SQL to stop the command.

Note: you probably should update to a more recent version of MySQL-5.6.

answered Sep 16 '18 at 23:50

danblack

12,130
2
22
41

I did that but it keeps on creating more processes. – Mo Huss Sep 17 '18 at 00:53

MYSQL 5.6.10 ANALYZE TABLE lock issue

2 Answers2