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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2001-12-20 01:36:05 | Re: [PATCHES] Problem compiling postgres sql --with-tcl |
Previous Message | Ashley Cambrell | 2001-12-20 00:51:03 | Re: 7.2 is slow? [compile problem] |