If you exhaust your pool, there is not a lot you can do except increase your pool. You should also look at your code and see if you can optimize it to return your connections sooner.
It does sound like your implementation isn't very stable. DBCP ( and I think connection pooling in general ) is meant for pooling connections that have the same properties (user,password, server, etc). You may want to create a connection pool for each user, but that sounds like it might defeat the purpose of pooling for you. What exactly are you trying to do? Authenticate user's specifically, that way you can control what they have access to? - Andrew > -----Original Message----- > From: teletype [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, August 27, 2002 11:53 AM > To: Jakarta Commons Users List > Subject: RE: [DBCP] Connection pooling on MSSQL > > > Andrew Conrad <[EMAIL PROTECTED]> wrote: > > > Do you experience the problem when you use DBCP w/o your > custom DS > > factory? > > > > > No, I don't. Does it mean my approach is wrong? > (However I experience frequent blocks because of pool > exhaustion.) > > > You may ask why is not enough to specify user/password etc. > setting in a configuration file. This is because we have > to log database activity of users (insert, update, delete > only). > We wrote triggers for tables that can log operations with > the connected user login id. So after the user log in > the system this username/password information is also > used to connect the database. This is carried out by > passing environment filled out with username/password to > JNDI SPI > that creates an account initialized datasource. > > On the other hand there are login unaware components in the > system (eg. form validator) where select statements > executed only. > This case the default account settings used by JNDI SPI. > > I've attached the source code of the factory class. Please > look into, it very simple. > Thanks. > > > [Common-Logging] > > If you try this code the last lines shows you another > exception this time > with from commons-logging dispite I did the checklist for > Tomcat: > > - set JAVA_OPTS=- > Dlog4j.configuration=e:\kalman\projects\dbcp\conf\log4j.prop > erties > - copy log4j.properties %CATALINA_HOME%/commons/classes > - copy commons-logging.jar %CATALINA_HOME%/commons/lib > - copy commons-logging-api.jar %CATALINA_HOME%/commons/lib > - copy log4j-1.2.6.jar %CATALINA_HOME%/commons/lib > > content of log4j.properties file: > > log4j.rootCategory=DEBUG, fileAppender > log4j.logger.my.dbcp=DEBUG > log4j.appender.fileAppender=org.apache.log4j.FileAppender > log4j.appender.fileAppender.File=e:\\my.dbcp.txt > log4j.appender.fileAppender.Append=false > log4j.appender.fileAppender.Threshold=DEBUG > > > Regards, > tele > > > - Andrew > > > > > -----Original Message----- > > > From: teletype [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, August 27, 2002 7:58 AM > > > To: [EMAIL PROTECTED] > > > Subject: [DBCP] Connection pooling on MSSQL > > > > > > > > > Hello, > > > > > > Maybe others also encountered similar problems like us > > > so I decided to proclaim the issues we've experinced > using > > > DBCP > > > DataSource factory and MSSQL2000 JDBC driver. I've > > > implemented > > > a custom DataSource factory based on DBCP's > > > BasicDataSourceFactory with 2 additional advantages: > > > > > > - allow to override default configuration options > > > like username by the environment parameter of > > > getObjectInstance. > > > > > > - it perserves one BasicDataSource instance per user > in a > > > Map. > > > if username has been omitted it falls back to the > > > one secified in the config file (server.xml in > Tomcat). > > > > > > Let's look an example for usage: > > > > > > Hashtable env = new Hashtable(); > > > env.put("username", "Duke"); > > > env.put("password", "Earl"); > > > > > > InitialContext context = new InitialContext(env); > > > DataSource dataSource = > > > (DataSource) PortableRemoteObject.narrow > > > (context.lookup(jndiName), javax.sql.DataSource.class); > > > > > > That's it. I've tested the class and it worked fine > > > for a couple of minutes. But for a longer testing > > > (eg. more than about 5 minutes) it produces strange > results > > > on MSSQL: > > > > > > - This issue is same on Tyrex: > > > MSSQL seems to close connection in arbitrary > moments > > > irrespectively of Query time-out setting > > > (Enterprise Manager select server >> > > > Properties >> SQL Server Properties >> Connections > >> > > > Remote Server Connections >> Query time-out) and at > > > last end up > > > the JDBC driver throws exception: > > > > > > Caused by: java.sql.SQLException: [Microsoft][SQLServer > > > 2000 Driver for JDBC] > > > Object has been closed. > > > at > > > com.microsoft.jdbc.base.BaseExceptions.createException > > > (Unknown > > > Source) > > > at > > > com.microsoft.jdbc.base.BaseExceptions.getException > (Unknown > > > Source) > > > at > > > > com.microsoft.jdbc.base.BaseResultSet.validateClosedState > > > (Unknown > > > Source) > > > at com.microsoft.jdbc.base.BaseResultSet.wasNull > > > (Unknown Source) > > > at > > > org.apache.commons.dbcp.DelegatingResultSet.wasNull > > > (DelegatingResultSet.java:136) > > > > > > > > > - Tried a lot of different setting (removeAbandoned, > > > removeAbandonedTimeout etc.) > > > but eventually DBCP locks or if maxWait is set then > > > throws an exception: > > > "DBCP could not obtain an idle db connection, pool > > > exhausted" > > > - some exlcusive locks remain held on tables however > we > > > take care > > > to close connection in the finally blocks with a > util > > > method. > > > > > > > > > However timing out server connections by the server > > > itself > > > makes sense (resource sparing) I don't encounter > > > this "Object has been closed." problem on other > > > database servers > > > (mysql, oracle, etc.). > > > > > > This behaviour of MSSQL server make it impossible > to > > > make > > > connection pooling for it. > > > > > > The validationQuery helps nothing here because at > the > > > moment > > > when borrowing a connection may be valid but a > moment > > > later > > > the server may close it. > > > > > > From the user perspective this appication is > completly > > > instable > > > because this error appears in different times. > > > > > > Any comments? > > > > > > Regards > > > > > > > > > > > > > > > > > > -- > > > To unsubscribe, e-mail: > > > <mailto:[EMAIL PROTECTED]> > > > For > > > additional commands, e-mail: > > > <mailto:[EMAIL PROTECTED]> > > > > > > > > > -- > > To unsubscribe, e-mail: <mailto:commons-user- > [EMAIL PROTECTED]> > > For additional commands, e-mail: <mailto:commons-user- > [EMAIL PROTECTED]> > > > > > > > > > > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
