Managing & Using MySQL, 2nd Edition

Transactions

You talk to the database in the form of transactions.^[1] A database transaction is one or more database statements that must be executed together, or not at all. A bank account transfer is a good example of a complex transaction. In short, an account transfer is actually two separate events: a debit of one account and a credit to another. Should the database crash after the debit but before the credit, the application should be able to back out of the debit. A database transaction enables a programmer to mark when a transaction begins, when it ends, and what should happen if one of the pieces of the transaction fails.

Until recently, MySQL had no support for transactions. In other words, when you executed an SQL statement under old versions of MySQL, it took effect immediately. This behavior is still the default for MySQL. Newer versions of MySQL, however, support the ability to use transactions with certain tables in the database. Specifically, the table must use a transaction-safe table format. Currently, MySQL supports two transaction-safe table types: BDB (Berkeley DB) and InnoDB. Instructions for configuring MySQL to use these databases can be found on the MySQL web site.

In Chapter 3, we described the MySQL syntax for managing transactions from the MySQL client command line. Managing transactions from within applications is often very different. In general, each API will provide a mechanism for beginning, committing, and rolling back transactions. If it does ...