Re: [INTERFACES] DBI driver and transactions

On Tue, 4 Feb 2003, Rudy Lippan wrote:

> On Tue, 4 Feb 2003, Nigel J. Andrews wrote:
>
> > 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.
>
> ...
>
> >
> > 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.

Bingo. Thanks for the explanation. You are indeed correct I was
misunderstanding the cached connection stuff was doing.

I thought it was doing something more like how I changed my usage to use
connect() and a reference count. My connection only gets closed when there the
reference count drops back to zero. To me that's much more sensible a scheme.

--
Nigel J. Andrews