At 4:36 PM -0500 6/23/01, Mike<mickalo>Blezien wrote:
>Hello All,
>
>I'm current working on project, converting a flatfile system, to use MySQL,
>which supports transactions w/Innobase tables. My questions is, as
>I'm sure many
>have coded using transaction w/Perl and DBI. I understand that there as some
>draw backs to using transactions when not necessary, but very beneficial, when
>needed. What are some good guide lines or "rule of thumb" to following when
>using transactions type tables?
>
>Any comments or feedback much appreciated. :)
Use transactions any time you care about the consistency of your database.
Let me give a couple of examples.
You're writing a banking application. You have a button on your
website that lets people transfer funds from one account to another.
The user enters the FROM account and the TO account and the dollar
amount, and presses a button marked TRANSFER.
Now your code first subtracts the amount (say, $100) from the FROM
account and then -- **CRASH** some lamer in the computer room trips
over the power cord and shuts down your server. Now your user has
$100 less in his FROM account but the TO account never got
incremented. So your user lost $100. Very uncool if you're running
a bank and processing a hundred million dollars a day in wire
transfers, checking account deposits, mortgate payments, ATM
withdrawals, etc. So you most definitely want to bracket the
decrement of the FROM and the increment of the TO, so that either
they both get recorded or neither does. That's what transactions are
for.
When would transactions not be needed? Only when you don't care
about the consistency of your database. Somebody joins your site and
increments your hit counter. If one update succeeds and the other
fails, who cares?
So the rule of thumb is if you have a set of database updates that
must either ALL succeed or else NONE happen at all, you should
bracket the updates inside a transaction.
Steve L
