Databases Reference
In-Depth Information
storage engines, and as you'll see later in this chapter and throughout the topic, the
storage engines all implement locking in their own ways.
Transactions
You can't examine the more advanced features of a database system for very long before
transactions
enter the mix. A transaction is a group of SQL queries that are treated
atomically
, as a single unit of work. If the database engine can apply the entire group
of queries to a database, it does so, but if any of them can't be done because of a crash
or other reason, none of them is applied. It's all or nothing.
Little of this section is specific to MySQL. If you're already familiar with ACID trans-
actions, feel free to skip ahead to
“Transactions in MySQL” on page 10
.
A banking application is the classic example of why transactions are necessary. Imagine
a bank's database with two tables:
checking
and
savings
. To move $200 from Jane's
checking account to her savings account, you need to perform at least three steps:
1. Make sure her checking account balance is greater than $200.
2. Subtract $200 from her checking account balance.
3. Add $200 to her savings account balance.
The entire operation should be wrapped in a transaction so that if any one of the steps
fails, any completed steps can be rolled back.
You start a transaction with the
START TRANSACTION
statement and then either make its
changes permanent with
COMMIT
or discard the changes with
ROLLBACK
. So, the SQL for
our sample transaction might look like this:
1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;
But transactions alone aren't the whole story. What happens if the database server
crashes while performing line 4? Who knows? The customer probably just lost $200.
And what if another process comes along between lines 3 and 4 and removes the entire
checking account balance? The bank has given the customer a $200 credit without even
knowing it.
Transactions aren't enough unless the system passes the
ACID test
. ACID stands for
Atomicity, Consistency, Isolation, and Durability. These are tightly related criteria that
a well-behaved transaction processing system must meet:
Atomicity
A transaction must function as a single indivisible unit of work so that the entire
transaction is either applied or rolled back. When transactions are atomic, there is
no such thing as a partially completed transaction: it's all or nothing.
