Handling multiple request from client to update a column in table in spring application

Question

I have a table called Balance in that table i have employee and the employee points.

Lets say the employee John is using mobile and web app simultaneously he places order at the same time.

So currently when he tries to place order from both the app the point should be updated accordingly.

When he tries to place order simultaneoulsy each request is getting 5000 point and based on cart value of john the point is updated.

From mobile app he redeeemed 2000 points so the updated amount is 5000-2000 ie 3000 From web app he redeemed 1000 point so the updatded amount should be 3000-1000 ie 2000

But in current scenario the updated amount is 5000-1000 = 4000.(For web app dirty read)

What should i do in order to make sure the transaction is proper.

I have added isolation level to SERIALIZABLE but in this case when second request updates the table column points

I get org.springframework.dao.DeadlockLoserDataAccessException:

What should i do in order to make sure that both the simultaneous request update the table with proper points.

Below is my code

@Transactional(isolation=Isolation.SERIALIZABLE)
public void updatePoints(String employee,int points){
    Object[] arr = new Object[] {points, employeeId};
    getJdbcTemplate().update("UPDATE BALANCE SET POINTS =POINTS -? WHERE EMPLOYEEID=?",arr);
}

PLEASE FIND UPDTAED CODE ABOVE CODE WAS ONLY FOR UNDERSTANDING PURPOSE Used Transactional at service layer not in dao layer

@Override
    @Transactional(isolation=Isolation.SERIALIZABLE)
    public void putFinalCartItems(String employeeCode, List cartId, Map statusMap,int userId) {
        CartItems carPoints = repoDao.getFinalCartItems(cartId,employeeCode);//here i have used select query with join from Balance Table
        try {
        
            
            String orderNo=null;
            String mainOrderNo=OrderIdGenerator.getOrderId();
            List finalCartItems = repoDao.getBifurcatedFinalCart(cartId,employeeCode);//here i have used select query with join from Balance Table
            Integer balance = Integer.parseInt(finalCartItems.get(0).getPoints());
            Integer successCount=0;
            try {
                
                for(CartItems cartEntityId : finalCartItems) {
                    balance = balance-Integer.parseInt(cartEntityId.getTotalNoOfPoint());
        
                     orderNo="TRANS-"+cartEntityId.getId();
                     successCount = repoDao.placeOrder(cartEntityId);
                }
                repoDao.updatePoints(Integer.parseInt(carPoints.getTotalNoOfPoint()),userId,employeeCode);
            
            }catch(Exception e) {
                //e.printStackTrace();
                successCount=0;
            }
            if(successCount>0) {
                    FinalOrder lfo = new FinalOrder();
                    lfo.setBalance(String.valueOf(totbalance));
                    lfo.setMainOrderId(mainOrderNo);
                    lfo.setTotalPointsSpent(carPoints.getTotalNoOfPoint());
                    statusMap.put("success",lfo );
                    
            }
        }
        catch(Exception e ) {
            statusMap.put("error","Please try again after some time" );
        }
    }

Error stack trace that iam getting on second request

org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [UPDATE BALANCE set Points=points-? where EMPLOYEEID=? ]; Transaction (Process ID 94) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 94) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)

I want second request to be successfull simultaneoulsy with first request without dirty read.

user_7875623 · Answer

Isolation.SERIALIZABLE is bad for performance. The exception name is misleading as it is not a deadlock since you are updating the tables in same sequence assuming you don't have some other Isolation.SERIALIZABLE methods.
Deadlock is when two different parallel operations try to acquire lock. Say Txn1 involves updating table a first and table b second. Txn2 involves updating table b first and table a second. Now if both of them run parallel, Txn1 will lock table a and wants lock on table b , but Txn2 has locked, table b first and wants lock on table a first and wait for each other to give up. But that is not the case in your example. So if you still want to proceed with Isolation.SERIALIZABLE, you need to catch the DeadlockLoserDataAccessException and retry with recalculated points at that time.
Your real code differs from initial question. In initial question, you are incrementing the points but in real code you are replacing the points with newly calculated points. So I think if you use the approach in the initial question without Isolation.SERIALIZABLE, it should work.
So the parameters you need to pass to the update points method are pointsToReduct not newTotalPoints.

    Object[] arr = new Object[] { pointsToReduct, employeeId };
    getJdbcTemplate().update("UPDATE BALANCE SET POINTS = POINTS - ? WHERE 
                               EMPLOYEEID = ?",arr);

user_12038714 · Answer

if you are using spring, with REST style requests you don't need to worry about this as Dispatcher Servlet is going to take care of simultaneous requests coming into your code one at a time.

But one solution would be to just put @Transactional and in the method first do select to see the current amount and then update += the amount of points. Because it is inside of a transaction there will be a row lock on database so you don't need to worry somebody else accessing it at the same time. (assuming your database provider provides such locks which most would by default)

Handling multiple request from client to update a column in table in spring application

2 Answers