Last week I talked about how to best use indices in your database queries. Now let's talk about transactions. Transactions may sound reasonably straightforward, as indexing does, but there are a lot of things you can do wrong which will haunt you in certain circumstances.

The general idea behind a transaction is that several steps can be performed in series, with the capability to undo all of the steps at once if needed. In addition, the transaction should happen inside an isolated world where other transactions cannot change data while your transaction is running.

If you're one of the 6 or 7 people who didn't buy Linux Journal just to read my article ;-), then you may not know how to properly use transactions or why you should use them.

When you are used to building sites and not using transactions, you work around their absence in creative ways. For instance, I have a lot of random cron jobs that run on SourceForge to munge data in interesting ways. Some of that munging can take quite a while, and you don't want your site to be out of business while it happens. So with MySQL, I would create temp tables to munge data in, then swap the temp table for the permanent one when I'm done. This did the trick - mostly. But for the brief instance while I was swapping tables, the site could be hosed.

If you live in a world where you have transactions, you can wrap the entire process in a begin...commit block and your site won't be out of business for even one microsecond.

So rather than munging data in a temp table, I start a transaction, delete all the data from the live table, recreate it, then commit my changes. The entire process happens in its own little universe right up until I commit them, and then the rest of the world can see the changes.

Like proper indexing, proper transaction use is one or two degrees more complex than you may have bargained for. To do things 100% correctly, you may have to wrap entire blocks of selects and updates inside the same transaction.