1

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?

sanemain
  • 139
  • 1
  • 13

2 Answers2

1

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
junbetterway
  • 236
  • 3
  • 13
  • Thanks for the help. The result for the first select is 50. My preference is to solve the issue via spring, JPA mechanisms if possible at all. I really don't want to set session parameter at this point, seems too global to me. It would be better if I could handle this case at the query/transaction level. The solution you proposed will be my last resort though:) Do you know how to set session param in a spring app? Should i use application.properties file? – sanemain Mar 03 '21 at 23:53
  • There are similar questions related to PESSIMISTIC_WRITE on stackoverflow. The accepted answers say QueryHint works for that case. I have not tested that, though. I started to think that the case with PESSIMISTIC_READ is not implemented by the developers or maybe it is a bug... – sanemain Mar 04 '21 at 00:02
  • Hi @sanemain - yes you can use `spring.jpa.properties.javax.persistence.query.timeout=5000` on the property file, but if you don't mind can you also share the property/yml file of your application from your question. I am curious on the MySQL dialect you used for jpa. Thanks – junbetterway Mar 04 '21 at 00:23
  • Are you already using this property: `spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect`? As mentioned from this [answer](https://stackoverflow.com/a/60368500/11641618) it has something to do with the store engine being used by the MySQL dialect. – junbetterway Mar 04 '21 at 00:41
  • I already checked the other question before asking this question. MySQL dialect i am using is org.hibernate.dialect.MySQL8Dialect, so that should not be a problem. The property setting you suggested works and I I will use that. Thanks for the help again:) – sanemain Mar 04 '21 at 13:11
  • Glad it helped you. I will just update the answer to include the property approach. Cheers! – junbetterway Mar 04 '21 at 14:16
0

MySQL does not support query lock timeout and for this reason @QueryHints({ @QueryHint(name = "javax.persistence.lock.timeout", value = "5000") }) is ignored

enter image description here

For more details: https://blog.mimacom.com/handling-pessimistic-locking-jpa-oracle-mysql-postgresql-derbi-h2/

Rafael Pivetta Balbuena
  • 11
  • 4