CODEWITHSUNDEEP
javapostgresqlhibernatejpa
Daniel Taub
Daniel Taub

Reputation: 5399

How to set lock timeout in postgres - Hibernate

I'm trying to set a Lock for the row I'm working on until the next commit:

entityManager.createQuery("SELECT value from Table where id=:id")
            .setParameter("id", "123")
            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
            .setHint("javax.persistence.lock.timeout", 10000)
            .getSingleResult();

What I thought should happen is that if two threads will try to write to the db at the same time, one thread will reach the update operation before the other, the second thread should wait 10 seconds and then throw PessimisticLockException.

But instead the thread hangs until the other thread finishes, regardless of the timeout set.

Look at this example :

database.createTransaction(transaction -> {
    // Execute the first request to the db, and lock the table
    requestAndLock(transaction);

    // open another transaction, and execute the second request in
    // a different transaction
    database.createTransaction(secondTransaction -> {
        requestAndLock(secondTransaction);
    });

    transaction.commit();
});

I expected that in the second request the transaction will wait until the timeout set and then throw the PessimisticLockException, but instead it deadlocks forever.

Hibernate generates my request to the db this way : 

SELECT value from Table where id=123 FOR UPDATE

In this answer I saw that Postgres allows only SELECT FOR UPDATE NO WAIT that sets the timeout to 0, but it isn't possible to set a timeout in that way.

Is there any other way that I can use with Hibernate / JPA? Maybe this way is somehow recommended?

Upvotes: 16

Views: 16633

Answers (6)

banterCZ
banterCZ

Reputation: 1871

Hibernate has refused to do any workaround (no offense), see https://hibernate.atlassian.net/browse/HHH-16071, since

  1. JPA query hints are optional.
  2. Postgres itself does not support statement-level lock timeouts.

But there is an option to globally configure lock_timeout parameter, see https://postgresqlco.nf/doc/en/param/lock_timeout/

ALTER DATABASE name SET lock_timeout=10000;

To print current value, use

show lock_timeout;

Upvotes: 4

ALex
ALex

Reputation: 800

For anyone who's still looking for a data jpa solution, this is how i managed to do it

  1. First i've created a function in postgres
CREATE function function_name (some_var bigint)
RETURNS TABLE (id BIGINT, counter bigint, organisation_id bigint) -- here you list all the columns you want to be returned in the select statement
LANGUAGE plpgsql
AS
$$
BEGIN
    SET LOCAL lock_timeout = '5s';
    return query SELECT * from some_table where some_table.id = some_var FOR UPDATE;
END;
$$;
  1. then in the repository interface i've created a native query that calls the function. This will apply the lock timeout on that particular transaction
  @Transactional
  @Query(value = """
      select * from function_name(:id);
      """, nativeQuery = true)
  Optional<SomeTableEntity> findById(Long id);

Upvotes: 2

LubošCZ
LubošCZ

Reputation: 101

The hint for lock timeout for PostgresSQL doesn't work on PostreSQL 9.6 (.setHint("javax.persistence.lock.timeout", 10000)

The only solution I found is uncommenting lock_timeout property in postgresql.conf:

lock_timeout = 10000            # in milliseconds, 0 is disabled

Upvotes: 2

Thorben Janssen
Thorben Janssen

Reputation: 3275

Hibernate supports a bunch of query hints. The one you're using sets the timeout for the query, not for the pessimistic lock. The query and the lock are independent of each other, and you need to use the hint shown below.

But before you do that, please be aware, that Hibernate doesn't handle the timeout itself. It only sends it to the database and it depends on the database, if and how it applies it.

To set a timeout for the pessimistic lock, you need to use the javax.persistence.lock.timeout hint instead. Here's an example:

entityManager.createQuery("SELECT value from Table where id=:id")
        .setParameter("id", "123")
        .setLockMode(LockModeType.PESSIMISTIC_WRITE)
        .setHint("javax.persistence.lock.timeout", 10000)
        .getSingleResult();

Upvotes: 9

kan
kan

Reputation: 28981

I think you could try

SET LOCAL lock_timeout = '10s';
SELECT ....;

I doubt Hibernate supports this out-of-box. You could try find a way to extend it, not sure if it worth it. Because I guess using locks on a postges database (which is mvcc) is not the smartest option.

You could also do NO WAIT and delay-retry several times from your code.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 248030

There is the lock_timeout parameter that does exactly what you want.

You can set it in postgresql.conf or with ALTER ROLE or ALTER DATABASE per user or per database.

Upvotes: 3

Related Questions