Thanks Phil for the prompt reply. I'll try this again with maxWait = 1 and check the results and get back.
Regards, Purnima On Mon, Mar 3, 2008 at 7:53 PM, Phil Steitz <[EMAIL PROTECTED]> wrote: > On Mon, Mar 3, 2008 at 6:36 AM, Purnima Venkatram > <[EMAIL PROTECTED]> wrote: > > Hi all. > > > > I am using commons dbcp through Tomcat. > > > > Configuration: > > > > 1) Spring 2.5.1 > > 2) Tomcat 5.5.25 > > 3) mysql 5.0.21. > > > > Have put the commons-dbcp-1.2.2.jar / commonds-pool-1.4.jar / > > commonds-logging-1.0.4.jar into tomcat's common/lib. > > > > The dataSource is accessed through a JNDI lookup. I want to check > connection > > pooling. > > > > For the same I tried the following for the dataSource definition. > > > > In context.xml I have: > > > > <Resource > > name="jdbc/XYZ" > > auth="Container" type="javax.sql.DataSource" > > maxActive="1" > > maxWait="0" > > maxIdle="0" > > validationQuery="SELECT 1" > > username="test" > > password="test123" > > driverClassName="com.mysql.jdbc.Driver" > > factory="org.apache.commons.dbcp.BasicDataSourceFactory" > > logAbandoned="true" > > url="jdbc:mysql://127.0.0.1:3306/abc"/> > > > > Setting maxActive to 1 means that only 1 connection will be allocated > at the > > same time. Which means that If i establish two concurrent requests to > the > > application, then the second one should fail. > > Not necessarily, depending on configuration, the second request may > wait for a connection to be returned to the pool. Unfortunately, 0 is > interpreted similarly to the default, -1, as a configuration parameter > for maxWait, so maxWait = 0 means wait indefinitely. (This behavior > was introduced in early versions of commons pool and has not been > changed to preserve backward compatability). There is no way to get > BasicDataSource to fail *immediately* when the pool is exhausted. To > get that behavior, you would need to set up the pool yourself and > create a PoolingDataSource from it, setting the whenExhaustedAction to > WHEN_EXHAUSTED_FAIL. Setting maxWait to a very small positive number, > e.g. 1, with BasicDataSource should effectively accomplish the same > thing, though. > > I don't see this happen. I > > initially thought that I had very few rows in the db and hence the > mysql > > operation was finishing quickly due to which the second request was > also > > getting processed. But putting a loop and executing the mysql statement > also > > results in both the requests getting served. > > > > I tried this with Jmeter too. I tried a Forever loop with Jmeter. And > during > > that time maxActive=1 and then from the browser again tried a request > to the > > jsp and this time I got a 'JDBC exception : Communications link > failure' - > > Address already in use. If i stop Jmeter tests, an redo the browser > test, it > > only succeeds after a while. ie the first few runs still fail with the > same > > exceptions and then it seems like the connections are released and then > this > > call succeeds. But in this time interval , in mysql 'show processlist' > I > > dont see the connections. > > > > I have tried this for a lot of time and any suggestions/comments would > be > > appreciated. > > > > I have checked the source of BasicDataSource and saw that it uses the > > ObjectPool and PoolableConnectionFactory. Hence am assuming there isnt > > anything else that I need to do to get pooling to work. > > I also tried deleting the naming-dbcp jar from tomcat's common lib > since > > that seemed to have the same classes as commons-dbcp. > > > > Is anything else required ? > > > > In spring this is accessed using getBean and then the corresponding dao > > method is called. > > > > Also, as soon as it finishes the operation, the connection is removed > from > > the 'show processlist' of mysql. Does that mean , Tomcat drops and > creates > > connections for every call ? Isn't that against connection pooling ? > Most of > > the time I see the status as 'null' / 'sleep' / 'query' with > validatation > > query and very less time for the actual select query. > > > I am not a MySql expert, but IIRC, show processlist reports on > *running* threads, so it does not show idle connections. The one > active connection will be idle between requests or when the engine has > finished responding. > > Phil > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
