MyISAM's "table lock" problem, and how InnoDB solves it

Most serious users of MySQL have moved their tables to InnoDB years ago.  For those who haven't, let's discuss why InnoDB is a more scalable solution than MyISAM.

MyISAM was designed to be very fast for read queries.  It does not handle higher loads of writes very well.  It also suffers a more serious flaw: it isn't crash-safe.  In other words, you better have frequent backups.

MyISAM tables have a read queue and a write queue.  Queries are placed into one of those two queues to be processed.  The write queue has higher priority than the read queue, but the table can only process one write query at a time.  Multiple read queries can occur at once, so the read queue will often be empty.

If a single query is added to the write queue, the read queue will block additional queries from starting.  All existing read queries will continue to run until they're finished, then any queries in the write queue will be processed.  Then, and only then, the read queue will flush its queries.

This queue system works very well for short queries.  As long as no queries run for a significant amount of time, things will generally perform well.  A long-running write query will lock the table the entire time it's running.  Additionally, a long-running read query will do the same, if a write query (even a short one) is waiting for it.

InnoDB avoids this table-locking problem by using Multi-Version Concurrency Control, or MVCC.  This is the same method used by many other enterprise database solutions, such as Oracle, MS SQL Server, and PostgreSQL.

As its name implies, MVCC allows multiple versions of the same table to exist in parallel.  Once a SELECT query begins, it will continue to see the table as it existed at the time the query (or transaction) began.  Another query can update a row in that table without affecting the results returned to the first query.

No longer will a long-running query take down your server!  There are still some concerns, however.  Even with MVCC, we still have to worry about multiple write queries overwriting each other, and making sure things are done in the correct order.  InnoDB uses row-level locking to ensure that multiple transactions don't write to the same row of your table.  It also has deadlock detection, which aborts queries if the server thinks two transactions are waiting for each other.  For most workloads, you won't notice those very often, but you should be aware of them.

Also of note is the server's isolation level.  Which isolation level you use depends entirely on what risks you're willing to take with your data.  They're described in the MySQL manual.