Bug #92996 ANALYZE TABLE still locks tables 10 years later

Submitted: 29 Oct 2018 19:01 Modified: 30 Oct 2018 12:54
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[29 Oct 2018 19:01] Domas Mituzas
Description:
I'm just trying my luck ten years later to get issues identified in https://bugs.mysql.com/bug.php?id=33278 to be addressed today.

How to repeat:
... on a table that has long running query elsewhere...

mysql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> select * from t1 limit 1;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    0 |    1 | NULL |
+---+------+------+------+
1 row in set (1 min 27.02 sec)

Suggested fix:
don't hold a lock?
[29 Oct 2018 19:03] Domas Mituzas
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+-------------------------+--------------------------------------+
| Id | User            | Host      | db   | Command | Time | State                   | Info                                 |
+----+-----------------+-----------+------+---------+------+-------------------------+--------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 4884 | Waiting on empty queue  | NULL                                 |
|  9 | ubuntu          | localhost | test | Sleep   | 2549 |                         | NULL                                 |
| 14 | ubuntu          | localhost | test | Query   |   33 | User sleep              | select sleep(0.1) from t1 limit 1000 |
| 15 | ubuntu          | localhost | test | Query   |   26 | Waiting for table flush | select * from t1 limit 1             |
| 16 | ubuntu          | localhost | NULL | Query   |    0 | starting                | show processlist                     |
+----+-----------------+-----------+------+---------+------+-------------------------+--------------------------------------+
5 rows in set (0.00 sec)

mysql>
[30 Oct 2018 12:54] MySQL Verification Team
Hi,

I have tested your example on the large InnoDB table with two connections, one ANALYZE and the other SELECT and indeed, the lock is still held.

However, I consider this a feature request and not a bug.

Verified as reported.
[30 Oct 2018 16:43] MySQL Verification Team
Only one small notice from me.

I have analysed the code a bit and it seems to me that having ANALYZE and SELECT running in parallel would be possible only if scanning is used. With multi-indexed tables, it would be possible to use only indices that ANALYZE has done with prior to SELECT being run.
[30 Oct 2018 16:53] Valeriy Kravchuk
Check how Percona managed to fix it here:

https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/
[14 Sep 2021 15:36] Øystein Grøvlen
I think this was fixed in 8.0.24 (Bug#32224917)
[15 Sep 2021 12:08] MySQL Verification Team
Thanks .....

We shall check it out ......
[16 Nov 2021 13:00] zhai weixiang
Release Note from 8.0.24:

ANALYZE TABLE executed on a table concurrently with a long-running query on the same table caused subsequent queries on the table to wait for the long-running query to finish. This wait induced by ANALYZE TABLE is now eliminated, thus allowing the subsequent queries to execute with no wait. (Bug #32224917)
[17 Nov 2021 13:07] MySQL Verification Team
Thank you, Mr. weixiang.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.