Queries to MyISAM table stuck in "Waiting for table level lock" state

Symptoms

You may see queries stuck in the MySQL process list with the "Waiting for table level lock" state.

Description 

MySQL only uses row-level locking in InnoDB tables, while MyISAM tables use table-level locks. Because of this, MyISAM tables with write-heavy workloads can experience high lock contention. However, they can be converted to InnoDB tables, which support row-level locking. 

Workaround

Please note that in the following procedure, "DATABASE_NAME" and "TABLE_NAME" must be replaced with the database's name and the name of the table to convert.

1. Access the server's command line as the 'root' user via SSH or "Terminal" in WHM.
2. Ensure that backups exist for the table prior to converting.

   mysqldump DATABASE_NAME TABLE_NAME > TABLE_NAME.sql

3. Use the ALTER TABLE command to convert the table to InnoDB.

   mysql -e "ALTER TABLE DATABASE_NAME.TABLE_NAME ENGINE=InnoDB"

Additional resources

Internal Locking Methods