Re: [INTERFACES] DBI driver and transactions

From: "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>
To: Rudy Lippan <rlippan(at)remotelinux(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] DBI driver and transactions
Date: 2003-02-05 01:53:12
Message-ID: Pine.LNX.4.21.0302050143310.20150-100000@ponder.fairway2k.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-interfaces

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-02-05 02:25:33 Re: UPDATE slow
Previous Message Rudy Lippan 2003-02-05 01:39:34 Re: [INTERFACES] DBI driver and transactions

Browse pgsql-interfaces by date

  From Date Subject
Next Message Rudy Lippan 2003-02-05 03:46:21 Re: Prepare and prepare ?
Previous Message Rudy Lippan 2003-02-05 01:39:34 Re: [INTERFACES] DBI driver and transactions