Re: [INTERFACES] DBI driver and transactions - Mailing list pgsql-general

On Tue, 4 Feb 2003, Rudy Lippan wrote:

> On Mon, 3 Feb 2003, Nigel J. Andrews wrote:
>
> > Oh I give up. Trying to clarify what happens when I'm now seeing rollbacks
> > issued in AutoCommit => mode where I'm not doing any and it's certainly not me
> > since I've got a trap on my interface layer to DBI for that and it's not been
> > tripped at all.
> >
> IIRC, It will do a rollback for you on disconnect.

Yep, it was a disconnect that was doing that. What I wasn't expecting was a
disconnect on a cached connection (with others still open) to be rolling
back.

It sort of makes sense to do that on the assumption that each connection is
opened for a particular transaction and others are idle. However, there is only
one connection to the backend and to make that assumption the driver should
also not send any requests for a particular cached connection to the backend
until it sees a commit/rollback. Why? Well because it's silly to make the
assumption that a single cached connection close is terminating a transaction
when there can be another one interleaving queries with it. For example take
the situation:

Object or Library 1:
  dbh1 = DBI->connect_cached

Object or Library 2:
  dbh2 = DBI->connect_cached


main:

 do_selects_in_lib1();
 do_inserts_in_lib2();
 close_lib1_without_commit();
 ...all of a sudden here we find lib2's inserts have been rolled back.

It may be that I've taken an odd design route and that a more usual one is to
create a connection and pass that around instead of letting subsystems create
their own (it's not as though I don't need to pass the information to make the
connection around).

My point, however, is that the connect_cached method is multiplexing request
channels to a single processing unit. It is not normal for one such channel to
invalidate all others, or indeed validate (commit), all others. On the one hand
DBI is saying each channel is a separate entity, otherwise it wouldn't rollback
when only one closes, but on the other hand all channels are one, otherwise it
wouldn't allow query interleaving, i.e. would have locking on the channel to
the server.


> > So,
> >
> > a) anyone know of any problems,
> >
> > b) can I assume it is me and not just give up on this DBI stuff in the future (
> > I haven't got the time to rewrite my application now; not that I can't see it
> > being a particularly onerous task for what I use from DBI )
> >
> > c) can I just issue BEGIN, COMMIT, ROLLBACK when I want to via normal
> > $dbh->do(...) without causing myself even more problems?
> >
>
> Yes and no. DBD::Pg will stop you from calling those commands;
> however, I think that you can trick DBD::Pg by make it not look like a
> transaction command (eg. $dbh->do(q{/* Trick Pg */ BEGIN}) ); although, I
> have not tried it, and it is not recommended.
>
> I, for the most part, do:
>
> sub stuff {
>     local($dbi->{AutoCommit}) = 0;
>
>     eval {
>         #do transaction stuff.
>         $dbi->commit();
>     }; if (my $e = $@)
>         $dbi->rollback();
>     }
>    # $on exit $dbi->{AutoCommit} will go to 1 and DBD::Pg will call
>    # commit() for you
> }
>
> DBI now supports a begin_work method, but DBD::Pg does not have
> support for it at this time.

Interesting. Just wondering though, why the comment on exit calling
commit? Within your eval block you've already done the commit and dbi won't
issue another one until another statement is executed.


--
Nigel J. Andrews