Re: Connection Pooling, a year later

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?