Learning PHP, MySQL, JavaScript, and CSS, 2nd Edition

Transactions

In some applications, it is vitally important that a sequence of queries runs in the correct order and that every single query successfully completes. For example, suppose that you are creating a sequence of queries to transfer funds from one bank account to another. You would not want either of the following events to occur:

• You add the funds to the second account, but when you try to subtract them from the first account the update fails, and now both accounts have the funds.

• You subtract the funds from the first bank account, but the update request to add them to the second account fails, and the funds have now disappeared into thin air.

As you can see, not only is the order of queries important in this type of transaction, but it is also vital that all parts of the transaction complete successfully. But how can you ensure this happens, because surely after a query has occurred, it cannot be undone? Do you have to keep track of all parts of a transaction and then undo them all one at a time if any one fails? The answer is absolutely not, because MySQL comes with powerful transaction handling features to cover just these types of eventualities.

In addition, transactions allow concurrent access to a database by many users or programs at the same time. MySQL handles this seamlessly, ensuring that all transactions are queued up and that the users or programs take their turns and don’t tread on each other’s toes.

Transaction Storage Engines

In order to be able to use MySQL’s ...