Databases Reference
In-Depth Information
MySQL also supports the
LOCK TABLES
and
UNLOCK TABLES
commands, which are im-
plemented in the server, not in the storage engines. These have their uses, but they are
not a substitute for transactions. If you need transactions, use a transactional storage
engine.
We often see applications that have been converted from MyISAM to InnoDB but are
still using
LOCK TABLES
. This is no longer necessary because of row-level locking, and it
can cause severe performance problems.
The interaction between
LOCK TABLES
and transactions is complex, and
there are unexpected behaviors in some server versions. Therefore, we
recommend that you never use
LOCK TABLES
unless you are in a trans-
action and
AUTOCOMMIT
is disabled, no matter what storage engine you
are using.
Multiversion Concurrency Control
Most of MySQL's transactional storage engines don't use a simple row-locking mech-
anism. Instead, they use row-level locking in conjunction with a technique for increas-
ing concurrency known as
multiversion concurrency control
(MVCC). MVCC is not
unique to MySQL: Oracle, PostgreSQL, and some other database systems use it too,
although there are significant differences because there is no standard for how MVCC
should work.
You can think of MVCC as a twist on row-level locking; it avoids the need for locking
at all in many cases and can have much lower overhead. Depending on how it is im-
plemented, it can allow nonlocking reads, while locking only the necessary rows during
write operations.
MVCC works by keeping a snapshot of the data as it existed at some point in time.
This means transactions can see a consistent view of the data, no matter how long they
run. It also means different transactions can see different data in the same tables at the
same time! If you've never experienced this before, it might be confusing, but it will
become easier to understand with familiarity.
Each storage engine implements MVCC differently. Some of the variations include
optimistic
and
pessimistic
concurrency control. We'll illustrate one way MVCC works
by explaining a simplified version of InnoDB's behavior.
InnoDB implements MVCC by storing with each row two additional, hidden values
that record when the row was created and when it was expired (or deleted). Rather
than storing the actual times at which these events occurred, the row stores the system
version number at the time each event occurred. This is a number that increments each
time a transaction begins. Each transaction keeps its own record of the current system
version, as of the time it began. Each query has to check each row's version numbers
