16

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?

Improve this question

6 Answers 6

Reset to default
9
+25

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();
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

it's was a typo mistake,ive change the question
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.
4

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;
Improve this answer

1 Comment

And restart the database e.g.: sudo systemctl restart postgresql
3

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.

Improve this answer

2 Comments

He wants to lock the row for the specific query only, not the entire database or user
Is there no way to run an SQL statement from Hibernate? SET LOCAL lock_timeout = 1000
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.

Improve this answer

1 Comment

Watch out! This is only session local settings, see postgresql.org/docs/current/config-setting.html For global settings, see my answer stackoverflow.com/a/74208072/204950
2

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
Improve this answer

Comments

2

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);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.