How to set lock timeout in postgres - Hibernate

Question

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?

score 8 · Answer 1 · answered Aug 17 '18 at 11:36

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();

It won't work with Postgres... if provide 0 for javax.persistence.lock.timeout... NO_WAIT will trigger but with other values postgres ignores it. — Sagar Rout, Feb 23 '21 at 11:33

kan · Answer 2 · 2018-08-15T10:33:43.317

3

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.

edited Aug 15 '18 at 10:33

answered Aug 15 '18 at 10:27

kan

28,279
7
71
101

Watch out! This is only session local settings, see https://www.postgresql.org/docs/current/config-setting.html For global settings, see my answer https://stackoverflow.com/a/74208072/204950 – banterCZ Jun 21 '23 at 06:36

score 2 · Answer 3 · answered Aug 14 '18 at 20:32

2

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.

answered Aug 14 '18 at 20:32

Laurenz Albe

209,280
17
206
263

He wants to lock the row for the specific query only, not the entire database or user – orirab Aug 15 '18 at 09:07
Is there no way to run an SQL statement from Hibernate? `SET LOCAL lock_timeout = 1000` – Laurenz Albe Aug 15 '18 at 17:50

score 2 · Answer 4 · answered Apr 02 '19 at 19:11

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

score 0 · Answer 5 · answered Oct 26 '22 at 12:49

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

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;
$$;

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);

score 0 · Answer 6 · answered Jun 21 '23 at 06:34

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

JPA query hints are optional.

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;

How to set lock timeout in postgres - Hibernate

6 Answers6

Linked