jdbcTemplate hangs on long update


jdbcTemplate hangs on long update



I recently switched to Spring Framework instead of manually handling JDBC, and it is mostly a good transition. One program started having strange problems, though: if the database is slow, when calling getJdbcTemplate().update( ... ) it sometimes never returns.

After researching a little bit, I switched from Apache DBCP to C3PO, but the problem still came back.

Here's the code I'm using:

public class MyDao extends SimpleJdbcDaoSupport {     private static Logger logger = Logger.getLogger(MyDao.class);      public MyDao(Config config) {         super();          ComboPooledDataSource cpds = new ComboPooledDataSource();         try {             cpds.setDriverClass("com.mysql.jdbc.Driver");         } catch (PropertyVetoException e) {             throw new RuntimeException(e);         }         cpds.setUser("username");         cpds.setPassword("password");         cpds.setJdbcUrl("jdbc:mysql://localhost/schema" +                          "?useUnicode=true&characterEncoding=UTF-8");         cpds.setMaxStatements( 180 );         cpds.setPreferredTestQuery("SELECT 1");         cpds.setTestConnectionOnCheckout(true);          this.setDataSource(cpds);     }      public void addToWorkQueue(String item) {         long[] ids = Utils.getItemIds(item);          try {             logger.debug("About to insert to work table");             getJdbcTemplate().update(                     "INSERT IGNORE INTO work " +                     "SELECT * FROM queue WHERE id_1 = ? AND id_2 = ?",                     new Object[] { ids[0], ids[1] }             );         } finally {             logger.debug("Updated work table");         }     } } 

Here's what it looks like in the log file:

2009-07-29 17:37:13.570 com.mycomp.MyDao About to insert into work table 2009-07-29 17:37:13.570 com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@170984c] on CHECKOUT. 2009-07-29 17:37:13.571 com.mchange.v2.c3p0.stmt.GooGooStatementCache checkinAll(): com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 1; checked out: 0; num connections: 1; num keys: 1 2009-07-29 17:37:13.571 com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@170984c] on CHECKOUT has SUCCEEDED. 2009-07-29 17:37:13.571 com.mchange.v2.resourcepool.BasicResourcePool trace com.mchange.v2.resourcepool.BasicResourcePool@d402dd [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@170984c) 2009-07-29 17:37:13.571 com.mchange.v2.c3p0.stmt.GooGooStatementCache com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache ----> CACHE HIT 2009-07-29 17:37:13.571 com.mchange.v2.c3p0.stmt.GooGooStatementCache checkoutStatement: com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 1; checked out: 1; num connections: 1; num keys: 1 

This is where the code hangs. Usually it just goes on like this:

2009-07-29 17:37:13.762 com.mchange.v2.c3p0.stmt.GooGooStatementCache checkinStatement(): com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 1; checked out: 0; num connections: 1; num keys: 1 2009-07-29 17:37:13.763 com.mchange.v2.c3p0.stmt.GooGooStatementCache checkinAll(): com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 1; checked out: 0; num connections: 1; num keys: 1 2009-07-29 17:37:13.763 com.mchange.v2.resourcepool.BasicResourcePool trace com.mchange.v2.resourcepool.BasicResourcePool@d402dd [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@170984c) 2009-07-29 17:37:13.763 com.mycomp.MyDao Updated work table 

I don't know why I'm not getting any log message from Spring Framework itself. I added these lines in my main code:

Logger springLogger = Logger.getLogger("org.springframework"); springLogger.setLevel(Level.TRACE); springLogger.debug("testing spring logger"); 

The test message shows, but nothing else. Sorry for diverging.

I did notice a slowdown before the hang. The last time the query ran successfully it took a minute and a half to finish, instead of the usual 200ms. The next time, I let it run for 25 minutes before killing the process.

I know I have some problems with my database (InnoDB), which I'm working on, but this seems like after a timeout, Spring Framework just "gives up" and hangs.

Any advice would be appreciated.




Receiving an array of Object from a web form using Spring framework

1:



Eclipse keeps changing the Targeted Runtimes
Eventually, the problem was avoided by fixing the underlying DB issue..
How to use Spring to inject an object in a setter that doesnt follow the Java bean specification?
I was using an InnoDB table as a work queue, which meant I added and removed a whole lot of items to it.


Bulk inserts with Spring / Hibernate
The table never had too many rows at a given time, but apparently InnoDB can't handle this sort of work, or as my DBA friend put it, "deleting rows from a table doesn't do anything for performance"..
programmatically create a spring context
After switching to a much crazier db strategy, which involved creating and dropping tables all the time, the performance was much improved and the hangs went away..
Spring Injecting into Struts RequestProcessor
So I guess what I'm saying is, skaffman's comment was probably right.


Spring Open JPA
This didn't have anything to do with Spring..
What is the simplest solution to integrate 2 apps within a Tomcat server?



94 out of 100 based on 84 user ratings 334 reviews