When tables are locked does MySQL queue queries?

Question

I have a small production website, but it's live 24/7. When running the Optimize, Analyze or Repair commands, MySQL locks the table it is working on.

QUESTIONS

• How would this effect users trying to make changes from a Perl application?
• Are queries queued for the time table is locked and then processed (assuming the application does not time out)?
• Do queries simply not work?

Thanks!

/usman

Answer 1

Since OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are DDL, full table locks are required.

However, if all data is InnoDB, the latest version of MySQL is a little more lenient with DDL locks. Note the current MySQL Documentation on OPTIMIZE TABLE in InnoDB:

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL (ALGORITHM=INPLACE). Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, i.e. the table is locked. Also, secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key.

As of 5.6.17, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for both regular and partitioned InnoDB tables. The table rebuild, triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE, is now performed using online DDL (ALGORITHM=INPLACE) and only locks the table for a brief interval, which reduces downtime for concurrent DML operations.

With ANALYZE TABLE

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

REPAIR TABLE makes no apologies. The documentation sheds no light on reads, so SELECTs would be outright blocked until the repair is completed.

All queries that are writes (INSERT, UPDATE, DELETE) would be queued until those full table locks are released. This is true regardless of the storage engine being InnoDB or MyISAM.

SUMMARY

Read locks mitigate SELECTs depending on the command

• ANALYZE TABLE will permit SELECTs
• OPTIMIZE TABLE will try to minimize lock time
• REPAIR TABLE has nothing to offer queries but waiting