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?