MySQL Architecture and History - High Performance MySQL - page 8

Each storage engine implements isolation levels slightly differently, and
they don't necessarily match what you might expect if you're used to
another database product (thus, we won't go into exhaustive detail in
this section). You should read the manuals for whichever storage en-
gines you decide to use.
Let's take a quick look at the four isolation levels:
READ UNCOMMITTED
In the READ UNCOMMITTED isolation level, transactions can view the results of un-
committed transactions. At this level, many problems can occur unless you really,
really know what you are doing and have a good reason for doing it. This level is
rarely used in practice, because its performance isn't much better than the other
levels, which have many advantages. Reading uncommitted data is also known as
a dirty read .
READ COMMITTED
The default isolation level for most database systems (but not MySQL!) is READ
COMMITTED . It satisfies the simple definition of isolation used earlier: a transaction
will see only those changes made by transactions that were already committed
when it began, and its changes won't be visible to others until it has committed.
This level still allows what's known as a nonrepeatable read . This means you can
run the same statement twice and see different data.
REPEATABLE READ
REPEATABLE READ solves the problems that READ UNCOMMITTED allows. It guarantees
that any rows a transaction reads will “look the same” in subsequent reads within
the same transaction, but in theory it still allows another tricky problem: phantom
reads . Simply put, a phantom read can happen when you select some range of rows,
another transaction inserts a new row into the range, and then you select the same
range again; you will then see the new “phantom” row. InnoDB and XtraDB solve
the phantom read problem with multiversion concurrency control, which we ex-
plain later in this chapter.
REPEATABLE READ is MySQL's default transaction isolation level.
SERIALIZABLE
The highest level of isolation, SERIALIZABLE , solves the phantom read problem by
forcing transactions to be ordered so that they can't possibly conflict. In a nutshell,
SERIALIZABLE places a lock on every row it reads. At this level, a lot of timeouts and
lock contention can occur. We've rarely seen people use this isolation level, but
your application's needs might force you to accept the decreased concurrency in
favor of the data stability that results.
Table 1-1 summarizes the various isolation levels and the drawbacks associated with
each one.