How can I set a timeout on a Hibernate save() call with a MySQL backend?

Question

We ran into a problem in a large, Java, Hibernate-based system last week. Our backend MySQL database (hosted on Amazon RDS) went unresponsive for 5-10 minutes (it would still accept connections, but due to hardware issues, its write throughput dropped to zero). This piece of code:

getSession().save(entity); //session is an instance of org.hibernate.Session

Ended up hanging for about 8.5 minutes. So clearly there's a need for some sort of timeout condition on this statement to make it fail in the case of my particular scenario. I can't guarantee that I won't see a similar hardware issue in the future.

I should mention that I'm still fairly new to Hibernate, so it's possible that I just don't understand some things like the association between using save() versus using Criteria, Transactions, etc. So I've found the following:

• hibernate.c3p0.timeout can be used to set connection timeouts on the C3P0 connection pool
• getSession().getTransaction().setTimeout(...) can be used to timeout a transaction
• getSession().createQuery(...).setTimeout(...) can be used to timeout a query
• I've seen the JPA 2 javax.persistence.query.timeout, but I'm not entirely sure it's what I want (I also don't think my Hibernate version is new enough)

None of these seems like exactly what I want to do (except maybe the JPA 2 one). This seems like it should be really simple. Is there something I'm missing here?

Thanks!

Answer 1

I've looked for this same kind of thing before, but using Oracle on the back end instead of MySQL. As far as I've ever seen, there just isn't any mechanism for this in any library, including the JDBC drivers. Even the timeouts you listed, like Transaction.setTimeout(), don't do what you'd expect. They just wait for a database statement to finish executing, and then throw a TimeoutException if it exceeded the given timeout. That's pretty useless if you have a query that's just hanging indefinitely. The only potential out-of-the-box solution I've ever found is to set a query timeout on the database itself, which on Oracle is a global setting and will affect all queries against the database. That's not terribly useful. As far as I can tell, to do what you're looking for, you'd have to start a separate thread to time queries as they're made and somehow interrupt them when they hit the timeout. I haven't found any support at all for doing this from either Oracle or any db-related libraries/frameworks.