JPA PESSIMISTIC_READ does not time out in the specified period

I am using Spring Boot, Spring Data, JPA(Hibernate) and MySQL in my project. Here is a method signature from my repository interface:

@Lock(LockModeType.PESSIMISTIC_READ)
@QueryHints({ @QueryHint(name = "javax.persistence.lock.timeout", value = "5000") })
@Query("select b from book b where b.id = :bookId")
Optional<Book> findBookCustom(Long bookId);

When I inspect the SQL logs, I can see that “for shared” clause is appended to the select statement.

This is a quote from MySQL documentation regarding shared locks:

If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

I can actually trigger the blocking behaviour by updating the book from another thread. When I suspend the updating thread (after book is updated) and then start the reading thread, I can see that the reading thread is blocked. The problem is that the reading thread waits for about 50 seconds (this is the default wait value, I suppose) until the timeout exception is thrown. The value 5000 I provide in QueryHint does not take effect. I also tried using the EntityManager directly to issue the JPQL select statement, but the result was same. Any ideas on how i can set the timeout to 5 seconds?

Answer

Can we check your MySQL setting via workbench or cmd-line:

select @@innodb_lock_wait_timeout

Then try to set it to a new value by:

set session innodb_lock_wait_timeout = 5

You could now acquire a pessimistic lock through JPA query, and it will set LockTimeout to 5 seconds. For more information check MySQL documentation.

Depending on the persistence provider and database in use, the hint may or may not be observed.

Hope this helps. Cheers!

UPDATE

If one do not wish to modify the session on their MySQL environment. One can put below setting to the application properties file:

spring.jpa.properties.javax.persistence.query.timeout=5000

Leave a Reply

Your email address will not be published. Required fields are marked *