Thread: Connection pool deadlock
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/
Anil Kumar <techbreeze@yahoo.com> writes:
> This is a Web application running on Tomcat. To render one of its
> pages it requires three concurrent connections to the database.
<blink> And this is a good design exactly why?
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Anil Kumar <techbreeze@yahoo.com> writes: > > This is a Web application running on Tomcat. To render one of > its > > pages it requires three concurrent connections to the database. > > <blink> And this is a good design exactly why? > Objects communicate with the database directly to store/retrieve their persistency data and page rendering has nothing to do with that. Anil __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Anil Kumar <techbreeze@yahoo.com> writes: > > This is a Web application running on Tomcat. To render one of > its > > pages it requires three concurrent connections to the database. > > <blink> And this is a good design exactly why? > > Objects communicate with the database directly to store/retrieve their persistency data and page rendering has nothing to do with that. Anil __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
Anil, Other than re-coding which is the real solution; (There is no reason for more than on connection per request) make sure your pool has multiple of 3 connections, ie 9, 12, 15, but not 10. Dave On Fri, 2003-02-28 at 00: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 -- Dave Cramer <Dave@micro-automation.net>
Hi Dave, The reason for multiple connections is that the Objects in my application contact the database directly for their persisitency requirements. And I am not creating any connection object in that jsp page directly. Instead I'm making calls to those objects and they in-turn contact the database to get the data. This is the reason for multiple connections. I think I can reduce the number of concurrent connections required while rendering that page but I cannot eliminated it completely. Me too had the idea of keeping the pool size as an integral multiple of 3 but didn't work :( --- Dave Cramer <Dave@micro-automation.net> wrote: > Anil, > > Other than re-coding which is the real solution; (There is no > reason for > more than on connection per request) make sure your pool has > multiple of > 3 connections, ie 9, 12, 15, but not 10. > > Dave > On Fri, 2003-02-28 at 00: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 > -- > Dave Cramer <Dave@micro-automation.net> > __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
Hi Csaba, The connection objects are requested by different objects in a single thread. As in mentioned in my reply to Dave, I'm not creating any connection objects directly in that jsp page. Increasing the pool size is not a bad idea but this is not a fool-proof solution because an unexpected traffic can still upset the pooling. The idea of synchronization won't work for me because connection request and release/close happen in different and independent Application Objects being called arbitrarily by that jsp page. But I think it is possible to give priority to those threads which have already taken connections from the pool if the pool size is less than 3 (in my case). Anil --- Csaba Nagy <nagy@ecircle-ag.com> wrote: > 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 > > > > __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
Anil, You can have multiple result sets on the same connection, as long as you create different statements. Why didn't the multiple of 3 thing work? Dave On Fri, 2003-02-28 at 05:46, Anil Kumar wrote: > Hi Dave, > > The reason for multiple connections is that the Objects in my > application contact the database directly for their persisitency > requirements. And I am not creating any connection object in that > jsp page directly. Instead I'm making calls to those objects and > they in-turn contact the database to get the data. This is the > reason for multiple connections. I think I can reduce the number of > concurrent connections required while rendering that page but I > cannot eliminated it completely. > > Me too had the idea of keeping the pool size as an integral > multiple of 3 but didn't work :( > > > --- Dave Cramer <Dave@micro-automation.net> wrote: > > Anil, > > > > Other than re-coding which is the real solution; (There is no > > reason for > > more than on connection per request) make sure your pool has > > multiple of > > 3 connections, ie 9, 12, 15, but not 10. > > > > Dave > > On Fri, 2003-02-28 at 00: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 > > -- > > Dave Cramer <Dave@micro-automation.net> > > > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, more > http://taxes.yahoo.com/ -- Dave Cramer <Dave@micro-automation.net>
--- Dave Cramer <Dave@micro-automation.net> wrote: ... > Why didn't the multiple of 3 thing work? If there are 3 connections left in the pool and 3 concurrent requests come in each will be provided with one connection and it will go to wait status when the first thread asks for its second connection. Anil > > Dave > On Fri, 2003-02-28 at 05:46, Anil Kumar wrote: > > Hi Dave, > > > > The reason for multiple connections is that the Objects in my > > application contact the database directly for their > persisitency > > requirements. And I am not creating any connection object in > that > > jsp page directly. Instead I'm making calls to those objects > and > > they in-turn contact the database to get the data. This is the > > reason for multiple connections. I think I can reduce the > number of > > concurrent connections required while rendering that page but I > > cannot eliminated it completely. > > > > Me too had the idea of keeping the pool size as an integral > > multiple of 3 but didn't work :( > > > > > > --- Dave Cramer <Dave@micro-automation.net> wrote: > > > Anil, > > > > > > Other than re-coding which is the real solution; (There is no > > > reason for > > > more than on connection per request) make sure your pool has > > > multiple of > > > 3 connections, ie 9, 12, 15, but not 10. > > > > > > Dave > > > On Fri, 2003-02-28 at 00: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 > > > -- > > > Dave Cramer <Dave@micro-automation.net> > > > > > > > > > __________________________________________________ > > Do you Yahoo!? > > Yahoo! Tax Center - forms, calculators, tips, more > > http://taxes.yahoo.com/ > -- > Dave Cramer <Dave@micro-automation.net> > __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
Ok, I think you can do a few things here. 1) make sure you give up your connections right away. This really should solve your problem. 2) don't block on your connection pool, instead return without a connection and tell the user that you can't service the request at this time. 3) change your connection pool so that you "reserve" 3 connections upon entry into the request thereby ensuring that it will complete. Realize however that you need to redesign your code to not keep the connections, or to share the connection. Dave On Fri, 2003-02-28 at 06:17, Anil Kumar wrote: > --- Dave Cramer <Dave@micro-automation.net> wrote: > ... > > > Why didn't the multiple of 3 thing work? > > If there are 3 connections left in the pool and 3 concurrent > requests come in each will be provided with one connection and it > will go to wait status when the first thread asks for its second > connection. > > Anil > > > > > > > Dave > > On Fri, 2003-02-28 at 05:46, Anil Kumar wrote: > > > Hi Dave, > > > > > > The reason for multiple connections is that the Objects in my > > > application contact the database directly for their > > persisitency > > > requirements. And I am not creating any connection object in > > that > > > jsp page directly. Instead I'm making calls to those objects > > and > > > they in-turn contact the database to get the data. This is the > > > reason for multiple connections. I think I can reduce the > > number of > > > concurrent connections required while rendering that page but I > > > cannot eliminated it completely. > > > > > > Me too had the idea of keeping the pool size as an integral > > > multiple of 3 but didn't work :( > > > > > > > > > --- Dave Cramer <Dave@micro-automation.net> wrote: > > > > Anil, > > > > > > > > Other than re-coding which is the real solution; (There is no > > > > reason for > > > > more than on connection per request) make sure your pool has > > > > multiple of > > > > 3 connections, ie 9, 12, 15, but not 10. > > > > > > > > Dave > > > > On Fri, 2003-02-28 at 00: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 > > > > -- > > > > Dave Cramer <Dave@micro-automation.net> > > > > > > > > > > > > > __________________________________________________ > > > Do you Yahoo!? > > > Yahoo! Tax Center - forms, calculators, tips, more > > > http://taxes.yahoo.com/ > > -- > > Dave Cramer <Dave@micro-automation.net> > > > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, more > http://taxes.yahoo.com/ -- Dave Cramer <Dave@micro-automation.net>
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 >
On 28 Feb 2003, Dave Cramer wrote:
> 2) don't block on your connection pool, instead return without a
> connection and tell the user that you can't service the request at this
> time.
I think our pooling implementation blocks when it runs out of
connections, though I'm not sure if he's using ours or not. We could
change that relatively easily to offer the option of throwing a
SQLException instead of blocking, if that would be valuable. But of
course we can't fix it to allow his code to use more connections than
there actually are present in the pool.
Aaron
On 6 Mar 2003, Csaba Nagy wrote:
> Why not ? The pool size could be dynamic, to accommodate the demand...
> IIRC, the apache HTTP connection pool is also configurable to
> grow/shrink. The same principles could apply here.
Let's say you set your pool size to 10, but really you're willing
to have it use up to 100 connections. So you complain that we block you
after 10. Instead, you can set it to 100 connections in the first place.
They still won't be actually opened unless they're needed. I wouldn't
want to make a totally open-ended pool, but if you do, you can set the
maxConnections to 0, which means "unlimited".
So the only issue here is shrinking, which was not implemented
because no one seemed to care. Is that still the case?
Aaron