org.apache.ibatis.exceptions.PersistenceException:
### Error committing transaction. Cause: org.apache.ibatis.executor.BatchExecutorException:
DEPENDENT.insertDependentToStaging (batch index #1) failed. Cause: java.sql.BatchUpdateException:
ORA-04091: table MO0RCO00_UAT.RCMBENSTAGWF is mutating, trigger/function may not see it
ORA-06512: at "MO0RCO00_UAT.TRIG_DEPENDENTNO", line 4
ORA-04088: error during execution of trigger 'MO0RCO00_UAT.TRIG_DEPENDENTNO'
I have a DAO
public void saveBeneficiariesToStaging(List<DependentBean> dependents){
SqlSession session = getSqlSessionFactory().openSession(ExecutorType.BATCH);
int len = dependents.size();
try {
for(int i = 0; i < len; i++) {
session.update("insertDependentToStaging", dependents.get(i));
}
} catch(Exception e) {
logger.error("saveBeneficiariesToStaging", e);
e.printStackTrace();
session.close();
}finally{
session.close();
}
}
Solution
public void saveBeneficiariesToStaging(List<DependentBean> dependents){
SqlSession session = getSqlSessionFactory().openSession(ExecutorType.BATCH);
int len = dependents.size();
try {
for(int i = 0; i < len; i++) {
session.update("insertDependentToStaging", dependents.get(i));
session.commit();
}
} catch(Exception e) {
logger.error("saveBeneficiariesToStaging", e);
e.printStackTrace();
session.close();
}finally{
session.close();
}
}
Explanation: The trigger is selecting from the latest inserted rows in the batch update. But Trigger is mutating because it selects something that is not yet on the table because I am using batch update. Program should commit each row it inserts so that the trigger can select the recently inserted row.