Learning PHP, MySQL, and JavaScript

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, by ensuring all transactions are queued up and each user or program takes their turn, and doesn’t tread on each others’ toes—all handled seamlessly by MySQL.

Transaction Storage Engines

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