0

How to update row of a table in mysql maintaining consistency of data.

For Example :

    Algo : 


    do{
             CreditBalance credit = getCreditBalance(initialDeductionCreditRequest.getUserId(),
                    creditMetadata.getId());
            if (credit.getBalance() >= cost) {
                final Integer newBalance = credit.getBalance() - cost;
                credit.setNewBalance(newBalance);
                rowsUpdated = compareAndSetBalanceForUser(credit.getBalance(), newBalance,
                        credit.getUserId(), balanceCredit.getCreditId());
            }
}while (rowsUpdated == 0);

-- getCreditBalance contains Query which retrieves user balance based on i/p criteria

public Integer compareAndSetBalanceForUser(Integer oldBalance, Integer newBalance, String userId,
            Integer creditId) {
        Session session = sessionFactoryCoreServices.getCurrentSession();
        Query query = session.createQuery(
                "**update CreditBalance cb set cb.balance = :newBalance , modified_date = NOW() where cb.balance = :oldBalance and cb.userId = :userId and cb.creditId = :creditId**");
        query.setParameter("newBalance", newBalance);
        query.setParameter("oldBalance", oldBalance);
        query.setParameter("userId", userId);
        query.setParameter("creditId", creditId);
        return query.executeUpdate();
    }

I have a use case where i need to update balance of particular user, maintaining credit balance consistent.

i run my code with 20 threads concurrently the above code is not promising data consistency.

i even tried SELECT FOR UPDATE approach which is also not promising data consistency.

is there any other way which can assure data consistency after certain set of concurrent operations.

kavetiraviteja
  • 2,058
  • 1
  • 15
  • 35
  • As Hibernate is not thread safe, so make Session thread safe. private static ThreadLocal threadSafeSession = new ThreadLocal() { protected Session initialValue(){ return sf.openSession(); } }; – Gaurav Srivastav Sep 22 '17 at 17:03
  • @GauravSrivastav think about situation where above code is exposed as API where multiple Instances of server are hosting this API.then how we can ensure data consistency. – kavetiraviteja Sep 22 '17 at 17:06
  • I think we can achieved this by table lock thing. Because in cloud server there are multiple server instances. In this case we can't achieve consistency at application layer. – Gaurav Srivastav Sep 23 '17 at 02:30
  • @GauravSrivastav i think table locking is not a good way.because it reduces overall API performance and impacts other API performances which does a join on that locked table. that is the reason i didn't got for table locking. i choose CAS(compare and swap) method than locking table with minimal retry attempts. – kavetiraviteja Sep 23 '17 at 06:30

0 Answers0