MySQL users have a few different storage engines to choose from for each of their tables, the most popular/well-known being MyISAM and InnoDB. Each has its own pros and cons, but there are several features that make InnoDB the best choice for most cases. One of these is row level locking, as opposed to MyISAM’s table level locking.

What’s the difference? Simply put, when writing to an InnoDB table –as part of an insert or an update– the row being written is locked, and all other operations on that row must wait until the write operation finishes. That’s pretty reasonable, and won’t normally cause any problems. On the other hand, when writing to a MyISAM table, the whole table is locked, and all other operations on the whole table must wait until the write operation finishes.

You won’t normally want to perform lots of simultaneous writes to a single row, but a web application under heavy load may easily be performing many concurrent writes (and reads) on a given table. And if that table’s engine is MyISAM… things can get pretty slow.

As an example, a few years ago, while working on a high traffic Facebook app, we started getting complaints about some parts of the app being slow. It turned out that some update queries where taking 10+ seconds to complete! A little investigation revealed the cause: MyISAM’s table locking. By switching to InnoDB, everything started running smoothly (way below 1 second) again.