Currently, I use mysqldump to backup several large MySQL databases amounting to millions of rows of data. During the dump process, tables get locked for reading. 99% of the time, this system works just fine.

Ever so often (perhaps every 50-100 dumps), a read lock will hang on a table. This prevents all access to the table (even for SELECTs), which basically results in a self-imposed Denial of Service (DoS) attack.

From my experience, this only appears to happen to MyISAM type tables. As an experiment, I've converted my last problem table to InnoDB via

ALTER TABLE <table_name> TYPE = InnoDB;

Over the next few weeks, I'll keep an eye on this problem and leave an update as to the outcome.

In the meantime, if you have any suggestions, leave a comment or drop me a line.

Further Reading:

• MySQL Cookbook by Paul DuBois
• High Performance MySQL: Optimization, Backups, Replication, and More by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy Zawodny, Arjen Lentz, and Derek Balling