Managing Long-Running Jobs in Spring Batch: Troubleshooting Connection Issues

Dealing with long-running jobs in Spring Batch can be tricky, especially when it comes to managing database connections and transactions. In our latest post on Developer’s Coffee, we dive into a real-world issue we faced with a Spring Batch job that timed out due to connection limits. Discover how we tackled the problem by tweaking HikariCP settings and adjusting load balancer timeouts to ensure smooth and reliable batch processing. Read on to learn more about our solutions and how you can apply them to your projects.

Yugbayte

Introduction

Spring Batch is a powerful framework for processing large volumes of data. However, it can pose challenges when dealing with long-running jobs, especially regarding database connections and transaction management. In this post, we’ll explore a real-world issue encountered during a long-running batch job and how we resolved it.

The Problem

We had a Spring Batch job that included several steps, one of which took a considerable amount of time to execute. The job utilized HikariCP for connection pooling, with a max-lifetime setting of 30 minutes. The database in use was YugabyteDB.

Here’s a snippet of our job configuration:

return new StepBuilder("step1", jobRepository)
        .<UUID, UUID>chunk(employeeTmpProcessorPageSize, transactionManager)
        .reader(employeeTmpReader)
        .writer(employeeTmpWriter)
        .faultTolerant()
        .skipLimit(step1SkipLimit)
        .skip(RuntimeException.class)
        .allowStartIfComplete(true)
        .listener(new SPItemProcessorListener())
        .taskExecutor(taskExecutor)
        .build();

During execution, if step1 took longer than 30 minutes, we encountered the following exception:

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [UPDATE ...]; An I/O error occurred while sending to the backend.; nested exception is com.yugabyte.util.PSQLException: An I/O error occurred while sending to the backend.
...
Caused by: java.sql.SQLException: Connection is closed
    at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:502)

Root Cause Analysis (RCA)

1. Connection Timeout: The HikariCP connection pool closed connections that were idle for more than 30 minutes. This caused issues for long-running steps that exceeded this duration.

2. Database Load Balancer Timeout: The load balancer’s idle timeout was set to a lower value (e.g., 30 minutes), leading to connection drops for long-running transactions.

3. Transactional Context: Spring Batch’s transactional context maintained connections to update job metadata. When the connection closed, it led to failures in updating this metadata.

Solution

To address the issue, we implemented the following changes:

1. Increase HikariCP max-lifetime: This setting was increased to a higher value to ensure connections remain open for the duration of long-running steps. However, this was a temporary measure.

spring.datasource.hikari.max-lifetime=5400000 # 90 minutes

2. Adjust Load Balancer Timeout: We increased the idle timeout setting on the Azure load balancer to 60 minutes to align with our expected job execution times.

3. Periodic Connection Validation: We added a connection validation query to periodically check and refresh the connection during long-running steps.

spring.datasource.hikari.validation-timeout=30000 # 30 seconds
spring.datasource.hikari.idle-timeout=60000 # 60 seconds

Result

After implementing these changes, the long-running batch job executed successfully without connection timeout issues. The job repository was able to maintain and update the job execution context, ensuring data consistency and successful job completion.

Conclusion

Handling long-running jobs in Spring Batch requires careful consideration of database connection settings and transaction management. By increasing connection lifetimes and aligning load balancer settings with job requirements, we can ensure robust and reliable batch processing.

Stay tuned for more insights and solutions to common development challenges at Developer’s Coffee!

Feel free to share your experiences and solutions to similar issues in the comments below. Happy coding!

Reference:

https://medium.com/@office.yeon/spring-batch-connection-timeout-correlation-between-mysql-and-hikaricp-d27e4112c9c3

https://stackoverflow.com/questions/77243253/spring-batch-connection-closed-when-long-time-step-execution

Related Posts

16 Comments

  1. Its like you learn my thoughts! You seem to grasp a lot approximately this, such as you wrote the guide in it or something. I believe that you simply could do with a few percent to power the message home a little bit, however other than that, that is excellent blog. An excellent read. I’ll certainly be back.

  2. I like what you guys are up also. Such smart work and reporting! Carry on the superb works guys I have incorporated you guys to my blogroll. I think it will improve the value of my site :).

  4. Hi,

    I just visited developerscoffee.com and wondered if you’d ever thought about having an engaging video to explain what you do?

    Our videos cost just $195 for a 30 second video ($239 for 60 seconds) and include a full script, voice-over and video.

    I can show you some previous videos we’ve done if you want me to send some over. Let me know if you’re interested in seeing samples of our previous work.

    Regards,
    Joanna

    Unsubscribe: https://removeme.live/unsubscribe.php?d=developerscoffee.com

  5. Hi there,

    We run a YouTube growth service, which increases your number of subscribers both safely and practically.

    – We guarantee to gain you 700-1500+ subscribers per month.
    – People subscribe because they are interested in your channel/videos, increasing likes, comments and interaction.
    – All actions are made manually by our team. We do not use any ‘bots’.
    – Channel Creation: If you haven’t started your YouTube journey yet, we can create a professional channel for you as part of your initial order.

    The price is just $60 (USD) per month, and we can start immediately.

    If you have any questions, let me know, and we can discuss further.

    Kind Regards,
    Katelyn

  6. Hi there,

    We run a TikTok growth service, where we can increase your follower count safely and practically.

    – Guaranteed: We guarantee to gain you 700-1500 new followers each month.
    – Real, human followers who follow because they are interested in your profile/videos.
    – Safe: All actions are done without using any automated tasks / bots.

    Our price is just $60 (USD) per month and we can start immediately.

    For further information, visit our website here, read the FAQs, and complete the form:
    https://furtherinfo.info/tiktok

    However, if you are not interested, use this link and we won’t contact you again:
    https://removeme.live/tt/unsubscribe.php?d=developerscoffee.com

    Kind Regards,
    Gemma

  7. Hi there,

    We run a YouTube growth service, which increases your number of subscribers both safely and practically.

    – We guarantee to gain you 700-1500+ subscribers per month.
    – People subscribe because they are interested in your channel/videos, increasing likes, comments and interaction.
    – All actions are made manually by our team. We do not use any ‘bots’.

    The price is just $60 (USD) per month, and we can start immediately.

    If you have any questions, let me know, and we can discuss further.

    Kind Regards,
    Katelyn

  8. Hi there,

    I’m reaching out because we’ve just released a complete WordPress video training package with unrestricted Private Label Rights. The package includes 19 professional video lessons across 4 courses, covering everything from WordPress basics to local development.

    Key features:

    Full PLR rights (rebrand, resell, repurpose as you wish)
    4 complete courses with 19 video lessons
    Professional production quality
    Ready-to-launch sales materials included

    This is ideal if you’re looking to quickly launch your own WordPress training business or add to your existing product line.

    For detailed information and pricing, visit: https://furtherinfo.info/wpmk

    Kind regards,
    Gino

  10. Hi,

    I just visited developerscoffee.com and wondered if you’d ever thought about having an engaging video to explain what you do?

    Our videos cost just $195 for a 30 second video ($239 for 60 seconds) and include a full script, voice-over and video.

    I can show you some previous videos we’ve done if you want me to send some over. Let me know if you’re interested in seeing samples of our previous work.

    Regards,
    Joanna

  11. I was curious if you ever thought of changing the structure of your blog? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having 1 or 2 pictures. Maybe you could space it out better?

  13. Hi there,

    We wanted to introduce you to a revolutionary system that helps you create AI-powered tools to generate steady, qualified leads without paid advertising.

    Key benefits:

    Create AI tools in minutes with simple copy/paste templates
    Drive free, targeted traffic to any niche or offer
    Built-in call-to-action system to funnel leads to your sales pages
    No coding or technical experience needed

    We’re currently offering a special launch price of $17 (regular $97) which includes bonus training on traffic generation and AI monetization.

    For more details, check out: https://furtherinfo.info/etb

    Thanks,
    Gabriele

  14. Hi there,

    We run a Youtube growth service, where we can increase your subscriber count safely and practically.

    – Guaranteed: We guarantee to gain you 700-1500 new subscribers each month.
    – Real, human subscribers who subscribe because they are interested in your channel/videos.
    – Safe: All actions are done, without using any automated tasks / bots.

    Our price is just $60 (USD) per month and we can start immediately.

    If you are interested then we can discuss further.

    Kind Regards,
    Amelia

  15. Hi there,

    I’m reaching out because we’ve just released a complete WordPress video training package with unrestricted Private Label Rights. The package includes 19 professional video lessons across 4 courses, covering everything from WordPress basics to local development.

    Key features:

    Full PLR rights (rebrand, resell, repurpose as you wish)
    4 complete courses with 19 video lessons
    Professional production quality
    Ready-to-launch sales materials included

    This is ideal if you’re looking to quickly launch your own WordPress training business or add to your existing product line.

    For detailed information and pricing, visit: https://furtherinfo.info/wpmk

    Kind regards,
    Lettie

  16. Hi,

    I saw developerscoffee.com and thought you might be interested in a tool that could complement your digital product strategy.

    Micro Product Magic GPT helps you create complete mini digital offers in minutes – perfect for lead magnets, tripwires, or bonuses without the usual time investment.

    This tool:

    – Generates engaging titles and structured reports your audience wants.
    – Creates sales copy designed to convert.
    – Builds a complete offer with pricing and pitch.
    – Works with the free version of ChatGPT

    What makes this unique is that it comes with full Resale Rights – meaning you can use it for your own products AND sell access to others as an additional revenue stream.

    Learn more: https://furtherinfo.info/refer/magicgpt

    Tammara

Leave a Reply

Your email address will not be published. Required fields are marked *