CODEWITHSUNDEEP
javamysqlsqlspringspring-data-jpa
Kency Kurian
Kency Kurian

Reputation: 302

Spring Data not updating on latest data

I have a field in my SQL table which needs to be updated by one and return a unique ID. But looks like it is not being updated on the latest data, especially when I give a lot of requests.

@Transactional
public interface CompanyRepository extends CrudRepository<Company, Integer> {    
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query(value = "update Company c set c.accessId = :accessId WHERE c.id = :companyId AND c.accessId = :oldAccessId", nativeQuery = true)
    int updateAccessId(@Param("companyId") Integer companyId, @Param("accessId") Integer accessId, @Param("oldAccessId") Integer oldAccessId);
}

Even with both clearAutomatically and flushAutomatically set to true, it is not working on the latest data.

I could see two update query being successful both with oldAccessId as the same.

Should the table design be changed?

PS : I have tried without nativeQuery = true as well.

Upvotes: 0

Views: 1570

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81988

What you have here is a classical race condition.

Two threads read the same entity, with identical accessId, increment it by one and then writing the result using the method you show in your question. Resulting in effectively only one update.

There are various ways how to fix this.

  1. Use JPA and optimistic locking.

    Assuming you have an attribute with @Version annotated you can do the following in a single transactional method:

    1. Load the entity.
    2. increment the accessId.
    3. persist the entity.

    If another transaction tries to do the same on the same entity one of the two will get an exception. In that case retry until the update goes through.

  2. Use the database.

    Make reading and updating atomic in the database. Instead of passing the new value as parameter use a query like this one: 

    update Company c 
set c.accessId = c.accessId + 1 
WHERE c.id = :companyId

  3. Make it a version attribute.

    As mentioned above JPA already has @Version attributes which get updated on every change. Depending on the exact requirements you might be able to make accessId simply that attribute and get it updated automatically.

  4. Check if any rows got updated.

    Based on your comment your intention was to basically reimplement what JPA does with version attributes. If you do this you are missing a crucial part: checking that the update actually updated anything, by comparing the return value against 1.

Upvotes: 2

Related Questions