Re: Connection Pooling, a year later

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.

The general idea is that you grab a handle and hold onto it until you're
done. This makes the above scenario impossible.

Forgetting to commit or rollback before relenquishing the handle is
another scenario that can lead to problems but that's already been
discussed in detail.

--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org