1

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.

https://imgur.com/a/fFN6rlJ

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?

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
Pedro Rates
  • 41
  • 8
  • How many rows are being updated and is it a local or remote database? – fdkgfosfskjdlsjdlkfsf Jan 11 '19 at 13:13
  • @fdkgfosfskjdlsjdlkfsf 5 rows are being updated. It's a remote database. – Pedro Rates Jan 11 '19 at 13:21
  • Does the `update` also take unusually long if you execute it directly from sql server? – fdkgfosfskjdlsjdlkfsf Jan 11 '19 at 13:30
  • It does not. I've executed it using SQL Management Studio and it is fast. Not all updates done through code cause halting, only some, which makes it more difficult to debug. – Pedro Rates Jan 11 '19 at 15:05
  • Just to make sure that it's not the `if` statement and the persisting object, have you tried changing it to `if (entityManager.getTransaction().isActive()) { // All the code to UPDATE }` and removing the try/catch? The `update` should never happen and the app would break. – fdkgfosfskjdlsjdlkfsf Jan 11 '19 at 15:38
  • I tried removing the `if` statement and it kept locking the database. Actually, after removing it, the db got locked more frequently. Turns out that the cause of the problem seems to some async batch insertions in the database. I've removed them and didn't get any more locks. – Pedro Rates Jan 12 '19 at 17:11

0 Answers0