Transaction Management By @@Transactional Annotation

Question

I have one requirement in which i have to insert record in one table and update the record in another table.

There are 100 records for which i have to do above insertion and updation. But If any record fails due to any error in insertion or updation only that record should be rollback not others.Means if There are 100 records and 60 gets success and 61 gets fails due to any error then only 61 records should be rolled back not all other 60 records.

I am using Spring boot JPA and @Transactional Annotation.But if put this annotation on starting of my method,Then in case of any error it roll back all records.

I am attaching my code below.Please suggest how i can achieve this. In below code i have persistRecord() method which have all my code for insertion and updation.I have not marked this with @Transactional annotation insteed below method is annotated with @Transactional annotation

        logger.debug("insertSalesTargetData method called of service class");
        String userid = (String) webSession.getAttribute("username");
        logger.debug("Userid :" + userid);
        HashMap<String, String> persistRecordStatus = new HashMap<String, String>();
        Query qCountCheck = em.createNativeQuery(QueryConstant.CHECK_SALES_TARGET_DATA_QUERY);
        List<Object> existingRecordList = null;
        persistRecordStatus.put("TOTAL_RECORD", String.valueOf(xlsRowslist.size()));
        int failedRecordCount = 0;
        int successRecordCount = 0;
        for (int i = 0; i < xlsRowslist.size(); i++) {
            ArrayList rowList = xlsRowslist.get(i);
            // try {
            double weekNoDouble = Double.parseDouble((String) rowList.get(0));// Week No
            String fromDate = (String) rowList.get(1);// fromDate
            String toDate = (String) rowList.get(2);// toDate
            double catCodeDouble = Double.parseDouble((String) rowList.get(3));// catCode
            int catCode = (int) catCodeDouble;
            int weekNo = (int) weekNoDouble;
            String target = (String) rowList.get(4);// target

            String salesGoalId = fromDate + toDate + catCode;
            salesGoalId = salesGoalId.replace("-", "");

            // Check if the sales goal id already exist in the database or not
            qCountCheck.setParameter(1, salesGoalId);// SALES_GOAL_ID
            existingRecordList = qCountCheck.getResultList();
            logger.debug("Count List Size " + existingRecordList.size());
            if (existingRecordList != null && existingRecordList.size() > 0) {
                if (existingRecordList.get(0) != null) {
                    BigDecimal row = (BigDecimal) existingRecordList.get(0);
                    // try {
                    logger.debug("Persisting record no  " + i);
                    persistRecord(row, salesGoalId, target, fromDate, toDate, userid, catCode, weekNo);
                    logger.debug("Record no  " + i + " persisted");
                    persistRecordStatus.put("SUCCESS_RECORD", String.valueOf(successRecordCount++));
                    /*
                     * } catch (Exception e) { persistRecordStatus.put("FAILED_RECORD",
                     * String.valueOf(failedRecordCount++)); }
                     */

                }
            } else {
                persistRecordStatus.put("FAILED_RECORD",String.valueOf(failedRecordCount++));
            }
            /*
             * } catch (Exception e) { logger.debug("Exception in processing record no " + i
             * + " " + e.toString()); persistRecordStatus.put("FAILED_RECORD",
             * String.valueOf(failedRecordCount++)); }
             */

        }
        return persistRecordStatus;
    }

Answer 1

You are doing some kind of batch updates ?

In batch processing, you treat "failures" by putting all stuff involved (e.g. the input record, an error code, ...) on an error store for subsequent review by some user. And you commit that along with all the "successful" updates.

Alternatively, put each insert/update pair in its own transaction. (and still alternatively, if your DBMS / transactional framework supports savepoints, take a savepoint after each insert/update pair and upon failure, rollback to the last savepoint and then commit (and figure out a way to not lose the remaining 40 unprocessed entries from your input). In either case, don't come complaining if you get performance trouble.

There is no having things both ways in transactional batch processing.