0

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.....
Superman
  • 221
  • 2
  • 11
  • 1. print out isBalanceAvailable.getBalanceAmount() to log, 2. Enable show sql if you are using JPA ... It will help you know the process more clearly. – Huy Nguyen Oct 07 '21 at 13:52
  • isBalanceAvailable.getBalanceAmount() is giving 1000rs in each iteration, thats the issue! – Superman Oct 07 '21 at 14:00
  • So base on your answer, i guess the update is not commit to database. Many ORM relations DB don't immediately commit the change... You can try applying commit in your update BalanceAmount and check the result... – Huy Nguyen Oct 07 '21 at 14:07
  • Yeah i believe so, But basically my update method is like: `walletRepository.setUpdatedNalanceAmount(..., updatedAmount);` So where do I need to put "commit" line and how? – Superman Oct 07 '21 at 14:15
  • You can add your full code setUpdatedNalanceAmount in description, others can easily review it – Huy Nguyen Oct 07 '21 at 14:20
  • I have added the repo call here. I tried moving @Transactional from one method to another but still no luck! – Superman Oct 07 '21 at 14:44
  • Let using @Transactional(propagation=Propagation.REQUIRES_NEW) in method callToUpdateBalanceAmount and see the result – Huy Nguyen Oct 07 '21 at 14:52
  • I did exactly the same but still it skip the first iteration update anyway! What else we can do it? Should I remove @Transactional from everywhere? – Superman Oct 07 '21 at 15:04
  • Because i can't see the full source code here, it's hard to investigate, you should enable show sql query in jpa to trace the problem and you should write few unit test to make sure each function do the right thing... – Huy Nguyen Oct 07 '21 at 15:16
  • I have edited the parent method also. Now you can see. Also, I will add the SQL queries from console in few mins. But believe me, code is not breaking anywhere but somehow it doesn't reflect each iteration value. – Superman Oct 07 '21 at 15:20

2 Answers2

0

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

Shubham Sharma
  • 21
  • 2
  • That's the issue. Deduction for each request must be updated and it should happen in the loop, not outside loop. If you find any solution to this, please do reply & thanks in advance. – Superman Oct 07 '21 at 15:37
0

Can you try with this at method 2

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

This will work. but this will have bit performance issue as its running in different transactional. But to fix performance you need to improve your design. Also check your hibernate config if auto_commit enabled. else you have commit manually after each transaction.

Md Ashif Qureshi
  • 1
  • 2