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, Apr 08 '16 at 10:20
For the question in the title see: https://stackoverflow.com/questions/49690671/spring-data-repository-query-update-and-return-modified-entity/61473038 — Jens Schauder, Apr 28 '20 at 05:12

score 1 · Answer 1 · edited May 23 '17 at 12:07

1

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;
}

edited May 23 '17 at 12:07

Community

1
1

answered Apr 07 '16 at 16:13

Aritz

30,971
16
136
217

Hi, your solution will work when I know the id but I don't know the id. 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. – krmanish007 Apr 07 '16 at 19:25
Just edited my answer. Go on with a single Spring transaction, it will achieve what you want. Anyway, it would be good to have your question updated with the details you provide in the comment above. – Aritz Apr 07 '16 at 20:29
it should work on multiple server, so transactional wouldn't be a solution. At last I may have to go with optimistic and pessimistic locking but only if there is no other option. – krmanish007 Apr 07 '16 at 21:46
1

'it should work on multiple server, so it's not a solution'. Well it is, depending on your db vendor and your architecture, actually. Transactions work at db level, so your clustered db must guarantee they are isolated properly. And PLEASE, edit your question with your whole requirements. – Aritz Apr 08 '16 at 04:43

score 1 · Answer 2 · answered Apr 28 '20 at 04:45

I suggest to use multiple transactions plus Optimistic Locking. Make sure your entity has an attribute annotated with @Version.

In the first transaction load the entity, mark it as USED, close the transaction.

This will flush and commit the changes and make sure nobody else touched the entity in the mean time.

In the second transaction you can no do whatever you want to do with the entity.

For these small transactions I find it clumsy to move them to separate methods so I can use @Transactional. I therefore use the TransactionTemplate instead.

update and return data in spring data JPA

2 Answers2