• Post Reply Bookmark Topic Watch Topic
  • New Topic

thread pool - Maxmim # of processes exceed  RSS feed

 
Neelima Garimella
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a server which is thread-safe, can server multiple clients concurrently. I had no problems when I had 5 -20 clients running concurrently and when the thread-pool size was set it 5.
But, when I increase the thread-pool size to 20 and when I try to run 20 clients..I am getting the SQL exception ..."MAXMIMUM NUMBER OF PROCESSES EXCEEDED(200)".
It would be great, if any one can post the reason for this.
Thanks in advance
Neelima
 
Robert Paris
Ranch Hand
Posts: 585
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, the error is in SQL so my guess is you're not closing the connections when you're done with them AND that you're not using a CONNECTION pool. With a connection pool, even though you have 20 clients, chances are you won't be opening a new connection for every new dealing with the database. It sounds like you're connecting to the DB numerous times in each thread, so a connection pool would GREATLY help here. Also, ALWAYS call close() on the connection (put it in a finally{} block).
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, if you do implement/use a connection pool, you probably don't want to close() each Connection after you use it - you want to return it to the pool, so it can be re-used. Many connection pool implementations solve this problem by overriding close() in the Connection so that merely returns the object to the pool, without "really" closing it. So in this case you do close() the connection, but you don't really close it. Very . Anyway, the point is, if you use a connection pool, make sure you know whether you're supposed to close() or not, and act accordingly.
Back to Neelima's question though - Robert's right, the problem is most likely that there are too many open connections/statements/resultsets. Assuming you're not currently using a connection pool - first, make sure you are in fact closing each connection when you're done with it (if you're really done with it). And close each Statement when you're done. And each ResultSet. Commonly you may find you can reuse the same Connection for many Statements, and/or use the same Statement for many different ResultSets - so close each as soon as you're really done with it, but not if you're going to reuse it. Garbage collection may take care of this for you, but don't count on it - use finally {} as Robert suggests. See if this solves your problem. Then if you still have a problem, or if you want to improve overall performance, look into setting up a connection pool.
[ February 13, 2003: Message edited by: Jim Yingst ]
 
Robert Paris
Ranch Hand
Posts: 585
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Uh, jim I said, "ALWAYS call close() on the connection" not call close on the PooledConnetion! This is in fact the way it works in the API. Your Pool simply registers as a listener for the call to Connection.close().
You're right that closing does not closed the connection's physical representation, but merely returns it to the pool. Sorry if I was unclear.
[ February 13, 2003: Message edited by: Robert Paris ]
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Beware that there's more than one connection pool implementation out there. JDK 1.4 does now include a PooledConnection (which despite its name is not a Connection). But before 1.4 there were numerous other variants created, and these may still be around. Take a look at this one for example. You're not supposed to call close() here on the connection - you call freeConnection() on the DBConnectionManager. Or see the PoolableConnection from Jakarta Commons DBCP. Here the PoolableConnection is a Connection, and you do call close() on it - but note the distinction between close() and reallyClose(). Personally I like this API a bit more than the PooledConnection, as it's easier to substitute into preexisting code that used a non-pooled Connection. But anyway - whatever connection pool implementation you use, study the docs carefully to see what cleanup you're expected to do. There's most always something to be done in a finally{} clause somewhere - but what that something is, may vary.
[ February 13, 2003: Message edited by: Jim Yingst ]
 
Neelima Garimella
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you all for your suggestion.
You're right, besides the connection pool I was using statement and I was not closing the statement.
That solves my problem.
 
Robert Paris
Ranch Hand
Posts: 585
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's right Jim, but I MUCH prefer the api with PooledConnection (I am a bit biased as I just built a ConnectionPoolManager using that). Consider the following non-pool-using code and the pool-using code (in both the datasource is in the servlet context):


You'll notice something interesting - there's NO difference! You can use my pool manager and change NO code! I have to say, this is one of the few areas where Java's SQL API scored a big 100%! I love it! I created a pool manager and then began using it in my old code with no changes and saw an ENORMOUS difference. The only thing I had to change was to add a small bit of instantiation code in another servlet that set the servletcontext's attribute. Pretty cool.
Now since this is the PooledConnection API, you're probably wondering where that is. Well, the API specs specifically say the developer should NEVER use this. It's internal to the Pool Manager. That's not too clearly stated though. so that's a HUGE area of confusion for most people and the first time I looked at it I thought, "what a dumb api," before i found out (in one small sentence buried in the spec on page 24 or something) that PooledConnection is hidden in implementation.
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, I see that now. Indeed I haven't really used this part of 1.4, so when you brought up PooledConnection I just looked at the API quickly - too quickly, as it turns out. So ignore my comments about PooledConnection, but keep my comments about not assuming that's the only way a connection pool might be implemented. (Though it's increasingly common now I guess.)
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Driver API and friends are now effectively obsolete. Whenever possible, you should use DataSource and get connection from there. The connections you get from a DataSource can be "raw" (as in DriverManager.getConnection), pooled, and even distributed. It makes no difference to your application; you use the Connection as if it were an ordinary one and close() after use.
You should never need to use PooledConnection or XAConnection directly unless you're writing application servers or other containers.
- Peter
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!