Ok I think I see, it is not so much that any one transaction is a big deal
to rollback manually, it is a matter of how many different actions you have
to deal with, and the fact that you may have to code each one manually. So
if you have a database where the queries are always changing, and there are
many different operations accross more than one table at once, you are nuts
to not use a database that has transactions built in. I am starting to see
where this is a drawback now for mySQL not to have transactions. But then
even with the database I am working on now, once it is done, it will
probably not have to change for some time. Man this stuff gets complex.. :)
Ok, what about this? Use a mirror copy of your database to do all changes
and then if all changes are successfull then lock tables on the "real"
database and move the records over? It seems like that might work well if
failed operations are not very common and anyway, what causes things to fail
other than programmer bugs?
At 04:09 PM 4/29/99 +0100, you wrote:
>> I was just wondering what the big deal is about transactions? I mean if you
>> are adding records to several different tables, and you get an error with
>> one of the tables what is wrong with just coding it so that the succesfully
>> added records get deleted and a error report made or some other action taken
>Nothing. Except that is basically what transactions are for. Why code
>something yourself when it can be built into the RDBMS.
>> I guess I am wondering just what transactions are used for. Why do they
>> seem to be a godlike thing for many people? I always thought it was really
>> just a safty measure because of a limitation of realtional databases.
>*Just* a safety measure.
>OK, consider this example.
>I want to transfer $100 from accountA to accountB. The operation looks
>something like this:
>1. Insert $100 into accountA
>2. Delete $100 from accountB
>(or vice versa)
>Now, suppose there is some catastrophic failure between 1. and 2.
>Ooops. AccountB gains $100!
>With transactions, you'd do something like:
> 1. Insert $100 into accountA
> 2. Delete $100 from accountB
>If no errors then
> Committ transactions
> Rollback transactions
>If anything fails between 1. and 2. then action 1. (the Insert) is not
>> Is it more than that? I am running into this a bit now because to add a
>> to a database, I have to add information into several different tables. If I
>> missed something with removing a comma or something from the web form input,
>> one INSERT could fail with the others going though.
>Exactly. That's where transactions come in.
>Robin Bowes - System Development Manager - Room 405A
>E.O.C., Overseas House, Quay St., Manchester, M3 3HN, UK.
>Tel: +44 161 838 8321 Fax: +44 161 835 1657
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread2629@stripped
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail mysql-unsubscribe@stripped instead.