Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

> From: <kaiq(at)realtyideas(dot)com>
> On Fri, 25 Feb 2000, Karl DeBisschop wrote:
>
> >
> > >>To summarize, I stated that the following does not work with
> > >>postgresql:
> > >>
> > >>> $dbh->{AutoCommit} = 0;
> > >>> $dbh->do("CREATE TABLE tmp (a int unique,b int)");
> > >>> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
> > >>> if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
> > >>> $dbh->commit;
> > >>> $dbh->disconnect;
> > >>
> >
> > The usefulness of the idion is that in a mutli-user environment, this
> > is a basic way to update data that may or may not already have a key
> > in the table. You can't do a "SELECT COUNT" because in the time
> > between when you SELECT and INSERT (assuming the key is not already
> > there) someone may have done a separate insert. The only other way I
> > know to do this is to lock the entire table against INSERTs which has
> > obvious performance effects.

> sounds right, but ;-) why you use the transaction in the first place?

Rememeber that this is just an example to illustrate what sort of
behaviour one user would find useful in tranasctions, so it is a
little simplistic. Not overly simplistic, though, I think.

I'd want a transaction because I'm doing a bulk insert into this live
database - say syncing in a bunch of data from a slave server while
the master is still running. If one (or more) insert(s) fail, I want
to revert back to the starting pint so I can fix the cause of the
failed insert and try again with the database in a known state.
(there may, for instance, be relationships beteewn the b field such
that if only part of the bulk insert suceeds, the database is rendered
corrupt).

--
Karl DeBisschop <kdebisschop(at)alert(dot)infoplease(dot)com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net