How To Reduce table_locks_waited In MySQL/MyISAM

By Stephen Jayna, 19th August 2009

The scourge of parallelism and scaling everywhere: locking. Or in MySQL/MyISAM — and to be more precise — table locks. Here's an overview of what to look out for and how one might go about reducing the frequency at which they occur.

Before you embark on this please read How To Speed Up MySQL: An Introduction To Optimizing. It suggests some things you should consider first before getting down to the nitty gritty of reducing table_locks_waited. After all it's usually too many queries that are running too slowly that cause locked tables. Fix those first!

Know Your Enemy: show status like '%\_locks\_%';

mysql> show status like '%\_locks\_%';
| Variable_name         | Value |
| Table_locks_immediate | 53148 | 
| Table_locks_waited    | 17716 | 
2 rows in set (0.00 sec)

That's an upsetting ratio. For every query that had to twiddle its thumbs, waiting in anticipation for a shot at said table, only 3 flew through immediately. You could throw more hardware at it I suppose, it might help. The chances are however you can increase your throughput using one or all of the following methods without reaching for another CPU, more memory or even faster disks.

Why Table Locking Occurs in MyISAM

Locks exist, in a nutshell, to prevent queries from altering data while that data is being read by another process. Or vice-versa.

There are different types of locking in the database world. MyISAM happens to use table locks which are very fast. They are easier to implement when compared to the row-level locking employed by InnoDB and permit a higher query throughput. That assumes of course the number of writes that occur on your database is few. Or, and this is sometimes overlooked, that no one query takes more than an instant.

After all it's the being locked out that hurts — writes tend to go through pretty quickly — but if they have to wait for a query to complete...

Imagine The Scenario

  1. 0.00 seconds A select query (ie. read-only) accesses the table, it will take around 2 seconds to complete.
  2. 0.01 seconds Another select query accesses the table, it takes no time and will complete in an instant as it can run in parallel.
  3. 0.02 seconds An insert, delete or update query (ie. write) attempts to write to that very same table before the first select has completed.
  4. 0.03 seconds A select query comes in, again waiting for the first select to complete.
  5. 0.04 seconds Another select query comes in, again waiting for the first select to complete.
  6. 0.05 seconds Yet another select query comes in, again waiting for the first select to complete.
  7. .
  8. 1.99 seconds (~1000 queries later) Yet another select query comes in, again waiting for the first select to complete.

And so on and so forth. The queries in orange and red are blocked and have to wait for the first select to complete before they can be executed. The selects in red are blocked by the write operation in orange which is in turn blocked by the long-running select in green. This is what causes the table_locks_waited value to grow.

See what happens if you've a query that takes any more than an instant and another query comes along, needing to write to the table? A whopping great long queue that's what. And therefore a collapse in parallelism. You're not going to scale like this.

How To Avoid Or Reduce Table Locking

There's a comprehensive list of things you might consider here on MySQL's site. While they're all worthy of consideration I can't help feeling some of them will only serve to delay the inevitable.

Divide And Conquer: Your Queries

As described in How To Speed Up MySQL: An Introduction To Optimizing consider splitting up your queries. Ten queries that take an instant is preferable to one when locking is an issue. Make your application do the work: it'll scale more easily than MySQL.

Row-level Locking: InnoDB

InnoDB uses row-level locking. That is to say during normal operation it won't lock entire tables when only updating a single row. The locking mechanism itself is slower than MyISAM but if your table is heavily contended it might be that the table in question should be moved to InnoDB.

That has its downsides of course. Your indexes will grow in physical size, chances are you may need more memory to cope.

Divide And Conquer: Your Data

Divide your data. Shard. Partition. Ensure no one table is too big. What's too big? It depends. MySQL barely imposes any artificial limits on the size a table can grow to but you're going to have to eventually make a call to split the table into shards or chunks.

That's easier said than done of course. Most people shy away from sharding in MySQL because it's difficult. MySQL are beginning to include partitioning themselves. It's the sort of thing that Hadoop does very well though.

Alternatives To MySQL

There are a lot of non-traditional DBMS out there that can be superior to MySQL in some situations. Don't believe it if anyone suggests your data must reside in either MySQL, PostgreSQL, Oracle or perhaps Microsoft's SQL Server.

I'll write about Hadoop, MonetDB and friends sooner rather than later but I'm afraid they're more data warehouse than online transaction processing. Low latency they are not. Not necessarily.


I hope we've given you some idea of how to start dealing with lock contended tables or at least food for thought as it were. Reducing table_locks_waited can tricky to get right and depends very much on your data and the nature of your queries. Make sure you do review MySQL's page on the matter. A tweak to your configuration may be enough for your application in the short term.

ps. If you followed the link to the MonetDB benchmarks take them with a pinch of salt, I included it purely for fun. I wouldn't dare vouch for their validity!

Your Comments

Table locks

Nice article on locking. I was very close to switching to InnoDB for it's row based locking when I found I had an inherent bug in my system. Check out what I did to improve table level locks here:

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

Having MySQL performance issues?

We're experts at tuning MySQL and offer a MySQL performance consulting service.

LAMP stack not performing as you'd hoped?

Everita is experienced at getting the most out of your Linux, Apache, MySQL and Perl, PHP or Python setup. We're Drupal Experts.

Client Testimonials

Steve was knowledgeable and diligent in helping us identify application characteristics which were impacting MySQL's efficiency.

I would recommend him to anyone needing help optimising MySQL server and look forward to working with him in the future.

Richard Ainley
Performance Tester
WorkPlace Systems PLC

Next »


Enter your email address below to receive a very occasional message when something significant is published on the site.

You can unsubscribe at any time and we'll never share your address.

Contact Us

L: Reading, United Kingdom

Linux & Mac Specialists

Images courtesy of Rowan Mersh