MySQL Transactions & Why They Can’t Be Emulated in PHP

My recent article, “Top 10 MySQL Mistakes Made by PHP Developers” was far more controversial than I expected. Several of the more intriguing responses were from PHP coders who considered transactions to be an unnecessary overhead. With good-quality PHP code, they argued, you don’t need transactions or MySQL’s InnoDB storage engine.

It’s certainly true you don’t always need transactions. However, they are useful—and not just for banking or financial applications. A transaction should be used whenever you need to update two or more records and want to ensure they all succeed.

For example, assume we’re creating a simple shopping cart system. Three updates are required following a purchase:

1. add the customer’s details to the database
2. mark that the contents of a cart have been purchased
3. reduce the quantity of items in stock accordingly

In SQL code, this could resolve to:

INSERT INTO customers (name,email,cartid) VALUES ('Customer1','customer@email.com',123);
UPDATE cart SET status='paid' WHERE id=123;
UPDATE product SET stock=stock-1 WHERE id=567;

We don’t want any of those updates to break. If the last statement failed, our stock levels would not be reduced accordingly and customers could order items which weren’t available.

Now I won’t doubt the quality of your PHP code. You can run those SQL updates and validate they’re successful. You could even query the existing data so you can revert back to the original record if a failure occurs.

Unfortunately, it’s not enough. Your code may be perfect, but the PHP interpreter certainly isn’t. Nor is the web server it’s running on. Nor is the OS you’re using (yes, even Linux). MySQL could also disappear. And let’s not forget the hardware … processors die. Hard disks crash. Memory fails. Servers explode. IT administrators kick networking cables. Power grids shut down. Hosting companies go bankrupt.

Putting it simply, you can never be certain a PHP program will run successfully and execute all SQL commands as expected. However, you can prevent your database being polluted in the event of catastrophic failure.

ACID is the Cure

Transactions offer Atomicity, Consistency, Isolation and Durability. In essence, a set of SQL updates wrapped in a transaction is guaranteed to be applied to the database when you COMMIT it. If you don’t commit, the updates never occur. You can also undo all the updates if an error is caught.

Contrary to popular opinion, transactions do not necessarily make your application slower. Updates are batched and applied at once so they can improve efficiency. In some cases, it may be reasonable to start a transaction at the beginning of your web page and run a COMMIT at the end.

InnoDB or Bust

MySQL’s default MyISAM storage engine does not support transactions, so it’s not an option. If you want to use transactions, ensure all your tables are defined as InnoDB. Even if you’re not using transactions now, you never know how your application will evolve — if in doubt, InnoDB is the best choice.

Using Transactions

Despite being shrouded in mystery, transactions are remarkably simple. The following SQL statement starts a transaction:

START TRANSACTION;

Alternatively, you can use:

SET autocommit=0;

By default, every SQL statement runs within it’s own transaction. Switching off autocommit cancels this action so all subsequent SQL updates form part of a single transaction.

Now the transaction has been started, we can run any number of SQL queries, insertions, updates and deletions. None of the commands will permanently affect the database until we’re sure it’s correct. At that point, we run:

COMMIT;

Assuming that single command runs successfully, we can be sure our database was updated. Both the PHP PDO and mysqli libraries also provide commit() methods.

But what if something went wrong? Perhaps the customer was permitted to order an item which wasn’t in stock? We probably don’t want our stock level being set to -1, so we could cancel the whole transaction and alert an administrator.

If we don’t execute a COMMIT, none of the data updates are applied when the PHP page ends. However, since we’re all good programmers, we should specifically state we want to undo the transaction using:

ROLLBACK;

Alternatively, the PHP PDO and mysqli libraries provide rollback() methods.

That’s transactions in a nutshell. There are a few catches and exceptions, but we’ll save those for my next post. For now, have fun transactionalizing your applications!