What's the proper way to handle JDBC connections with Spring and DBCP?

Question

I'm using the Spring MVC to build a thin layer on top of a SQL Server database. When I began testing, it seems that it doesn't handle stress very well :). I'm using Apache Commons DBCP to handle connection pooling and the data source.

When I first attempted ~10-15 simultaneous connections, it used to hang and I'd have to restart the server (for dev I'm using Tomcat, but I'm gonna have to deploy on Weblogic eventually).

These are my Spring bean definitions:

<bean id="dataSource" destroy-method="close"
      class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="[...]"/>
    <property name="username" value="[...]" />
    <property name="password" value="[...]" />
</bean>

<bean id="partnerDAO" class="com.hp.gpl.JdbcPartnerDAO">
    <constructor-arg ref="dataSource"/>
</bean>

<!-- + other beans -->

And this is how I use them:

// in the DAO
public JdbcPartnerDAO(DataSource dataSource) {
    jdbcTemplate = new JdbcTemplate(dataSource);
}

// in the controller
@Autowired
private PartnerDAO partnerDAO;

// in the controller method
Collection<Partner> partners = partnerDAO.getPartners(...);

After reading around a little bit, I found the maxWait, maxActive and maxIdle properties for the BasicDataSource (from GenericObjectPool). Here comes the problem. I'm not sure how I should set them, performance-wise. From what I know, Spring should be managing my connections so I shouldn't have to worry about releasing them.

<bean id="dataSource" destroy-method="close"
      class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="[...]"/>
    <property name="username" value="[...]" />
    <property name="password" value="[...]" />
    <property name="maxWait" value="30" />
    <property name="maxIdle" value="-1" />
    <property name="maxActive" value="-1" />
</bean>

First, I set maxWait, so that it wouldn't hang and instead throw an exception when no connection was available from the pool. The exception message was:

Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

There are some long-running queries, but the exception was thrown regardless of the query complexity.

Then, I set maxActive and maxIdle so that it wouldn't throw the exceptions in the first place. The default values are 8 for maxActive and maxIdle (I don't understand why); if I set them to -1 there are no more exceptions thrown and everything seems to work fine.

Considering that this app should support a large number of concurrent requests is it ok to leave these settings to infinite? Will Spring actually manage my connections, considering the errors I was receiving? Should I switch to C3P0 considering it's kinda dead?

Answer 1

Let's change the perspective.

but the exception was thrown regardless of the query complexity

It could be because the table or the records in the table, which you are querying against has been locked (by some other active transaction) and hence it times out.

Try running the same query from SQLServer Client and if it takes a long time, then you can be sure that it is the table or record lock that is causing this.

Answer 2

As you already found out, the default dbcp connection pool is 8 connections, so if you want to run 9 simultaneous queries one of them will be blocked. I suggest you connect to your database and run exec sp_who2 which will show you what is connected, and active, and whether any queries are being blocked. You can then confirm whether the issue is on the db or in your code.

As long as you are using Spring's JdbcTemplate family of objects your connections will be managed as you expect, and if you want to use a raw DataSource make sure you use DataSourceUtils to obtain a Connection.

One other suggestion - don't ever using JdbcTemplate, stick to SimpleJdbcTemplate, you can still access the same methods using SimpleJdbcTemplate.getJdbcOperations(), but you should find yourself writing much nicer code using generics, and remove the need to ever create JdbcTemplate/NamedParameterJdbcTemplate instances.