What Are Transactions?

Database transactions are simply sets of queries that must be executed in such a way so that if one fails to execute completely, they all fail. For instance, say that you have a set of three queries, the second dependent on the results of the first, and the third dependent on the results of the second. If the second query fails, you need to have a way to negate the results of the first query; similarly, if the third query fails you need to negate the results of the first and second queries as well.

By instituting transactional processing in your database-driven applications, you are ensuring the integrity of the data stored in your database. The following sections describe the process of using transactions both through the command-line interface and PHP functions.

Basic Syntax Used in Transactions

You will need to understand the following key terms when thinking about using transactions with MySQL:

• COMMIT This command occurs at the end of the series of queries in your transaction and is issued only if all the required queries have executed successfully.

• ROLLBACK This command is used when one or more of the series of queries in your transaction fails and resets the affected tables to their pretransaction state.

Going back to the example used previously, of three queries dependent on each other, a sequence of events in the MySQL command-line interface might look something like this:

1.	Issue the BEGIN command to begin a new transaction.
2.	Select a value from table1 to be inserted into table2.
3.	If a value cannot be selected from table1, issue a ROLLBACK command to ensure that the transaction ends and that tables are returned to their previous state.
4.	If a value can be selected from table1, insert a value into table2.
5.	If the insertion of a record into table2 fails, issue a ROLLBACK command to ensure that the transaction ends and that tables are returned to their previous state.
6.	If a value can be inserted into table1, insert a value into table2.
7.	If the insertion of a record into table3 fails, issue a ROLLBACK command to ensure that the transaction ends and that tables are returned to their previous state.
8.	However, if the insertion of a record into table3 is successful, issue a COMMIT command to ensure that the transaction ends and that tables are updated appropriately.

For more information on the inner workings of transactions in MySQL, see the MySQL Manual at http://dev.mysql.com/doc/refman/5.0/en/transactional-commands.html.

In the next section, you'll see an example of transactions as used with tables concerning inventory and sales records.

Working Example Using Transactions

For the purposes of this example, imagine that you've created an online storefront with database tables that hold inventory, sales records, and the line items for the sales records:

mysql> DESC store_inventory; +------------+-------------+------+-----+---------+----------------+ | Field      | Type        | Null | Key | Default | Extra          | +------------+-------------+------+-----+---------+----------------+ | id         | int(11)     | NO   | PRI |         | auto_increment | | item_name  | varchar(25) | YES  |     |         |                | | item_price | float(6,2)  | YES  |     |         |                | | item_qty   | int(11)     | YES  |     |         |                | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec) mysql> DESC store_orders; +----------------+-------------+------+-----+---------+----------------+ | Field          | Type        | Null | Key | Default | Extra          | +----------------+-------------+------+-----+---------+----------------+ | id             | int(11)     | NO   | PRI |         | auto_increment | | purchaser_name | varchar(50) | YES  |     |         |                | | purchase_date  | datetime    | YES  |     |         |                | +----------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> DESC store_orders_lineitems; +--------------+---------+------+-----+---------+----------------+ | Field        | Type    | Null | Key | Default | Extra          | +--------------+---------+------+-----+---------+----------------+ | id           | int(11) | NO   | PRI |         | auto_increment | | order_id     | int(11) | YES  |     |         |                | | inventory_id | int(11) | YES  |     |         |                | | item_qty     | int(11) | YES  |     |         |                | +--------------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)

In the store_inventory table for this example you can find two records:

mysql> SELECT * FROM store_inventory; +----+------------+------------+----------+ | id | item_name  | item_price | item_qty | +----+------------+------------+----------+ | 1  | Great Book | 19.99      | 10       | | 2  | Awesome CD | 9.99       | 20       | +----+------------+------------+----------+ 2 rows in set (0.00 sec)

If a shopper wants to purchase two Great Books and one Awesome CD through your online store, the process would go something like this:

1.	The user completes an online form and attempts to pay for the purchases, so issue a BEGIN command for the transaction that would be part of the checkout script: BEGIN;
2.	Decrement the quantity of items in the store_inventory table: UPDATE store_inventory SET item_qty = item_qty 2 WHERE id = 1; UPDATE store_inventory SET item_qty = item_qty 1 WHERE id = 2;
3.	Add a record to the store_orders table: INSERT INTO store_orders (purchaser_name, purchase_date) VALUES ('John Smith', now());
4.	If adding the record fails, issue a ROLLBACK command to reset the available quantity of the items: ROLLBACK;
5.	If adding the record succeeds, get the ID of the record just added and use it in your query to add line items to the sales record by inserting records in the store_orders_lineitems table: INSERT INTO store_orders_lineitems (order_id, inventory_id, item_qty) VALUES ('1', '1', '2'); INSERT INTO store_orders_lineitems (order_id, inventory_id, item_qty) VALUES ('1', '2', '1');
6.	If adding the records fails, issue a ROLLBACK command to reset the available quantity of the items and remove the record in store_orders: ROLLBACK;
7.	If adding the records succeeds but the subsequent charging of a credit card or other payment method fails, issue a ROLLBACK command to reset the available quantity of the items, remove the record in store_orders, and remove the records in store_orders_lineitems: ROLLBACK;
8.	If adding the records succeeds and the subsequent charging of a credit card or other payment method also succeeds, issue a COMMIT command to ensure all the changes are stored and the transaction ends: COMMIT;

Of course, an online storefront would not directly interface with MySQL via the command-line interface but rather through a scripting language such as PHP. But if you understand the processes behind the transaction, plugging it into PHP is simpleissuing the queries and commands listed previously. lt is no different from any other PHP-to-MySQL communication, which you will learn about in Chapter 18, "Interacting with MySQL Using PHP."

In addition to the information you'll learn in Chapter 18, be sure to review these function definitions in the PHP Manual if you intend to use transactions in your scripts:

• mysqli_autocommit()http://www.php.net/mysqli_autocommit

• mysqli_commit()http://www.php.net/mysqli_commit

• mysqli_rollback()http://www.php.net/mysqli_rollback