I have a springboot application that needs to iterate over a large number of records and call a stored procedure which inserts some data in a table for each record read.
we cannot use BatchUpdate because it is taking a long time to process thousands of records , I was asked to commit frequently (either after every record or after x records)
I was looking online and I did not see a good example on how to commit manually in springboot while calling a stored procedure in a loop. I am using SimpleJdbcCall and my code looks like this:
@Transactional(isolation = Isolation.READ_UNCOMMITTED,propagation = Propagation.NOT_SUPPORTED)
public class EventsProcessor
{
@Autowired
@Qualifier("dbDatasource")
DataSource dataSource;
public void process(List<Event> events) throws Exception
{
SimpleJdbcCall dbTemplate = new SimpleJdbcCall(dataSource).withProcedureName("UPDATE_EVENTS").withSchemaName("TEST");
DataSourceUtils.getConnection(dataSource).setAutoCommit(false);
for (Event ev : events)
{
//fill inParams here
outParams = dbTemplate.execute(inParams);
DataSourceUtils.getConnection(dataSource).commit();
}
}
}
I tried without Propagation.NOT_SUPPORTED and with it but same result. The code is executing the call to the sp and there is not error when it executes commit() , but after commit() if I query the table that the sp inserted the record in it, I don't see the records in the table.
If I remove the setAutocommit(false) and the commit statement and the Propagation.NOT_SUPPORTED , and just let springboot handle transactions, then while it's processing, I can see the records in the table if I do READ UNCOMMITTED, but they will not be committed till the full job ends. What am I doing wrong that is preventing the commit to happen after each row?
