Re: [INTERFACES] DBI driver and transactions

On Tue, 4 Feb 2003, Nigel J. Andrews wrote:

> > 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.
>
I do not think that connect_cached() is doing what you expect of it. When
you call disconnect() on your db handle it disconnects from the databse.
You were probably thinking of something like Apache::DBI which overloads
disconnect() so that it does not actually disconnect from the db.

> 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:
>
I think you are missing the point behind connect_cached()

> Object or Library 1:
> dbh1 = DBI->connect_cached
>
# DBI creates a connection to the database, and returns it. remembering
# that you connected with @stuff;

$dbh1 = DBI=>connect_cahced(@stuff);

> Object or Library 2:
> dbh2 = DBI->connect_cached
>
#DBI looks up sees that you already connected with @suff and returns
# a reference to handle that it already created (ie. $db1 == $dbh2)
$dbh2 = DBI->connect_cached(@stuff);

# now if you connect with @fooo -- you would get a different handle.
>
> 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.
>
because dbh1 == dbh2.

> 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).

If I can get away with it, I put $dbh in a global/package global.

>
> 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.
>
connect_cached() is not multiplexing anything. It just returns a cached
copy of a valid database handle. Everything is going over one connection
to the backend because there is only one object talking to the backend.
If you were to drop the connect_cached() and do two connect calls you
would get the behavior that you expect. Well, Except (maybe) for the
transaction behavour (depending on how you are using transactions) because
then you will be looking at to different data base connexions with
separate transactions -- And I don't think you want to go there.

>
> > 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.
>

I ment on exit from the sub, or more correctly the exit from the scope in
which local() was called. The reason for the commit is that
$dbi->{AutoCommit} gets changed from a value of 0 to a value of 1
(assuming that the value of $dbi->{AutoCommit} was 1 when you entered the
sub, of course). When autocommit is turned on DBD::Pg issues a commit.
And remember you are in transaction because the $dbi->commit/rollback()
automatically started another transaction for you.

-r