Entity fetched with pessimistic locking has outdated value

im testing some kind of small banking-ish service with mysql database.

im using 2nd level cache hibernate (possible source of problem?).

the data structure basically consists of 2 tables, a table for list of transactions (deposits) and a table for storing the latest balance for each account which should be able to be updated concurrently.

and while im testing the concurrency, i see some mistakes on my program.
here's my code, its not perfect but its actually running.

@Service
@Transactional
public class depositService {

    //method below is being executed concurrently
    public Deposit save(Long userId, BigDecimal amount){ //add deposit transaction into transaction list and update related user balance

        DepositTransaction depositTransaction = new DepositTransaction();
        depositTransaction.setUserId(userId);
        depositTransaction.setAmount(amount);
        DepositTransaction depositTransactionLatest = depositTransactionRepository.findTopWithLockByUser_IdOrderByIdDesc(userId).orElse(new DepositTransaction());
depositTransaction.setBalance(depositTransactionLatest.getBalance().add(depositTransaction.getAmount())); // balance increment works well 
        depositTransaction = depositTransactionRepository.save(depositTransaction); //add into transaction table works as expected without any inconsistencies / lost update
            
        AccountBalance accountBalance = accountBalanceRepository.findOneWithLockById(depositTransaction.getUserId()).orElse(null); //update user balance
        accountBalance.setBalance(accountBalance.getBalance().add(depositTransaction.getAmount())); //most of the time, accountBalance.getBalance still having outdated value
        accountBalanceRepository.save(companyBank);
    }
}

@Repository
public interface DepositTransactionRepository{
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<DepositTransaction> findTopWithLockByUser_IdOrderByIdDesc(Long userId);
}

@Repository
public interface AccountBalanceRepository{
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<AccountBalanceTransaction> findOneWithLockById(Long userId);
}

based on the above code, i have a transaction and inside it i use 2 pessimistic locking fetch for 2 entities.
the queries generated have "for update" included correctly and its locked properly since i tried to acquire the lock and failed using mysql command line.

my problem is the accountbalance will have inconsistencies if there are concurrent request, but i dont quite understand why deposit transaction's lock can do it.

the accountBalance.getBalance() will always have outdated value and will need to wait for a few iterations to change to the updated value.

i can make this work if i use serialize transaction isolation with retry mechanism in case of deadlock. but i prefer pessimistic if its possible.

I had a case where fetching data with a pessimistic lock would correctly block if there happened to be a concurrent transaction, but would inexplicably return stale data to me. The concurrent transaction would update the data and I would expect my return value to reflect this update. The cause was because I was also fetching unrelated data prior to my lock, and this caused hibernate to seed it's first-level cache with stale data. Something like this:

// Zoo has a collection of animals which will seed the first-level cache.
Zoo zoo = zooRepository.getZoo();

// ... do unrelated work with zoo

Animal panda = animalRepository.getAnimalByIdWithExclusiveLock(animalId);
/* 
 * Even though the above command might block, making you THINK that 
 * hibernate is going to return the latest version of this specific 
 * animal, in reality, if it's already in the first-level cache, from
 * when you fetched the zoo, you will get the cached copy which is 
 * now stale.
 */

I figured this out by having Spring print the SQL and carefully examining it. Prior to my lock I realized that while fetching the Zoo, it would've simultaneously fetched my target Animal in a stale state.

In my case, the answer was to fetch my locked entities at the very beginning of the transaction so there wasn't an opportunity for the first-level cache to have gotten dirty by that time, like this:

// Fetch important entity first so we know it's fresh from the database!
Animal panda = animalRepository.getAnimalByIdWithExclusiveLock(animalId);

// Now do unrelated work 
Zoo zoo = animal.getZoo();

An alternative that I didn't try might be to flush the cache immediately prior to fetching the locked entity:

Zoo zoo = animal.getZoo(); // Cache is now dirty/stale!

entityManager.clear(); // Clear the cache

Animal panda = animalRepository.getAnimalByIdWithExclusiveLock(animalId);

Hopefully that helps someone in the future.