Spring Data Pessimistic Lock timeout with Postgres

Question

I'm unsuccessful at getting Spring Data to send postgres a FOR UPDATE with NOWAIT.

I've tried this in the Repository:

@Lock(value = LockModeType.PESSIMISTIC_WRITE)
MyObject findByUuid(String uuid);

Configuration (snippet)

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    entityManagerFactoryRef = "docsEntityManagerFactory",
    transactionManagerRef = "docsTransactionManager",

@Bean(name = "docsEntityManagerFactory")
public EntityManagerFactory docsEntityManagerFactory(DataSource docsDataSource) {
props.setProperty("javax.persistence.lock.timeout", "0");
...

I even inject the EntityManager in my service and this returns 0:

logger.info(em.getProperties().get("javax.persistence.lock.timeout"));

but the above only gets me the "FOR UPDATE", the NOWAIT part isn't being set. Am I setting the right thing (from another post it looks like this should work).

This gets me closer, although I'd prefer not to use raw sql

 @Lock(value = LockModeType.PESSIMISTIC_WRITE)
 @Query(value = "SELECT * FROM the_table WHERE uuid = :uuid FOR UPDATE NOWAIT", nativeQuery = true)
 MyObject findByUuid(@Param("uuid") String uuid);

This does give me a Lock exception, but then the Spring decorated service (proxy) throws a Transaction/Rollback exception on the return, which means I can't give a return value on the method, that I need.

Questions: 1) How do I get Spring Data to add the NOWAIT? 2) Is there a way to return the value? I suspect I need to handle the transaction myself, or change the logic flow? Not sure if Spring handles the NOWAIT if it will behave differently.

I realize there is similar questions, but this is slightly different. I'm using hibernate 4.2.15, spring-data-jpa 1.7.1, postgres sql 9.3.

Spring Data JPA doesn't send anything to Postgresql, your JPA provider does. Does Hibernate (what you're presumably using) support addition of NOWAIT? — Neil Stockton, Jul 11 '15 at 07:16
Good question. Looking at the class we use (PostgresSQL82Dialect), it has the following implementation: getForUpdateNowaitString(...) { return this.getForUpdateString(...) + " nowait "; } Based on HH-6452 that should do it, is that the only thing to check? — Joel, Jul 13 '15 at 15:41
However, the dialect wasn't being set right so it wasn't working, now it is. That solved it, thanks @NeilStockton — Joel, Jul 13 '15 at 16:35

score 1 · Answer 1 · answered Nov 24 '19 at 20:17

javax.persistence.lock.timeout doesn't to be working for me either when provided like below

@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout",value = "15000")})

But then I tried something else which worked. Instead of using @Repository and using CrudRepository, now I am configuring my hibernate using the entity manager. Used createQuery along with lock and setting lock timeout. And this configuration is working as expected. I have two transactions running in parallel and trying to lock an exact same row in DB. The first transaction is able to acquire WRITE lock and holds the lock for around 10 secs before releasing the lock. Meanwhile, the second transaction tries to acquire lock on the same row but since javax.persistence.lock.timeout is set to 15 secs, it waits for lock to be released and then acquires its own lock. Hence making the flow serialized.

@Component
public class Repository {

    @PersistenceContext
    private EntityManager em;

    public Optional<Cache> getById(int id){
        List<Cache> list = em.createQuery("select c from Cache c where c.id = ?1")
                            .setParameter(1, id)
                            .setHint("javax.persistence.lock.timeout", 15000)
                            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
                            .getResultList();


        return Optional.ofNullable(list.get(0));
    }

    public void save(Cache cache) {
        cache = em.find(Cache.class, cache.getId());
        em.merge(cache);
    }
}

Make sure this lock mechanism is inside a transaction because lock will be released when either a transaction is committed or rollbacked.

score 0 · Answer 2 · answered Apr 07 '17 at 20:51

0

use PESSIMISTIC_FORCE_INCREMENT

answered Apr 07 '17 at 20:51

Java Spring Coder

1,017
3
12
20

5

While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations! – kayess Apr 08 '17 at 12:44
For unknown reason this actually helps – f1sherox Apr 28 '21 at 12:27

Spring Data Pessimistic Lock timeout with Postgres

2 Answers2

Linked