MySQL Bugs: #72385: Analyze table does not get blocked by a long running update on the first run

Bug #72385	Analyze table does not get blocked by a long running update on the first run
Submitted:	18 Apr 2014 16:00	Modified:	23 Apr 2014 18:23
Reporter:	Pim VAN DER WAL	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.17	OS:	Linux (Ubuntu 12.04.4 LTS 64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	analyze table, innodb

Description:
ANALYZE TABLE uses a read lock according to the documentation which means it should be blocked by an UPDATE statement. When I run a long UPDATE statement against an InnoDB table and then run ANALYZE TABLE on the same table in a parallel session the ANALYZE TABLE succeeds without getting blocked. If I run ANALYZE TABLE again it gets blocked though. All subsequent runs of ANALYZE TABLE get blocked.

Does this mean ANALYZE TABLE does not do anything on the first run or that it bypasses the read lock and does update the statistics?

I encountered this on 5.5.24 and verified this behavior also occurs on 5.5.35 and 5.6.17. It blocks on the first run for MyISAM so this seems to be an InnoDB problem.

How to repeat:
Session 1:

mysql> CREATE TABLE t1 (col1 INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 (col1) VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET col1=col1+SLEEP(1000) WHERE col1=1;

Session 2:

mysql> ANALYZE TABLE t1;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| tmp.t1 | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> ANALYZE TABLE t1;

Now the ANALYZE TABLE can't complete unless the UPDATE completes.

Suggested fix:
Make ANALYZE TABLE block on the first run as well.

Thank you for the report.

This is not a bug.

Regarding to the first run: ANALYZE TABLE acquires READ LOCK on rows which it modifies as described at http://dev.mysql.com/doc/refman/5.6/en/analyze-table.html Since UPDATE of InnoDB tables uses row-level locking and does not lock all rows it can be not needed for ANALYZE to wait when it finishes.

Regarding to second run this is limitation of the InnoDB storage engine, described at http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html:

----<q>----
If statements or transactions are running on a table and ANALYZE TABLE is run on the same table followed by a second ANALYZE TABLE operation, the second ANALYZE TABLE operation is blocked until the statements or transactions are completed. This behaviour occurs because ANALYZE TABLE marks the currently loaded table definition as obsolete when ANALYZE TABLE is finished running. New statements or transactions (including a second ANALYZE TABLE statement) must load the new table definition into the table cache, which cannot occur until currently running statements or transactions are completed and the old table definition is purged. Loading multiple concurrent table definitions is not supported. 
----</q>----

Thanks for the explanation. I missed the exception for the second run in the manual and that makes sense but for the first run the manual says "During the analysis, the table is locked with a read lock for InnoDB and MyISAM." so I assumed it was locking the entire table rather than just some rows. Are you saying that only the rows that are analyzed are locked? I also get this with one row where it seems inevitable that the read lock and write lock hit the same record.

Not, I mean that UPDATE which started before ANALYZE locks only those rows which are getting updated at the current moment and does not prevent ANALYZE to run.

So to be clear, even when there is only one row in the table and the UPDATE locks that row it is correct that the read lock by ANALYZE TABLE does not conflict? In that case both locks have to be on the same row.