From: Robin Bowes
Date: April 29 1999 3:09pm
Subject: Re: About transaction support
List-Archive: http://lists.mysql.com/mysql/2629
Message-Id: <37287628.D699ED10@eoc.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

efrazier@stripped wrote:
> 
> Hi,
> 
> 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
> etc?

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:

Begin transaction
	1. Insert $100 into accountA
	2. Delete $100 from accountB
end transaction
If no errors then
	Committ transactions
else
	Rollback transactions

If anything fails between 1. and 2. then action 1. (the Insert) is not
committed.

>  Is it more than that? I am running into this a bit now because to add a new client
> 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.

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