Databases Reference
In-Depth Information
change. Minimizing the amount of data that you lock at any one time lets changes to
a given resource occur simultaneously, as long as they don't conflict with each other.
The problem is locks consume resources. Every lock operation—getting a lock, check-
ing to see whether a lock is free, releasing a lock, and so on—has overhead. If the system
spends too much time managing locks instead of storing and retrieving data, perfor-
mance can suffer.
A locking strategy is a compromise between lock overhead and data safety, and that
compromise affects performance. Most commercial database servers don't give you
much choice: you get what is known as row-level locking in your tables, with a variety
of often complex ways to give good performance with many locks.
MySQL, on the other hand, does offer choices. Its storage engines can implement their
own locking policies and lock granularities. Lock management is a very important de-
cision in storage engine design; fixing the granularity at a certain level can give better
performance for certain uses, yet make that engine less suited for other purposes. Be-
cause MySQL offers multiple storage engines, it doesn't require a single general-
purpose solution. Let's have a look at the two most important lock strategies.
Table locks
The most basic locking strategy available in MySQL, and the one with the lowest over-
head, is
table locks
. A table lock is analogous to the mailbox locks described earlier: it
locks the entire table. When a client wishes to write to a table (insert, delete, update,
etc.), it acquires a write lock. This keeps all other read and write operations at bay.
When nobody is writing, readers can obtain read locks, which don't conflict with other
read locks.
Table locks have variations for good performance in specific situations. For example,
READ LOCAL
table locks allow some types of concurrent write operations. Write locks
also have a higher priority than read locks, so a request for a write lock will advance to
the front of the lock queue even if readers are already in the queue (write locks can
advance past read locks in the queue, but read locks cannot advance past write locks).
Although storage engines can manage their own locks, MySQL itself also uses a variety
of locks that are effectively table-level for various purposes. For instance, the server
uses a table-level lock for statements such as
ALTER TABLE
, regardless of the storage
engine.
Row locks
The locking style that offers the greatest concurrency (and carries the greatest overhead)
is the use of
row locks
. Row-level locking, as this strategy is commonly known, is
available in the InnoDB and XtraDB storage engines, among others. Row locks are
implemented in the storage engine, not the server (refer back to the logical architecture
diagram if you need to). The server is completely unaware of locks implemented in the
