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.
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.
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.
Transactions
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.
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.
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.
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.
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.
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.
