4

I have a spring batch application that uses Azure SQL server, it runs without any issues and updates the Database for a most part however I am getting the following error occasionally

enter image description here

enter image description here

what does it mean? how to handle this?

Note: this is a long running job executes for more than 2hrs. The above error reported after ~1.45hrs.

I am reading the data from CSV file using FlatfileReader & writing into Azure SQL Server using ItemWriter as mentioned below

public class StoreWriter implements ItemWriter<List<Store>> {

   Logger logger = Logger.getLogger(StoreWriter.class);

   private HibernateItemWriter<Store> hibernateItemWriter;

   public StoreWriter(HibernateItemWriter<Store> hibernateItemWriter) {
      this.hibernateItemWriter = hibernateItemWriter;
   }

   @Override
   public void write(List<? extends List<Store>> items) throws Exception {
      for (List<Store> Store : items) {
        hibernateItemWriter.write(Store);
      }
      logger.info(String.format("Store Processing Completed %s", new LocalDateTime()));
   }
}

Below is my Hibernate configuration

<bean id="transactionManager" class="org.springframework.orm.hibernate5.HibernateTransactionManager" lazy-init="true">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

<tx:annotation-driven transaction-manager="transactionManager"/>
<!-- HikariCP Database bean -->
<bean id="demoDataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>

<!-- HikariConfig config that is fed to above dataSource -->
<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
    <property name="maximumPoolSize" value="50" />
    <property name="idleTimeout" value="30000" />
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
    <property name="url" value="jdbc:sqlserver://demo.database.windows.net:1433;database=sqldb;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;" />
    <property name="username" value="user1" />
    <property name="password" value="p@ssword1" /
</bean>
<bean class="org.springframework.batch.core.scope.StepScope" />
One Developer
  • 99
  • 5
  • 43
  • 103
  • 1
    The connection to the server seems to be closed after some time. You probably need to increase the idleTimeout parameter in your server's configuration. – Mahmoud Ben Hassine Sep 24 '21 at 13:36
  • Is there any way to introduce retry logic in the the Itemwriter class? – One Developer Oct 06 '21 at 03:03
  • let try increasing connection timeout to 60000, idle timeout to 600000 and max life time to 1800000 and retry... This will keep your connection longer ... – Huy Nguyen Oct 19 '21 at 14:59

1 Answers1

1

Please try using the annotation "@Transactional (propagation = Propagation.REQUIRES_NEW)" on the method where the records were committed.

borchvm
  • 3,533
  • 16
  • 44
  • 45
  • Thank you, @borchvm, what does it will do? how this will fix the issue? – One Developer Oct 11 '21 at 12:29
  • Hello, I saw it late, I was telling you to check that it is not due to some transaction that is not closed. This creates a new transaction even if one exists. – borchvm Oct 13 '21 at 06:14