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

Re: Connection Pooling, a year later

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: owensmk(at)earthlink(dot)net
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Connection Pooling, a year later
Date: 2001-12-20 00:52:28
Message-ID: 1008809548.24470.48.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, 2001-12-20 at 07:22, Michael Owens wrote:
> As long as each client's call is composed of a standalone transaction, there 
> is no problem with external connection pools. But what about when a client's 
> transactions spans two or more calls, such as SELECT FOR UPDATE? Then pooling 
> is not safe: it offers no assurance of what may be interjected into an open 
> transaction between calls. For example, each is a separate call to a shared 
> connection:
> 
> Client A:  BEGIN WORK; SELECT last_name from customer for update where <X>;
> 
> Client B:  BEGIN WORK; SELECT street from customer for update where <Y>;
> 
> Client A:  update customer set lastname=<modified value> where <X>; COMMIT 
> WORK;
> 
> 
> Now, isn't Client B's write lock gone with Client A's commit? Yet Client A's 
> lock is still hanging around. While Client B's commit will close it, Client B 
> has lost the assurance of its lock, defeating the purpose of SELECT FOR 
> UPDATE.
> 
> If this is corrent, then external connection pools limit what you can do with 
> the database to a single call. Any transaction spanning more than one call is 
> unsafe, because it is not isolated from other clients sharing the same 
> connection.

Oh, I see.  You are absolutely correct that client-side pooling wouldn't
work in that situation of course.

As an application developer nobody has forced me into such a corner yet,
however.  Long running transactions are something I avoid like the
plague.

Cheers,
					Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


In response to

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2001-12-20 01:36:05
Subject: Re: [PATCHES] Problem compiling postgres sql --with-tcl
Previous:From: Ashley CambrellDate: 2001-12-20 00:51:03
Subject: Re: 7.2 is slow? [compile problem]

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