Transactions - MySQL

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. Either the entire group of queries is applied to a database, or none of them are. Little of this section is specific to MySQL. If you’re already familiar with ACID transactions, feel free to skip ahead to the section “Transactions in MySQL.”

A banking application is the classic example of why transactions are necessary. Imagine a bank’s database with a 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, they can all be rolled back.

A transaction is initiated (or opened) with the BEGIN statement and applied with COMMIT or rolled back (undone) with ROLLBACK. So the SQL for the transaction might look like this:

        BEGIN;
[step 1] SELECT balance FROM checking WHERE customer_id = 10233276;
[step 2] UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
[step 3] UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
        COMMIT;

But transactions alone aren’t the whole story. What happens if the database server crashes while performing step 3? Who knows? The customer probably just lost $200. What if another process comes along between Steps 2 and 3 and removes the entire checking account balance? The bank has given the customer a $200 credit without even knowing it.

Simply having transactions isn’t sufficient unless the database server passes what is known as the ACID test. ACID is an acronym for Atomicity, Consistency, Isolation, and Durability—four tightly related criteria that are required in a well-behaved transaction processing system. Transactions that meet those four criteria are often referred to as ACID transactions.

Atomicity

Transactions must function as a single indivisible unit of work. 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.

Consistency

The database should always move from one consistent state to the next. Consistency ensures that a crash between Steps 2 and 3 doesn’t result in $200 missing from the checking account. Because the transaction is never committed, none of the transaction’s changes are ever reflected in the database.

Isolation

The results of a transaction are usually invisible to other transactions until the transaction is complete. This ensures that if a bank account summary runs after Step 2, but before Step 3, it still sees the $200 in the checking account. When we discuss isolation levels, you’ll understand why we said usually invisible.

Durability

Once committed, the results of a transaction are permanent. This means that the changes must be recorded in such a way that system crashes won’t lose the data. Of course, if the database server’s disks fail, all bets are off. That’s a hardware problem. We’ll talk more about how you can minimize the effects of hardware failures in Chapter 6.

If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today! Visit the O'Reilly Network http://www.oreillynet.com for more online content.