| Transactions are SQL queries executed as a two-step process. First you run the query (or queries) to see what the results will be. Then, if the results are acceptable, you enact the queries; otherwise, you undo their effects. While you have been able to build transaction support into MySQL ever since MySQL 3.23, current versions of MySQL always include a transaction-capable storage engine out of the box. Transactions bring increased reliability to your databases. If you have a sequence of SQL queries that should either all work or not work at all, you can now trust that you'll never get stuck halfway through the sequence. In fact, you could say that transactions bring an undo feature to your database applications (up to a point, that is). To this end, transaction-safe tables are more secure and easier to recover should the server crash (since queries are run either completely or not at all). On the downside, transactions can be slower than standard queries would be, and your databases will require more laborious SQL or programming interfaces to adjust for their transactional nature. Creating transaction-safe tables The MySQL software supports several different table types, where a table's type is also called its storage engine. These are formally discussed in Chapter 4, "Creating a MySQL Database." MyISAM is the default storage engine (on non-Windows systems) and is your best choice for most situations. What MyISAM does lack is support for transactions, but storage engines like InnoDB do offer this feature (read the sidebar, though, about the available storage engines). To make an InnoDB table, you'll proceed through the normal CREATE TABLE SQL but conclude it with ENGINE=InnoDB. This is true for assigning any table type, but when you do not specify a table type, MySQL assumes it to be the server's default type (which is MyISAM on non-Windows operating systems). The classic (and really one of the best) examples for the need for transactions is banking. If you're transferring money from one account to another, guaranteeing that both queries (subtracting from one account, adding to the other) worked is critical. I'll create a simple table to demonstrate the concept. Transaction-Safe Storage Engines At the time of this writing, MySQL has two storage engines that support transactions: InnoDB and BDB (Berkeley Database). (The MySQL Cluster also supports transactions, but it's an entirely different beast from the MySQL products discussed in this book.) For better or for worse, the companies that make these products have both been purchased by Oracle, another database company. I mention this because it is unclear what the future holds for the available MySQL storage engines. My suspicion is that both InnoDB and BDB will disappear in future editions of MySQL, probably to be replaced by one built from scratch by the MySQL AB company. In this chapter I will show you what steps to take to create a transaction-safe database using InnoDB, which is what MySQL has to offer at this time. Depending upon the version of MySQL you are using, you may need to use a different, as yet unnamed, storage engine instead. See Chapter 4 for instructions on identifying what storage engines your installation supports and how to choose among them. |
To create transaction-safe tables: 1. | Log in to mysql as a user capable of creating new tables in the test database.
| 2. | Select the test database.
USE test;
Since this is just a demonstration, I'll use the all-purpose test database.
| 3. | Create a new accounts table (Figure 10.1).
Figure 10.1. A new transaction-capable table must be created in order to demonstrate transactions. 
CREATE TABLE accounts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL DEFAULT 0.0, PRIMARY KEY (id), INDEX (last_name) ) ENGINE=InnoDB;
Obviously this isn't a complete table or database design. The most important aspect of the table definition is that its engineInnoDBallows for transactions.
| 4. | Populate the table (Figure 10.2).
Figure 10.2. A few sample records are inserted into the table. 
INSERT INTO accounts VALUES (NULL, 'Sarah', 'Vowell', 5460.23), (NULL, 'David', 'Sedaris', 909325.24), (NULL, 'Kojo', 'Nnamdi', 892.00);
You can use whatever names and values here that you want. The important thing to note is that MySQL will automatically commit this query, as no transaction has begun yet.
| 5. | Confirm the structure and contents of the table (Figure 10.3).
Figure 10.3. The account balances before any transactions. 
SELECT * FROM accounts;
|
 Tips
You can change an existing table's type using the command ALTER TABLE tablename ENGINE=InnoDB The same database can, and commonly does, have tables of different types.
Using transactions To start a transaction, you use the SQL term START TRANSACTION (prior to MySQL 4.0.11, you would just use BEGIN). Then you proceed with all of your queries as you otherwise would. If you like the results of the transaction, enter COMMIT to enact the queries. Otherwise, enter ROLLBACK to retract their effect. After you have either committed or rolled back the queries, the transaction is considered complete, and MySQL returns to an autocommit mode. This means that any queries you execute from that point on take immediate effect. To start another transaction, just type START TRANSACTION (or BEGIN). It is important to also know that certain types of queries cannot be rolled back. Specifically those that create, alter, truncate (empty), or delete tables or that create or delete databases cannot be undone. Furthermore, using such a query has the effect of committing and ending the current transaction. So if you start a transaction, insert a couple of records, and then alter the table, those inserts are committed. Finally, you should understand that transactions are particular to each connection. So one user connected through the mysql client has a different transaction than another mysql client user, both of which are different than a connected PHP script or Java application. To use transactions: 1. | Log in to the mysql client, if you have not already.
| 2. | Select a database with transaction-safe tables.
USE test;
Remember that transactions can be used (in current versions of MySQL) only on InnoDB and BDB table types. The accounts table, created in the preceding set of steps, fits the bill.
| 3. | Start a transaction (Figure 10.4).
Figure 10.4. Starting a new transaction. 
START TRANSACTION;
Every query from here until I enter COMMIT, ROLLBACK, or another transaction-terminating query will be part of one transaction, even if unrelated. Again, if you are using an earlier version of MySQL, you'll need to use just BEGIN instead of START TRANSACTION.
| 4. | Subtract $100 from David Sedaris' (or any user's) account.
UPDATE accounts SET balance=(balance-100) WHERE id=2;
Using an UPDATE query, a little math, and a WHERE conditional, I can subtract 100 from a balance. Although MySQL will indicate that one row was affected, the effect is not permanent until the transaction is committed.
| 5. | Add $100 to Sarah Vowell's account (Figure 10.5).
Figure 10.5. Because these queries are part of a transaction, they will not be truly enacted until the transaction is committed. 
UPDATE accounts SET balance= (balance+100) WHERE id=1;
This is the opposite of Step 4, as if $100 were being transferred from the one person to the other.
| | | 6. | Confirm the results (Figure 10.6).
Figure 10.6. You can see the results of the queries by reviewing the account balances (compare with Figure 10.3). 
SELECT * FROM accounts;
As you can see in the figure, the one balance is 100 more and the other is 100 less than they originally were (Figure 10.3).
| 7. | Roll back the transaction.
ROLLBACK;
To demonstrate how transactions can be undone, I'll negate the effects of these queries. The ROLLBACK command returns the database to how it was prior to starting the transaction. The command also terminates the transaction, returning MySQL to its autocommit mode.
| 8. | Confirm the results (Figure 10.7).
Figure 10.7. The transaction is rolled back, undoing the effect of the two UPDATE queries (compare with Figures 10.3 and 10.6). 
SELECT * FROM accounts;
The query should reveal the contents of the table as they originally were (as shown in Figure 10.3).
| 9. | Repeat Steps 3 through 5.
To see what happens when the transaction is committed, the two UPDATE queries will be run again. Be certain to start the transaction first, though, or the queries will automatically take effect!
| 10. | Commit the transaction and confirm the results (Figure 10.8).
Figure 10.8. After committing the transaction, the effect of the UPDATE queries is retained. 
COMMIT;
SELECT * FROM accounts;
Once you enter COMMIT, the entire transaction is permanent, meaning that any changes are now in place. COMMIT also ends the transaction, returning MySQL to autocommit mode.
|
Tips If the connection to the database is lost, all of the transactions are rolled back. This applies for a connection between the MySQL server and any type of client (mysql, a PHP script, etc.). When using tables that support transactions, if you fail to use the START TRANSACTION command, the queries will be automatically run much like they are on non-transactional tables (in other words, queries will be automatically committed). To alter MySQL's autocommit nature, use SET AUTOCOMMIT=0 Then you do not need to use START TRANSACTION and no queries will be permanent until you type COMMIT (or use an ALTER, CREATE, etc., query). It is advised that you do not do this, though, as it's best to more manually control your transactions.
Using Savepoints As of MySQL 4.0.14, you can use savepoints with your transactions. These are like bookmarks within a transaction, letting you roll back to a specific point. To create a savepoint, type SAVEPOINT name Then you can continue on with your transaction. If you make a mistake or an error occurs, you can revert to the state of the database at that savepoint by entering ROLLBACK TO SAVEPOINT name You can create multiple savepoints (with different names) as you need. Every savepoint is cleared once you commit the transaction. |
|
