CHAPTER 37

Transactions

This chapter introduces MySQL's transactional capabilities and demonstrates how transactions are executed both via a MySQL client and from within a PHP script. By its conclusion, you'll possess a general understanding of transactions, how they're implemented by MySQL, and how to incorporate them into your PHP applications.

What's a Transaction?

A transaction is an ordered group of database operations that are perceived as a single unit. A transaction is deemed successful if all operations in the group succeed, and is deemed unsuccessful if even a single operation fails. If all operations complete successfully, that transaction will be committed, and its changes will be made available to all other database processes. If an operation fails, the transaction will be rolled back, and the effects of all operations comprising that transaction will be annulled.

Any changes effected during the course of a transaction will be made solely available to the thread owning that transaction, and will remain so until those changes are indeed committed. This prevents other threads from potentially making use of data that may soon be negated due to a rollback, which would result in a corruption of data integrity.

Transactional capabilities are a crucial part of enterprise databases, because many business processes consist of multiple steps. Take for example a customer's attempt to execute an online purchase. At checkout time, the customer's shopping cart will be compared against existing inventories to ensure availability. Next, the customer must supply their billing and shipping information, at which point their credit card will be checked for the necessary available funds and then debited. Next, product inventories will be deducted accordingly, and the shipping department will be notified of the pending order. If any of these steps fails, then none of them should occur. Imagine the customer's dismay to learn that their credit card has been debited even though the product never arrived because of inadequate inventory. Likewise, you wouldn't want to deduct inventory or even ship the product if the credit card is invalid, or if insufficient shipping information was provided.

On more technical terms, a transaction is defined by its ability to follow four tenets, embodied in the acronym ACID. These four pillars of the transactional process are defined here:

• Atomicity: All steps of the transaction must be successfully completed; otherwise, none of the steps will be committed.
• Consistency: All steps of the transaction must be successfully completed; otherwise, all data will revert to the state that it was in before the transaction began.
• Isolation: The steps carried out by any as-of-yet incomplete transaction must remain isolated from the system until the transaction has been deemed complete.
• Durability: All committed data must be saved by the system in such a way that, in the event of a system failure, the data can be successfully returned to a valid state.

As you learn more about MySQL's transactional support throughout this chapter, you will understand that these tenets must be followed to ensure database integrity.

MySQL's Transactional Capabilities

MySQL support for transactions started with version 3.23.34a and is enabled by default in all versions of MySQL 4.0 and greater. Transactions are supported by two of MySQL's table handlers, InnoDB and BDB, both of which were first introduced in Chapter 28. This section explains transactions as applied to InnoDB. It first discusses the system requirements and configuration parameters available to the InnoDB handler, and concludes with a detailed usage example and a list of tips to keep in mind when working with InnoDB transactions. This section sets the stage for the concluding part of this chapter, in which you'll learn how to incorporate transactional capabilities into your PHP applications.

System Requirements

This chapter focuses on the transactions as applied to the InnoDB table handler, because its capabilities are enabled by default as of MySQL 4.0. You can verify whether InnoDB tables are available to you by executing this command:

mysql>SHOW VARIABLES LIKE 'have_innodb';

You should see the following:

---

+-----------------------+
| Variable_name | Value |
+-----------------------+
| have_innodb    | YES  |
+-----------------------+
1 row in set (0.00 sec)

---

If not, you need to upgrade your MySQL distribution to a version offering support for InnoDB. If you're still using a pre-4.0 version greater than 3.23.34, you can enable support by recompiling MySQL with the −-with-innodb option.

---

Tip If you're running MySQL 4.1.0 or greater, you can use the command SHOW TABLE TYPES to determine whether a particular table type is supported.

---

Table Creation

Creating a table of type InnoDB is really no different from the process required to create a table of any other type. In fact, this table type is the default on Microsoft Windows as of MySQL version 5.0, which means that no special action is required on this platform if you're running version 5.0 or greater. You simply use the CREATE TABLE statement and create the table as you see fit. Note that unless you start the MySQL daemon with the −-default-table-type=InnoDB flag, you need to explicitly specify that you'd like the table to be created as an InnoDB type at the time of creation. For example:

CREATE TABLE customers (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(45) NOT NULL,
   PRIMARY KEY(id)
   ) TYPE=InnoDB;

Once created, a *.frm file (in this example, a customers.frm file) is stored in the respective database directory, the location of which is denoted by MySQL's datadir parameter and defined at daemon startup. This file contains data dictionary information required by MySQL. Unlike MyISAM tables, however, the InnoDB engine requires all InnoDB data and index information to be stored in a tablespace. This tablespace can actually consist of numerous disparate files (or even raw disk partitions), which are located by default in MySQL's datadir directory. This is a pretty powerful feature, because it means that you can create databases that far exceed the maximum allowable file size imposed by many operating systems by simply concatenating new files to the tablespace as necessary. How all of this behaves is dependent upon how you define the pertinent InnoDB configuration parameters, introduced next.

---

Note You can change the default location of the tablespace by modifying the innodb_data_home_dir parameter.

---

A Sample Project

To help you get better acquainted with exactly how InnoDB tables behave, this section runs through a simple transactional example from the command line. This example demonstrates how two swap meet participants would go about exchanging an item for cash. Before examining the code, take a moment to review the pseudocode:

1. Participant Jason requests an item, say the abacus located in participant Jon's virtual trunk.
2. Participant Jason transfers a cash amount of $12.99 to participant Jon's account. The effect of this is the debiting of the amount from Jason's account, and the crediting of an equivalent amount to Jon's account.
3. Ownership of the abacus is transferred to participant Jason.

As you can see, each step of the process is crucial to the overall success of the procedure. Therefore, this section turns the process into a transaction to ensure that the data cannot become corrupted due to the failure of a single step. Although in a real-life scenario there are other steps, such as ensuring that the purchasing participant possesses adequate funds, the process is kept simple in this example so as not to detract from the main topic.

Creating Tables and Adding Sample Data

To follow along with the project, create the following tables and add the sample data that follows.

The participants Table

This table stores information about each of the swap meet participants, including their names, e-mail addresses, and available cash:

CREATE TABLE participants (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(35) NOT NULL,
   email VARCHAR(45) NOT NULL,
   cash DECIMAL(5,2) NOT NULL,
   PRIMARY KEY(id)
   ) TYPE=InnoDB;

The trunks Table

This table stores information about each item owned by the participants, including the owner, name, description, and price:

CREATE TABLE trunks (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   owner SMALLINT UNSIGNED NOT NULL REFERENCES participants(id),
   name VARCHAR(25) NOT NULL,
   price DECIMAL(5,2) NOT NULL,
   description MEDIUMTEXT NOT NULL,
   PRIMARY KEY(id)
   ) TYPE=InnoDB;

Adding Some Sample Data

Next, add a few rows of data to both tables. To keep things simple, add two participants, Jason and Jon, and a few items for their respective trunks.

mysql>INSERT INTO participants SET name="Jason", email="[email protected]",
                                  cash="100.00";
mysql>INSERT INTO participants SET name="Jon", email="[email protected]",
                                  cash="150.00";
mysql>INSERT INTO trunks SET owner=2, name="Abacus", price="12.99",
                           description="Low on computing power? Use an abacus!";
mysql>INSERT INTO trunks SET owner=2, name="Magazines", price="6.00",
                            description="Stack of computer magazines.";
mysql>INSERT INTO trunks SET owner=1, name="Used Lottery ticket", price="1.00",
                            description="Great gift for the eternal optimist.";

Executing an Example Transaction

Begin the transaction process by issuing the START TRANSACTION command:

mysql>START TRANSACTION;

---

Note The command BEGIN is an alias of START TRANSACTION. Although both accomplish the same task, it's recommended that you use the latter, because it conforms to SQL-99 syntax.

---

Next, deduct $12.99 from Jason's account:

mysql>UPDATE participants SET cash=cash-12.99 WHERE rowID=1;

Next, credit $12.99 to Jon's account:

mysql>UPDATE participants SET cash=cash+12.99 WHERE rowID=2;

Next, transfer ownership of the abacus to Jason:

mysql>UPDATE trunks SET owner=1 WHERE name="Abacus" AND owner=2;

Take a moment to check the participants table to ensure that the cash amount has been debited and credited correctly:

mysql>SELECT * FROM participants;

This returns the following result:

---

+-------+-------+-------------------+----------+
| id    | name  | email             | cash     |
+-------+-------+-------------------+----------+
|     1 | Jason | [email protected] |  87.01   |
|     2 | Jon   | [email protected]   | 162.99   |
+-------+-------+-------------------+----------+

---

Also take a moment to check the trunks table; you'll see that ownership of the abacus has indeed changed. Keep in mind, however, that because InnoDB tables must follow the ACID tenets, this change is currently only available to the thread executing the transaction. To illustrate this point, start up a second mysql client, again logging in and changing to the corporate database. Check out the participants table. You'll see that the participants' respective cash values remain unchanged. Checking the trunks table will also show that ownership of the abacus has not changed. This is because of the isolation component of the ACID test. Until you COMMIT the change, any changes made during the transaction process will not be made available to other threads.

Although the updates indeed worked correctly, suppose that one or several had not. Return to the first client window and negate the changes by issuing the command ROLLBACK:

mysql>ROLLBACK;

Now again execute the SELECT command:

mysql>SELECT * FROM participants;

This returns:

---

+-------+-------+-------------------+--------+
| id    | name  | email             | cash   |
+-------+-------+-------------------+--------+
|     1 | Jason | [email protected] | 100.00 |
|     2 | Jon   | [email protected]   | 150.00 |
+-------+-------+-------------------+--------+

---

Note that the participants' cash holdings have been reset to their original values. Checking the trunks table will also show that ownership of the abacus has not changed. Try repeating the above process anew, this time committing the changes using the COMMIT command rather than rolling them back. Once the transaction is committed, return again to the second client and review the tables; you'll see that the committed changes are made immediately available.

---

Note You should realize that until the COMMIT or ROLLBACK command is issued, any data changes taking place during a transactional sequence will not take effect. This means that if the MySQL server crashes before committing the changes, the changes will not take place, and you'll need to start the transactional series for those changes to occur.

---

The upcoming section "Building Transactional Applications with PHP" re-creates this process using a PHP script.

Backing Up and Restoring InnoDB Tables

You can back up your InnoDB tables in three ways:

• Perform a binary backup of your database by shutting down MySQL and copying all of your InnoDB data files, log files, and .frm files to a safe place.
• Take a snapshot of just the tables with the mysqldump command. Although InnoDB data is collectively stored in a tablespace, you can use mysqldump to take a current snapshot of an InnoDB table, just like you would with a MyISAM table.
• Use MySQL's replication features, with which InnoDB tables integrate seamlessly.

Data restoration is also accomplished in a variety of ways. Three commonly practiced methods follow:

• If the MySQL daemon crashes, InnoDB will execute its own restore process when the daemon restarts.
• If you'd like to recover a MySQL database to the location as specified by the most recent binary backup, you can use the mysqlbinlog client.
• If you used mysqldump to create a snapshot of a table, you could drop the table and re-create it by feeding the dump file back into MySQL, like so:
  
  %>mysql -u root -p databasename < dumpfile.sql

Usage Tips

This section offers some tips to keep in mind when using MySQL transactions:

• Issuing the START TRANSACTION command is the same as setting the AUTOCOMMIT variable to 0. The default is AUTOCOMMIT=1, which means that each statement is committed as soon as it's successfully executed. This is the reasoning for beginning your transaction with the START TRANSACTION command, because you don't want each component of a transaction to be committed upon execution.
• Only use transactions when it's critical that the entire process execute successfully. For example, the process for adding a product to a shopping cart is critical; browsing all available products is not. Take such matters into account when designing your tables, because it will affect performance.
• You cannot roll back data-definition language statements; that is, any statement used to create or drop a database, or create, drop, or alter tables.
• Transactions cannot be nested. Issuing multiple START TRANSACTION commands before a COMMIT or ROLLBACK will have no effect.
• If you update a nontransactional table during the process of a transaction and then conclude that transaction by issuing ROLLBACK, an error will be returned, notifying you that the nontransactional table will not be rolled back.
• Take regular snapshots of your InnoDB data and logs by backing up the binary log files, as well as using mysqldump to take a snapshot of the data found in each table.

Building Transactional Applications with PHP

Integrating MySQL's transactional capabilities into your PHP applications really isn't any major affair; you just need to remember to start the transaction at the appropriate time and then either commit or roll back the transaction once the relevant operations have completed. In this section, you'll learn how this is accomplished. By its completion, you should be familiar with the general process of incorporating this important feature into your applications.

The Swap Meet Revisited

In this example, you'll re-create the previously demonstrated swap meet scenario, this time using PHP. Keeping the nonrelevant details to a minimum, the page would display a product and offer the user the means for adding that item to their shopping cart; it might look like this:

<p>
   <strong>Abacus</strong><br />
   Owner: Jon<br />
   Price: $12.99<br />
   Low on computing power? Use an abacus!<br />
   <form action="purchase.php" method="post">
       <input type="hidden" name="itemid" value="1" />
       <br />
       <input type="submit" value="Purchase!" />
   </form>
</p>

As you would imagine, the data displayed in this page could easily be extracted from the participants and trunks tables in the database. Rendered in the browser, this page would look like Figure 37-1.

Clicking the Purchase! button would take the user to the purchase.php script. One variable is passed along, namely $_POST['itemid']. Using this variable in conjunction with some hypothetical class methods for retrieving the appropriate participants and trunks rows' primary keys, you can use MySQL transactions to add the product to the database and deduct and credit the participants' accounts accordingly.

Figure 37-1. A typical product display

To execute this task, use the mysqli extension's transactional methods, first introduced in Chapter 30. Listing 37-1 contains the code (purchase.php). If you're not familiar with these methods, please take a moment to refer to the appropriate section in Chapter 30 for a quick review before continuing.

As you can see, both the status of the query and the affected rows were checked after the execution of each step of the transaction. If either failed at any time, $success was set to FALSE and all steps were rolled back at the conclusion of the script. Of course, you could optimize this script to start each query in lockstep, with each query taking place only after a determination that the prior query has in fact correctly executed, but that is left to you as an exercise.

Summary

Database transactions are of immense use when modeling your business processes, because they help to ensure the integrity of your organization's most valuable asset: its information. If you use database transactions prudently, they are a great asset when building database-driven applications.

In the next and final chapter, you'll learn how to use MySQL's default utilities to both import and export large amounts of data. Additionally, you'll see how to use a PHP script to format forms-based information for viewing via a spreadsheet application, such as Microsoft Excel.