Performance
An application may need to perform several lines' worth of operations on a number of tables. In this case, the
code may run fastest by locking the tables, thus guaranteeing exclusive and uninterrupted access to them.
The biggest impact may be if you have multiple INSERT statements in your code. Normally, the index
buffer is flushed to disk once per INSERT statement; but when locking is in force, the flushing will be
delayed until all INSERTs are completed.
Locking may help in the case of speed-critical parts of code. The downside is that other sections of code
will suffer because they have to wait for it to finish before they continue.Overcoming the Lack of Transactional Control
MySQL lacks transactional control, the ability to manage the various transactions occurring within the
database management system.
With transactional control, changes to the database do not affect the target tables immediately, even
though they may appear to do so. Instead, the changed data is held in a temporary buffer until you issue
a command to commit the new data to the table.
Transactional commands (in ANSI SQL) include COMMIT, ROLLBACK, and SAVEPOINT, which can be
used with INSERT, UPDATE, and DELETE. (More information about these commands, and how MySQL
does without them, is presented in Day 17, "MySQL Database Security.")
For example, ROLLBACK can be used to drop any changes you've made to target tables before actually
committing the changes to the tables. You might do a ROLLBACK if you suspected that another thread
had interfered with some of the data you were trying to update.
Using locking, you can get around this limitation with a simple technique. After you have locked the
required tables, you should test for any adverse conditions that might make you want to do a
ROLLBACK. Then, if everything is okay, you do your updates. Finally, you unlock the tables.Backing Up Your Database
You may want to back up your database and, in doing so, you want to make sure you get a consistent and
complete snapshot.
Consistency is important. In other words, you want to ensure that no tables are partway through being
modified by a thread at the moment you create your backup copy.
In this situation, you would perform a READ LOCK on the tables to ensure that no thread could be in the
middle of modifying something.What Is a Key?
A key on a database table provides a means to rapidly locate specific information. Although a key need not
mean anything to the human user of the database, keys are a vital part of the database architecture, and can
significantly influence performance.
Key Principles
Imagine that you have a very simple collection of data in which you hold just plain "useful" data. For
example, you might create a Customers table similar to an old-fashioned card-index file, with one
customer's name and details on each card. When you want to look up a customer, you flip through the file,
reading each card in turn. When you see the card or cards you want, you read that useful information—such
as the customer's name, address, and telephone number.
Conventionally, you might sort your card-index file in order of surname. This helps if you know the name
of the person for whose data you're looking. But what if you want to find people by some other criterion?Of course, you could set up your MySQL database in the same way. But you would soon run into
difficulties. For example, imagine that you wanted to find all the customers in a given geographical area.
Your database would have to read each customer's information in turn, a very inefficient operation.
You would call this operation a table scan. A table scan, the equivalent of flipping through that card-
index file until you find the entries you want, is the most time-consuming of database operations.