There are generally two ways to fix this:
- rewrite the application to use a single connection, as you suggest. This
is the best solution. If you have to nest queries (i.e., running a query
while interating over the results of another) it's probably a sign that you
are doing something else wrong, like not taking advantage of database joins.
You'll fix your deadlocks and get better performance.
- use Java synchronization such that grabbing all three connections is an
atomic operation. This is not as good. You'll prevent deadlock, but
requests for this page will stall while other requests finish.
-- Bill
> Date: Thu, 27 Feb 2003 21:25:08 -0800 (PST)
> From: Anil Kumar <techbreeze@yahoo.com>
> To: pgsql-jdbc@postgresql.org
> Subject: Connection pool deadlock
> Message-ID: <20030228052508.79222.qmail@web13903.mail.yahoo.com>
>
> 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:
