98 | Chapter 4: Database
MySQL
The MySQL DBMS is controversial. Some hold it to be a toy, while others consider it
to be a good foundation for web applications. Nevertheless, MySQL is the dominant
DBMS in use for Rails web applications today, and it has improved greatly between
versions 3 and 5.
Part of the Rails scalability mantra is “shared nothing”: each application server
should be able to stand on its own. Thus, you can throw five of them behind a load
balancer and it doesn’t matter if a user is served by different servers throughout the
course of a session. However, the bottleneck is the database. A big assumption of
this shared-nothing architecture is that the application servers all share a database. If
you use a database that doesn’t have great support for concurrency, you will have
problems.
Old versions of MySQL had some fairly serious issues, many revolving around the
issue of data integrity and constraints. The problem was not so much that the issues
existed as that MySQL’s developers seemed to have an attitude of “you aren’t going
Multiversion Concurrency Control
Multiversion concurrency control (MVCC) is one of the most powerful ways to achieve
isolation between concurrent database transactions. MVCC gives each transaction a
snapshot of the data it accesses, as the data existed at the start of the transaction. The
transaction performs actions on the data, which are logged with timestamps. When the
transaction commits, the DBMS checks the logs to ensure there are no conflicts with
other transactions; if the transaction can be performed successfully, it is applied to the
database at once, atomically.
The alternative to MVCC is row-level locking, which is used by MySQL’s InnoDB stor-
age engine. Row-level locking locks only those rows affected by an update during a
transaction (as opposed to page- or table-level locking, which are more coarse). The
primary advantage that MVCC has over locking is that MVCC does not block readers.
Since all update transactions are applied atomically, the database is always in a consis-
tent state. Pending transactions are stored as logs alongside the database to be written
upon commit, rather than being applied to the database in the middle of the transac-
tion. The most significant consequence of this is that reads never block, since they are
read from the database, which is always consistent.
It is important to realize that isolation for concurrent transactions usually trades off
against performance. MVCC uses more storage space than locking because it has to
store a snapshot for each in-progress transaction. And though MVCC never blocks
reads, the DBMS may roll back update transactions if they cause a conflict.