I got a very annoying scenario and Spring Boot or JPA is not helping at all:

I want to give my to kids some money from account A. I have a table where I keep the track of my balance_amount. Whenever I giveaway the money, I update it accordingly by subtracting the money I give to my kids.

Now, I have give my two kids some amount, let say 100rs and 200rs respectively in the form of requests (2 requests). So, to update my balance_amount, these are the steps I follow:

1. I stared a for loop.
2. Fetch my row from table to know my available balance_amount (single row), let say I have 1000rs left.
3. When loop iterates for first time, I pick the requestAmount (100rs) and subtract it from balance_amount (1000rs).
4. Update my row in table with updated_balance_amount (1000-100=900rs).
5. Loop iterates for second time.
6. Second iteration started, I fetch my row from table to know my available balance_amount.
7. I again pick the requestAmount (200rs this time) and subtract it from balance_amount.
8. Again updated my row in table with updated_balance_amount. Loop ends here (as I have only 2 requests).
9. I am expecting that at the end of loop, my remaining balance_amount would be 1000-100-200 = 700rs. But it's not! It's showing 800rs.

How the hell happened? Upon debugging, I found that when loop iterated for second time, at step 6, instead of showing 900rs as my balance_amount (after first iteration ended), it is picking the balance_amount as 1000rs! Why? Why it has not updated my row in the table?

During debugging after step 4, I checked my table's row and it was perfectly showing 900rs. But as soon as second iteration started, It discarded the updated value and picked the original 1000rs like it didn't commit the update query of first iteration.

So, please tell me why it's like that and how can I achieve my purpose. Thanks in advance.

Sample Code is:

Method 1:

@Override
public String doSomething(List<RequesDTO> requestDTO, ...)
{
    ...
    ...
    String callToDeductBalanceAmount(List<RequesDTO> requestDTO);
    ...
    ...
 return message;
}

Method 2

@Transactional
private String callToDeductBalanceAmount(List<RequesDTO> requestDTO) {

int remainingBalance = 0;
String message = null;

for (RequestDTO lRequest : requestDTO)  {
double requestAmount = lRequest.getRequestAmount;
Wallet isBalanceAvailable = walletRepository.checkForAvailableBalance(...);
    if(null != isBalanceAvailable) {
        double updatedAmount = isBalanceAvailable.getBalanceAmount() - requestAmount;
        remainingBalance = callToUpdateBalanceAmount(..., updatedAmount);
            if (remainingBalance == 0) {
                message = "Failed";
                }
            }
        }
    return message;
}

Method 3

@Transactional
private Integer callToUpdateBalanceAmount(..., updatedAmount) {
    return walletRepository.setUpdatedNalanceAmount(..., updatedAmount);
}


**Repository**    
@Modifying
@Transactional
@Query("UPDATE...")
Integer setUpdatedNalanceAmount(..., @Param("updatedAmount") double updatedAmount);

CONSOLE LOGS:

Hibernate: select ... from wallet.....
Hibernate: update wallet.....
Hibernate: select ... from wallet.....
Hibernate: update wallet.....

CodePudding user response：

checkForAvailableBalance fetches the data from repository on every call. But when you make changes(by deducting amount) that change will only be flushed to db once you've exited the @Transactional annotated method. You need to pull the business logic out of this Repository to a service class. Spring uses proxy pattern to understand how it works behind the scene have a look at this answer

CodePudding user response：

It doesn't work because of the way Spring implements @Transaction as the other poster said. If you inject a spring bean that uses @Transaction on methods, it will be a proxy which will start and commit transactions around those methods. So this cannot work if you call a method inside the same class because it's not a proxy anymore.

You have to move callToDeductBalanceAmount to another spring bean, lets call it BalanceDeducter. Then you can inject BalanceDeducter inside your present bean and call callToUpdateBalanceAmount. Basically @Transactional on private methods will never work because they are always called from the same class. It will only work methods that are called on injected beans.

Another solution is to use TransactionTemplate. With this you can programmatically start and commit transactions.