Re: Connection pool deadlock

Anil,

The best solution is of course to rewrite your app to use as few
connections/thread as possible (ideally 1).
Failing that you could make your connection pool flexible, so that it
can open more connections if all of them are checked out and there are
new requests. Of course there will be an upper limit of opened
connections, but you could set that high enough to avoid the deadlock
situation. The new connections could be closed again when the load peek
is over.
Another solution would be to place the code which opens more than 1
connection in a synchronized block, so that each of your pages get first
all the connections they need before another page can get any. This way
the deadlock is avoided, but there will be serialized access to the
connection checkout code (which is anyway kind of serialized I think).

HTH,
Csaba.

On Fri, 2003-02-28 at 06:25, Anil Kumar wrote:
> Hi All,
>
> I'm facing a problem with connection pooling. I'm using the
> pg73jdbc3.jar
> with PG 7.2 on RH 7.3.
>
> This is a Web application running on Tomcat. To render one of its
> pages it
> requires three concurrent connections to the database. It requests
> one
> connection after another and return them in the reverse order (the
> last
> obtained connection is returned first and so on). This creates a
> dead-lock
> if multiple requests are coming for this particular page. For
> example if
> the pool size is 10 and if the application is getting 4
> simultaneous
> requests for this page I found that the connection allocation
> happens in
> the following way:
>
> conn 1 -> request 1
> conn 2 -> request 2
> conn 3 -> request 3
> conn 4 -> request 4
> conn 5 -> request 1
> conn 6 -> request 2
> ...
> conn 10 -> request 2
>
> Here no request (or thread) will get enough number of connections
> to
> complete the request and all requests will go to an indefinite wait
> state.
> Of course this happens only when the connection requests reaches
> the pool
> capacity.
>
> I guess that some of you must have experienced this problem. What
> is the
> way out (other that re-coding the application to complete the
> request with
> single connection)?
>
> best regards
> Anil
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - forms, calculators, tips, more
> http://taxes.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>