Concurrency problem using spring, dbcp and postgres

Question

The software stack I'm using is: tomcat->spring-> hibernate-> DBCP -> postgreSQL

I have a query that search for some data using a column of type "timestamp without time zone".

If the application is tested in a single user mode, then there are no problems.

I'm using JMeter to make some stress test and can see that sometimes the query failed. This can only be reproduced if several users are accessing the application at the same time (more than 20 in the same second).

The error is something like:

org.postgresql.util.PSQLException: ERROR: timestamp out of range: "20120100-09-26 00:00:00.000000 -04:00:00"

org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062) org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795) org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479) org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367) org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271) org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) org.hibernate.loader.Loader.getResultSet(Loader.java:1808) org.hibernate.loader.Loader.doQuery(Loader.java:697) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) org.hibernate.loader.Loader.doList(Loader.java:2228) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) org.hibernate.loader.Loader.list(Loader.java:2120) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401) org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361) org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148) org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)

The versions that I'm using are:

tomcat 6.0.26
spring 3
DBCP 1.4
postgresql-8.4-701.jdbc4.jar
PostgreSQL Version: 8.4.4-0ubuntu10.04

Jon Freedman Jon Freedman · Accepted Answer · 2010-09-08T08:18:19

In your java code do you have non-threadsafe usage of a DateFormat object anywhere? Weird and wonderful date errors often stem from this sort of issue?

private static final DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

becomes

private static final ThreadLocal<DateFormat> fmt = new ThreadLocal<DateFormat>() {
    @Override
    protected DateFormat initialValue() {
        return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    }
}

See DateFormat, ThreadLocal

Concurrency problem using spring, dbcp and postgres

2 Answers