I have an update query that sometimes gets stuck and locks the entire database. I have to ask the DBA to kill the query manually whenever this occurs. The status of the query in the db is "sleeping", as can be seen in the following screenshot.
I'm using MS SQL 2012 Server and hibernate-core 5.2.6-Final.
I've tried implementing a query timeout, but without success. The query happened to be stuck again even with the timeout. I've pasted the snippet of code that performs the update below:
try {
if (!entityManager.getTransaction().isActive()) {
entityManager.getTransaction().begin();
}
String query = "UPDATE FooBarTable i SET i.foo = :foo"
+ "WHERE i.id.bar = :bar AND i.id.foobar = :foobar";
entityManager.createQuery(query)
.setParameter("foo", foo)
.setParameter("bar", bar)
.setParameter("foobar", foobar)
.setHint("org.hibernate.timeout", "15")
.executeUpdate();
entityManager.getTransaction().commit();
} catch (Exception e) {
entityManager.getTransaction().rollback();
throw new Exception(e);
}
I've also tried inserting a "finally" block with a call to entityManager.close()
. Since the insertion of the finally
block, the error didn't happen yet.
But I am not confident it's solved for good. How do I make sure that a query won't be stuck and lock the database?