How to set a timeout on a Hibernate save () call using the MySQL backend?

Question

Last week we ran into a problem in the large Java system, Hibernate. Our MySQL database database (hosted on Amazon RDS) did not respond for 5-10 minutes (it will still accept connections, but due to hardware problems, write throughput has dropped to zero). This piece of code:

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

Completion hangs about 8.5 minutes. It is so clear that this statement requires some kind of timeout condition so that it fails in the case of my specific scenario. I cannot guarantee that in the future I will not see a similar hardware problem.

I should mention that I'm still pretty new to Hibernate, so it's possible that I just don't understand some things, such as the relationship between using save() and using criteria, transactions, etc. So I found the following:

• hibernate.c3p0.timeout can be used to set connection timeouts in the C3P0 connection pool.
• getSession().getTransaction().setTimeout(...) can be used to turn off a transaction
• getSession().createQuery(...).setTimeout(...) can be used for request timeout
• I saw JPA 2 javax.persistence.query.timeout , but I'm not quite sure what I want (I also don't think my version of Hibernate is new enough)

None of this looks like what I want to do (except maybe JPA 2). It seems to be very simple. Is something missing here?

Thanks!

Answer 1

I searched this thing before, but used Oracle in the background instead of MySQL. As far as I have ever seen, there is no mechanism in this library, including JDBC drivers. Even the timeouts you listed, such as Transaction.setTimeout (), do not do what you expect. They simply wait for the database statement to complete, and then throw a TimeoutException if it exceeds the specified timeout. This is pretty useless if you have a query that just hangs endlessly. The only possible solution I have ever found is to set the request timeout for the database itself, which, according to Oracle, is a global parameter and will affect all database queries. This is not very helpful. As far as I can tell, in order to do what you are looking for, you will have to start a separate thread in temporary requests as they are created and somehow interrupt them when they press the timeout. I generally did not find support for this from Oracle or any db-related libraries / frameworks.

Answer 2

If the problem is responsiveness to the interface, you can use asynchronous database startup in any way. Then you are not required to support the backend. You can use the JMS bridge, or if you are in Spring, you can use @Async or anything else that is asynchronous.

If you started EJB, you can set the transaction timeout in the container or, if you use BMT, in the transaction itself. This parameter will end tx after a timeout.

In addition, you can also just move your local database.

Answer 3

• hibernate.c3p0.timeout ex: 300 You specify the waiting period (in this case, 300 seconds) after which an idle connection is removed from the pool). hibernate.c3p0.idle_test_periods This is the iddle time in seconds before the connection is automatically verified.