Re: 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