On Fri, 25 Feb 2000, Karl DeBisschop wrote:
>
> > From: <kaiq@realtyideas.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).
>
thanks. I'm on your side now ;-) -- it is a useful senario.
the question are: 1) can nested transaction be typically interpreted
to handle this situation? If is is, then, it should be handled by that
"advanced feature", not plain transaction ;
2) on the other hand, can sql92's (plain) transaction be interpreted
in the way that above behavior is legitimate?
