Long running persist ends up with errors

This is my code trying to save data

`     for (int i = 0; i < materialVersions.size(); i += BATCH_SIZE) {
            int endIndex = Math.min(i + BATCH_SIZE, materialVersions.size());
            List<MaterialVersion> materialVersionChunk = materialVersions.subList(i, endIndex);

            for (MaterialVersion materialVersion : materialVersionChunk) {
                materialVersion.setReferencedEntities(referencedEntities);
                materialVersionRepo.persist(materialVersion);
            }`

it's in transacational context

referencedEntities has onetomany with materialVersion and the connection is bidirectional. this save happens in chunks ,this is quarkus java app.

this is my properties

quarkus.hibernate-orm.jdbc.fetch.batch-size=100

quarkus.transaction-manager.default-transaction-timeout=3600

but since materialVersion is a lot ,it's a big list I end up with exception like

{"timestamp":"04:21:27.909","sequence":20079,"loggerClassName":"org.jboss.logging.Logger","loggerName":"io.agroal.pool","level":"WARN","message":"Datasource '<default>': ORA-17008: Closed connection\nhttps://docs.oracle.com/error-help/db/ora-17008/","threadName":"Transaction Reaper Worker 0","threadId":58,"mdc":{},"ndc":"","hostName":"85b64c4fc1dd","processName":"quarkus-run.jar","processId":1} {"timestamp":"04:21:28.019","sequence":20080,"loggerClassName":"org.hibernate.internal.CoreMessageLogger_$logger","loggerName":"org.hibernate.resource.transaction.backend.jta.internal.synchronization.SynchronizationCallbackCoordinatorTrackingImpl","level":"WARN","message":"HHH000451: Transaction afterCompletion called by a background thread; delaying afterCompletion processing until the original thread can handle it. [status=4]","threadName":"Transaction Reaper Worker 0","threadId":58,"mdc":{},"ndc":"","hostName":"85b64c4fc1dd","processName":"quarkus-run.jar","processId":1}

I've tried to add default-transaction-timeout and BATCH_SIZE ,somewhat it helps ,but solution is not consistent since for some other values from controller the list may get even bigger.

Instead of opening 1 long transaction, you can also do batched transactions.

Something like this:

@ConfigProperty(name = "quarkus.hibernate-orm.jdbc.statement-batch-size", defaultValue = "20")
    int batchSize;

    @Inject
    Entitymanager em;

    public void store(@NotEmpty @NotNull List<MaterialVersion> versions) {
            var index=0;
            try {
                for (var i = 0; i < versions.size(); i++) {    
                   em.getTransaction().begin();                
                   var endIndex = Math.min(i + batchSize, versions.size());
                   var materialVersionChunk = versions.subList(i, endIndex);

                   for (var j = 0; j < materialVersionChunk.size(); j++) {
                       if (j > 0 && this.batchSize % j == 0) {
                          em.getTransaction().commit();
                          em.clear();
                          em.getTransaction().begin();
                       }
                       materialVersionChunk.get(j).setReferencedEntities(referencedEntities);
                       materialVersionRepo.persist(materialVersion);
                   }
                   index++;
                   em.getTransaction().commit();
               }
               
            } catch (Exception ex) {
               em.getTransaction().rollback();
               throw new WebApplicationException("Failed persisting at index %d".formatted(index), 400);
            }
  }