MyISAM locking explained.

The other day, some of our users reported that saving data had become extremely slow. In some cases, forms were taking up to 15 seconds to submit.

Not good. Not good at all.

MyISAM locks the table.

While looking at MySQL Workbench, I could see that a number of UPDATE and INSERT queries were queuing up. Furthermore, I could see that these queries were all destined for a large MyISAM table that we had.

The table in question contained a large amount of data, most of which was indiscriminately dumped into a TEXT column that had a FULLTEXT index on it. This data was basically a copy of whatever the user had submitted.

Originally, this data was imported into the table at certain times during the day. However, for some reason or another, the developer before me had decided to scrap this design and update the table in real-time instead.

Read locks.

The problem wasn’t apparent until the SELECT queries on the table became more complex and the data grew in size over time. SELECT statements were taking to longer to complete and these SELECT statements were acquiring a READ LOCK on the table.

In case you didn’t already know, MyISAM locking works like so:

  • SELECT operations place a READ LOCK on the entire table.
  • INSERT, UPDATE and DELETE queries place a WRITE LOCK on the entire table.

This is opposed to InnoDB, which only locks the row.

Multiple READ LOCKS can exist on a MyISAM table at the same time. i.e. Several SELECT queries can run at the same time.

Write locks.

However, a WRITE LOCK can only be placed on a MyISAM table if there are no active READ LOCKS or WRITE LOCKS. This means that an INSERT, UPDATE, ALTER TABLE or a DELETE query will have to wait and queue if a READ LOCK or another WRITE LOCK is already in place.

Essentially, this means that a WRITE LOCK can only exist by itself.

In our case, this led to following situation:

  • User A was running a lengthy SELECT query on the table.
  • User B and User C were trying to run UPDATE queries on the table.

Because the SELECT query had acquired a READ LOCK on the table, the queries from User B and User C were forced to queue up and wait. As a result, one user was able to grind the system to a halt.

The fix.

Although I did not have the time to do a complete design overhaul, I was able to solve the slowness issue by creating a new InnoDB table. All INSERT and UPDATE queries were directed towards this new InnoDB table, which was basically a replica of the large MyISAM table. Because it was using the InnoDB engine, INSERT and UPDATE queries were completed almost-immediately. Then, at midnight, outside of normal office hours, I imported all of new data over. This was feasible in our case because the table was only used to search for historic data.

Conclusion.

Having frequent reads and writes on the same MyISAM table can be a very bad idea. If your table requires a lot of INSERT and UPDATE queries, then you might need to rethink your approach.

Facebook Comments