Skip to main content

You may have noticed our Login page looks different. We're currently improving our systems to provide you with a new more unified login experience.

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

Christopher Barnett
  • Updated

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. Other storage engines use table-level locks, as documented at:

 

https://dev.mysql.com/doc/refman/8.0/en/internal-locking.html

 

Due to this, MyISAM tables with write-heavy workloads can experience high lock contention.

 

Workaround

 

The MyISAM tables can be converted to InnoDB tables which support row-level locking. This should be performed by a qualified System or Database Administrator. This can be done with the following commands, replacing DATABASE_NAME and TABLE_NAME as appropriate.

 

1) Ensure that backups exist for the table prior to converting.

 

mysqldump DATABASE_NAME TABLE_NAME > TABLE_NAME.sql

 

2) Convert the table to InnoDB

 

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

Related articles