Re: [GENERAL] Re: [HACKERS] TRANSACTIONS

kaiq(at)realtyideas(dot)com wrote:
>
> On Fri, 25 Feb 2000, Karl DeBisschop wrote:
>
> >
> > > 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).
> >
> 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 ;

I guess like this (got rid of AutoCommit, because that looks funny
nested):

$dbh->RaiseError = 1;
$dbh->StartTransaction;
eval {
$dbh->do("CREATE TABLE tmp (a int unique,b int)");
while (blahblahblah) {
$dbh->StartTransaction;
eval {
$dbh->do("INSERT INTO tmp VALUES ($1,$2)");
};
if ($@) {
$dbh->Rollback;
{$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
} else {
$dbh->Commit;
}
}
}
if ($@) {
$dbh->rollback;
} else {
$dbh->commit;
}
$dbh->disconnect;

I.e., try the INSERT within the inner transaction; if it fails, roll it
back and do the UPDATE; if that fails, blow out the whole outer
transaction.

You could do the whole thing checking a return value as in the original
example, but the eval and RaiseError are canonical, according the the
docs.

> 2) on the other hand, can sql92's (plain) transaction be interpreted
> in the way that above behavior is legitimate?
>
Well, I'm not sure of the necessity of nested transactions in the case
of continuing a transaction after a single-row insert has failed, but
that's implementation details I'm not familiar with... i.e., I'm not
having to code the danged thing!