Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

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?