Databases Reference
In-Depth Information
against the transaction's version. Let's see how this applies to particular operations
when the transaction isolation level is set to
REPEATABLE READ
:
SELECT
InnoDB must examine each row to ensure that it meets two criteria:
a. InnoDB must find a version of the row that is at least as old as the transaction
(i.e., its version must be less than or equal to the transaction's version). This
ensures that either the row existed before the transaction began, or the trans-
action created or altered the row.
b. The row's deletion version must be undefined or greater than the transaction's
version. This ensures that the row wasn't deleted before the transaction began.
Rows that pass both tests may be returned as the query's result.
INSERT
InnoDB records the current system version number with the new row.
DELETE
InnoDB records the current system version number as the row's deletion ID.
UPDATE
InnoDB writes a new copy of the row, using the system version number for the new
row's version. It also writes the system version number as the old row's deletion
version.
The result of all this extra record keeping is that most read queries never acquire locks.
They simply read data as fast as they can, making sure to select only rows that meet
the criteria. The drawbacks are that the storage engine has to store more data with each
row, do more work when examining rows, and handle some additional housekeeping
operations.
MVCC works only with the
REPEATABLE READ
and
READ COMMITTED
isolation levels.
READ
UNCOMMITTED
isn't MVCC-compatible
4
because queries don't read the row version
that's appropriate for their transaction version; they read the newest version, no matter
what.
SERIALIZABLE
isn't MVCC-compatible because reads lock every row they return.
MySQL's Storage Engines
This section gives an overview of MySQL's storage engines. We won't go into great
detail here, because we discuss storage engines and their particular behaviors through-
out the topic. Even this topic, though, isn't a complete source of documentation; you
should read the MySQL manuals for the storage engines you decide to use.
MySQL stores each database (also called a
schema
) as a subdirectory of its data directory
in the underlying filesystem. When you create a table, MySQL stores the table definition
4. There is no formal standard that defines MVCC, so different engines and databases implement it very
differently, and no one can say any of them is wrong.
