Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-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

pgsql-interfaces by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group