org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) [com#117591]

Question

def saveProcessDetail = {

    //def employeeLoanInstance = EmployeeLoan?.findById(params?.long("id"),[lock: true])
    //lock(params?.long("id"))
    def employeeLoanInstance = EmployeeLoan?.get(params?.long("id"))
    if(employeeLoanInstance){
        def instance
        if(params?.modeOfPayment){
            if(params.modeOfPayment == 'DD'){
                params.ddDate = params.ddDate ? DateUtility?.parseDate(params.ddDate) : params.ddDate
                instance = new Dd()
            }
            if(params.modeOfPayment == 'Cheque'){
                params.chequeDate = params.chequeDate ? DateUtility?.parseDate(params.chequeDate) : params.chequeDate
                instance = new Cheque()
            }
            if(params.modeOfPayment == 'Cash'){
                params.paidDate = params.paidDate ? DateUtility?.parseDate(params.paidDate) : params.paidDate
                instance = new Cash()
            }
            instance?.properties = params
        }

        params.deductionDate = params.deductionDate ? DateUtility?.parseLoanDate(params.deductionDate) : params.deductionDate
        params.sanctionedDate = params.sanctionedDate ? DateUtility?.parseDate(params.sanctionedDate) : params.sanctionedDate

        //println " test : "+DateUtility.getFormattedMonthAndYear(params.deductionDate)
        //println " test zx : "+DateUtility.getFormattedMonthAndYear(params.sanctionedDate)
        def formattedDeductionDate = DateUtility.getFormattedMonthAndYear(params.deductionDate)
        def formattedSanctionedDate = DateUtility.getFormattedMonthAndYear(params.sanctionedDate)


        //println " less : "+(formattedDeductionDate < formattedSanctionedDate)

        //println " = : "+(formattedDeductionDate == formattedSanctionedDate)


        //println " greater : "+(formattedDeductionDate > formattedSanctionedDate)

    //  formattedDeductionDate.before(formattedSanctionedDate) && !(formattedDeductionDate.equals(formattedSanctionedDate))


        employeeLoanInstance?.properties = params
        instance?.validate()
        if(params.modeOfPayment == 'Cash' && employeeLoanInstance?.sanctionedAmount && instance?.cashAmount && (instance?.cashAmount != employeeLoanInstance?.sanctionedAmount))
            instance.errors.rejectValue ("cashAmount", "cash.cashAmount.invalid.message", [
                message(code : 'cash.cashAmount.label', default : 'Amount')]
            as Object[], message(code : 'cash.cashAmount.invalid.message'))
        if(!instance?.hasErrors())
            employeeLoanInstance.loanId = employeeLoanInstance.loanId ? employeeLoanInstance.loanId : loanService?.getLoanId(employeeLoanInstance?.employee,employeeLoanInstance?.createdBranch)
        employeeLoanInstance?.validate()
        /* Emi Amount Validation */
        if(employeeLoanInstance?.noOfInstallments && employeeLoanInstance?.emiAmount && employeeLoanInstance?.sanctionedAmount){
            def amt = (employeeLoanInstance?.noOfInstallments - 1) * (employeeLoanInstance?.emiAmount)
            def sanctionedAmt =  (employeeLoanInstance?.sanctionedAmount - amt)
            if(amt > employeeLoanInstance?.sanctionedAmount)
                employeeLoanInstance.errors.rejectValue ("emiAmount", "employeeLoan.emiAmount.invalid.message", [
                    message(code : 'employeeLoan.emiAmount.label', default : 'Emi Amount')]
                as Object[], message(code : 'employeeLoan.emiAmount.invalid.message'))
            if(sanctionedAmt > employeeLoanInstance?.emiAmount)
                employeeLoanInstance.errors.rejectValue ("emiAmount", "employeeLoan.emiAmount.invalid.message", [
                    message(code : 'employeeLoan.emiAmount.label', default : 'Emi Amount')]
                as Object[], message(code : 'employeeLoan.emiAmount.invalid.message'))
        }
        /* End */
        if(!(employeeLoanInstance?.hasErrors()) && !(instance?.hasErrors())){
            instance?.save(flush : true)
            employeeLoanInstance.paymentId = instance?.id
            employeeLoanInstance.outstandingAmount = employeeLoanInstance?.sanctionedAmount
            employeeLoanInstance?.save(flush : true)
            println"employeeLoanInstance?.noOfInstallments"+employeeLoanInstance?.noOfInstallments
            println "-------loan instance----------->"+employeeLoanInstance?.errors
            /* For saving EmiAmount Details */
            def emiMonth = params.deductionDate
            def amt = 0
            if(employeeLoanInstance?.noOfInstallments){
            for(int i = 0; i < Integer?.valueOf(employeeLoanInstance?.noOfInstallments); i++){
                amt = employeeLoanInstance?.emiAmount
                if(i > 0){
                    Calendar calendar = GregorianCalendar.getInstance()
                    Integer year = emiMonth?.year+1900
                    Integer month = (emiMonth?.month) + 1
                    Integer date = emiMonth.getAt(Calendar.DAY_OF_MONTH)
                    //calendar.set (year, month, date)
                    calendar.set(year, month, date, 0, 0, 0)
                    emiMonth = calendar?.getTime()
                }
                if(i == (employeeLoanInstance?.noOfInstallments - 1)){
                    amt = employeeLoanInstance?.sanctionedAmount - (employeeLoanInstance?.emiAmount * (employeeLoanInstance?.noOfInstallments - 1))
                }
                def emiAmountInstance = new EMIAmountDetails()
                emiAmountInstance?.emiMonth  = emiMonth
                emiAmountInstance?.emiAmount = amt
                emiAmountInstance?.loanId = employeeLoanInstance.loanId
                auditService.beforeSave(emiAmountInstance)
                emiAmountInstance?.save(flush : true)
            }
            }
            /* End */

            flash.message = "${message(code : 'loanApproval.created.message', args : [employeeLoanInstance?.loanType,loanService?.getName(employeeLoanInstance.employee.empPersonalDetails),message(code : 'employeeLoan.loanStatus.'+employeeLoanInstance?.loanStatus)])}"
            redirect(action : "list")
        }
        else{
            render(template : "processLoan", model : [screenName : params?.screenName,instance:instance,employeeLoanInstance : employeeLoanInstance, methodName : params?.methodName])
        }
    }
    else{
        flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'employeeLoan.label', default: 'EmployeeLoan'), params.id])}"
        redirect(action : "list")
    }
}

Note:I can't reproduce this issue locally by various scenario.but when i look into the Production server log file above issue repeatedly happened.shall i make it that block of code as synchronized one or else to service layer for transaction management.line instance?.save(flush : true) cause the problem exactly..Please give your idea to go forward

Answer 1

You should definitely move your database access into a transactional service. That won't necessarily prevent this problem though. Also, synchronization is definitely NOT the way to go here

This is an optimistic locking failure. (See https://grails.github.io/grails-doc/latest/guide/GORM.html#locking )

With the information you've given, I can't provide a definitive answer, but based on what I have seen before, I suspect that you have a double click-problem: The user is triggering the action a second time before the first action completes. The first action completes and updates the version of the affected object. When the second action writes to the database, it fails because the object's version has been updated since the object was loaded.

To help verify if that is the problem, you should add logging to your production system so that you can see when this method starts and what the id is. If you see two (or more) log entries for the same object with almost identical timestamps, then that is your problem.

To solve it, either modify your web page to block double clicks or modify your code to accept the StaleObjectStateException.