MySQL Stored Procedure Programming

Chapter 8. Transaction Management

A transaction is a set of one or more SQL statements that are logically grouped together and that must be either applied to the database in their entirety or not applied at all.

Consider the commonly cited example of a funds transfer from one account to another. In its most simple form, this transfer will involve two UPDATE statements: one to reduce the account balance in the “from” account, and another to increase the account balance in the “to” account. Suppose that the “from” account has been updated, but then the change to the “to” account cannot be completed. We must be sure to undo that first update, or the money that was to be transferred will have, in effect, “disappeared.”

We expect database transactions to conform to the ACID principle, which means that transactions should be:

Atomic

The transaction is indivisible—either all the statements in the transaction are applied to the database, or none are.

Consistent

The database remains in a consistent state before and after transaction execution.

Isolated

While multiple transactions can be executed by one or more users simultaneously, one transaction should not see the effects of other concurrent transactions.

Durable

Once a transaction is saved to the database (an action referred to in database programming circles as a commit), its changes are expected to persist. Even if the user turns off her computer or the database server goes down, the changes will be saved. This usually means that the result of ...