update and return data in spring data JPA

Question

For concurrency purpose, I have got a requirement to update the state of a column of the database to USED while selecting from AVAILABLE pool.

I was thinking to try @Modifying, and @Query(query to update the state based on the where clause)

It is all fine, but this is an update query and so it doesn't return the updated data.

So, is it possible in spring data, to update and return a row, so that whoever read the row first can use it exclusively.

My update query is something like UPDATE MyObject o SET o.state = 'USED' WHERE o.id = (select min(id) from MyObject a where a.state='AVAILABLE'), so basically the lowest available id will be marked used. There is a option of locking, but these requires exceptional handling and if exception occur for another thread, then try again, which is not approved in my scenario

You still need locking and exception handling in this case, because how else are you going to prevent concurrent requests accessing the same row... — M. Deinum
For the question in the title see: stackoverflow.com/questions/49690671/… — Jens Schauder

Xtreme Biker Xtreme Biker · Accepted Answer · 2016-04-07T16:13:46

You need to explicitly declare a transaction to avoid other transactions being able to read the values involved until it's commited. The level with best performance allowing it is READ_COMMITED, which doesn't allow dirty reads from other transactions (suits your case). So the code will look like this:

Repo:

@Repository
public interface MyObjectRepository extends JpaRepository<MyObject, Long> {

    @Modifying
    @Query("UPDATE MyObject o SET o.state = 'USED' WHERE o.id = :id")
    void lockObject(@Param("id") long id);

    @Query("select min(id) from MyObject a where a.state='AVAILABLE'")
    Integer minId();
}

Service:

@Transactional(isolation=Isolation.READ_COMMITTED)
public MyObject findFirstAvailable(){
    Integer minId;
    if ((minId = repo.minId()) != null){
        repo.lockObject(minId);
        return repo.findOne(minId);
    }
    return null;
}

update and return data in spring data JPA

2 Answers