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
– Neil Stockton, Commented Jul 11, 2015 at 7: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
– Joel, Commented Jul 13, 2015 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
– Joel, Commented Jul 13, 2015 at 16:35

user1193815 · Accepted Answer · 2019-11-24 20:17:14Z

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.

Java Spring Coder · Accepted Answer · 2017-04-07 20:51:35Z

0

use PESSIMISTIC_FORCE_INCREMENT

answered Apr 7, 2017 at 20:51

Java Spring Coder

1,0273 gold badges12 silver badges20 bronze badges

2 Comments

kayess Over a year ago

While this code snippet may solve the question, including an explanation 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!

f1sherox Over a year ago

For unknown reason this actually helps

Collectives™ on Stack Overflow

Spring Data Pessimistic Lock timeout with Postgres

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related