CODEWITHSUNDEEP
javaspringhibernateormspring-transactions
Kency Kurian
Kency Kurian

Reputation: 302

Spring - Two hibernate update queries are executed instead of one

I am working Spring and Hibernate. I have a requirement where I need to update a particular field by adding a number to it. Since multiple threads could execute it at the same time, while updating I check the field value with the old value. So if nothing was updated that means it was incremented by some other thread and we trigger a retry.

CompanyService

public Company getAndIncrementRequestId(final int companyId, int retry) throws Exception {
    Optional<Company> companyOptional = companyRepository.findById(companyId);
    if (!companyOptional.isPresent()) {
        throw new EntityNotFoundException("Company not found for given id" + companyId);
    }

    Company company = companyOptional.get();
    int oldRequestId = company.getRequestId();
    int requestId;
    if (oldRequestId == Integer.MAX_VALUE) {
        requestId = 1;
    } else {
        requestId = oldRequestId + 1;
    }
    company.setRequestId(requestId); //--------------------------> PROBLEM
    int result = companyRepository.updateRequestId(companyId, requestId, oldRequestId);
    if (result == 0) {
        if (retry < 0) {
            throw new Exception("Unable to get requestId");
        }
        LOG.warn("Retrying since there was some update on requestId by some other thread");
        try {
            TimeUnit.MILLISECONDS.sleep(100);
        } catch (InterruptedException e) {
            LOG.warn("Unexpected InterruptException occurred while trying to get requestId");
        }
        return getAndIncrementRequestId(companyId, retry - 1);
    }
    return company;
}

CompanyRepository

@Transactional
public interface CompanyRepository extends CrudRepository<Company, Integer> {
    Optional<Company> findById(String id);

    @Modifying(clearAutomatically = true)
    @Query("update Company c set c.requestId = :requestId WHERE c.id = :companyId AND c.requestId = :oldRequestId")
    int updateRequestId(@Param("companyId") Integer companyId, @Param("requestId") Integer requestId,@Param("oldRequestId") Integer oldRequestId);
}

But this above code in Service will trigger two hibernate updates one which set the requestId with lastest requestId and the other the actual update. Could observe two queries in the log after setting show-sql as true.

But if the line ,

company.setRequestId(requestId);

Is moved down after the companyRepository.updateRequestId() it works fine.

Working CompanyService

public Company getAndIncrementRequestId(final int companyId, int retry) throws Exception {
    Optional<Company> companyOptional = companyRepository.findById(companyId);
    if (!companyOptional.isPresent()) {
        throw new EntityNotFoundException("Company not found for given id" + companyId);
    }

    Company company = companyOptional.get();
    int oldRequestId = company.getRequestId();
    int requestId;
    if (oldRequestId == Integer.MAX_VALUE) {
        requestId = 1;
    } else {
        requestId = oldRequestId + 1;
    }

    int result = companyRepository.updateRequestId(companyId, requestId, oldRequestId);
    if (result == 0) {
        if (retry < 0) {
            throw new Exception("Unable to get requestId");
        }
        LOG.warn("Retrying since there was some update on requestId by some other thread");
        try {
            TimeUnit.MILLISECONDS.sleep(100);
        } catch (InterruptedException e) {
            LOG.warn("Unexpected InterruptException occurred while trying to get requestId");
        }
        return getAndIncrementRequestId(companyId, retry - 1);
    }
    company.setRequestId(requestId); //--------------------------> PROBLEM DOES NOT EXISTS
    return company;
}

Sp my question why are there two queries when I have not even passed the entity Company anywhere..?

Upvotes: 0

Views: 1323

Answers (1)

codeLover
codeLover

Reputation: 2592

It is because when you do "companyRepository.findById(companyId);" the returned company is returned in managed state. So , when in case 1 you set the request id before invoking "companyRepository.updateRequestId(companyId, requestId, oldRequestId);", a transaction object is created in the company repository which executes all the pending updates of the managed entity plus the query of the method "updateRequestId" also gets fired. While in second case, since you have written set statement after invoking "companyRepository.updateRequestId(companyId, requestId, oldRequestId);", that is why the update on managed object never gets fired because it does not get any transaction

Upvotes: 1

Related Questions