Databases Reference
In-Depth Information
Locks
can be applied to prevent concurrent users from interacting destructively with
one other's data. A
read lock
allows you to prevent other users from changing data
while you're reading and processing the data, while a
write lock
tells other users that
the data is being changed and that they should not read or modify it. For example, you
need locks to avoid problems with reports when one user is trying to produce a report
while another user changes the data the report is derived from.
In some cases, you want all or none of a series of operations to succeed. For example,
if you want to travel from Melbourne to Seattle via Los Angeles, you need to have a
seat on the flight from Melbourne to Los Angeles, and a seat on the connecting flight
from Los Angeles to Seattle. Having a confirmed seat on just one leg of the route is no
use to you; you can't fly without confirmed seats on both legs.
Transactions
allow you to batch together SQL statements as an indivisible set that either
succeeds or has no effect on the database. This means you can start a transaction and
then issue a series of SQL statements. At the conclusion, you have the option of
com-
mitting
(saving) the transaction to the database or
rolling back
(canceling) the
transaction.
By default, MySQL operates in
AUTOCOMMIT
mode, where each update is treated as an
atomic transaction of its own, and changes are automatically committed. If this mode
is disabled, or a transaction is explicitly started, changes aren't commited to the data-
base unless you execute a
COMMIT
or
ROLLBACK
instruction.
Locking and transaction support is complex, and you need to make choices about the
degree of isolation needed between users and the trade-offs involved in implementing
them for your application. This is a difficult and advanced topic that's mostly outside
the scope of this topic, but in the next section we discuss how the main table types
supported by MySQL allow locking and transactions. We also include a simple trans-
action example in “Transaction examples,” and we describe how simple locking can
be used—and avoided—for our PHP wedding-registry application in “Selecting and
Deselecting Gifts” in Chapter 15.
Table Types
In the topic so far, we've used only the default MyISAM table type. There's a good
reason behind this: you very rarely need to make any other choice in small- to medium-
size applications because it's a very fast, reliable table type for most tasks. However, at
the time of writing, there are at least nine other choices you can make. This section
gives you an insight into these choices, explaining briefly the pros and cons of the
alternatives to MyISAM.
You can divide the MySQL table types up into two sets using a few different criteria.
The most common division is transaction-safe (TST) versus non-transaction-safe
(NTST):
