sic wrote: > I'm using commons-pool-1.3, commons-dbcp-1.2.2 > > Our business is quite simple. Using quartz, 30 simpleTriggers(5sec > repeatInterval) are triggered by 10 thread on quartz threadPool. > When any simpleTrigger is fired, it executes a job of doing DB(oracle) > query(select several rows and then update for them). > Configuration is as below : > <bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean"> > <property name="quartzProperties"> > <props> > <prop key="org.quartz.threadPool.threadCount">10</prop> > </props> > </property> > <property name="triggers"> > <list> > ... 30 simple triggers ... > </list> > </property> > </bean> > <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" > destroy-method="close"> > <property name="driverClassName" > value="oracle.jdbc.driver.OracleDriver" /> > <property name="url" value="..." /> > <property name="username" value="..." /> > <property name="password" value="..." /> > <property name="defaultAutoCommit" value="false"/> > <property name="initialSize" value="10"/> > <property name="maxActive" value="15"/> > <property name="maxIdle" value="10"/> > <property name="minIdle" value="10"/> > <property name="maxWait" value="60000"/> > <property name="testWhileIdle" value="false"/> > <property name="validationQuery" value="select 1 from dual"/> > <property name="testOnReturn" value="true"/> > <property name="timeBetweenEvictionRunsMillis" value="10000"/> > <property name="removeAbandoned" value="true"/> > <property name="removeAbandonedTimeout" value="10"/> > <property name="logAbandoned" value="true"/> > </bean> > > by this configuration, 10 threads are executed by using 10~15 connections > after firing at 30 triggers. > On rare occasion, however, DB sessions(confusing connection and session) are > not closed normally and the result is that they occupy a tremendous amount > of sessions. > > As a result of inquiring of DB "select * from v$session", it presents the > maximum number of session(more than 1000) allowed to this user and remains > INACTIVE status. And this status cannot be resolved until we shutdown this > process. > > What causes this abnormal states? We certainly expected that DB pool can > limit the number of Active session by the "maxActive" property no matter > what may happen, but it seems not to work occasionally. > > Any advice about this matter..
When posting to this list, please prefix the subject line with the name of the component, in this case [dbcp]. The list is shared by all commons components and it makes it easier for us to find and respond to posts if they do this. Thanks! Now to your question. 1) Your configuration will likely lead to a lot of connection churn. Setting maxIdle = minIdle = 10 when maxActive = 15 will force the pool to be constantly destroying excess connections and then adding new ones. You would likely be better off dropping both of these or at least setting maxIdle to a negative value (no limit). Your removeAbandonedTimeout is also set to a very low value. If your code is closing all connections (something you should check in any case), you should not need to use abandoned connection cleanup and in any case running the evictor every 10 seconds and timing out idle connections every 10 seconds is extreme. Your code should close its own connections and not require such aggressive cleanup. In pool 1.3, evictor runs lock the pool, so eliminating this if you can clean up the client code will improve throughput. 2) The latest versions of dbcp and pool are dbcp 1.3 (JDK 1.4-1.5) or dbcp 1.4 (JDK 1.6+) and pool 1.5.4. Upgrading to the latest versions may resolve your problem. You should address 1) in any case. Phil > > regards, > > sic > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
