StoredProcedureItemReader not able to Retry on deadlock exceptions

Question

I am having difficulty in getting my StoredProcedureItemReader to Retry once it fails due to deadock exception on the Database.
Here is the configuration for my step process:

@Bean    
public Step Step() throws Exception {       
            return stepBuilderFactory.get("Step")
                    .<Student, Student>chunk(100)
                    .reader(storedProcItemReader())
                    .processor(studentItemProcessor)
                    .writer(fileItemWriter())
                    .faultTolerant()
                    .retryLimit(5)
                    .retry(myException.class)
                    .backOffPolicy(backoffPolicy())
                    .build();
    }

    @Bean
    @StepScope
    public StoredProcedureItemReader<Student> storedProcItemReader() throws Exception {
        return studentItemReader.getDataFromDatabase(dataSource);
    }

studentItemReader class file:

@Component
@StepScope
public class studentItemReader{

    @Retryable(include = {DataAccessException.class, JDBCException.class, TransactionException.class, DeadlockLoserDataAccessException.class, Exception.class }, maxAttempts = 5, backoff = @Backoff(delay = 1000,
            maxDelay = 15000, multiplier = 2))
    public StoredProcedureItemReader<Student> getDataFromDatabase(DataSource dataSource) {
        
            StoredProcedureItemReader<RegionResponse> reader = new StoredProcedureItemReader<>();
            
            SqlParameter[] parameter = { new SqlParameter("@studentId",
                    java.sql.Types.INTEGER) };
            PreparedStatementSetter statementValues = new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setInt(1, parameterValue);
                    }
            };
            reader.setDataSource(dataSource);
            reader.setProcedureName("dbo.StudentReport");
            reader.setParameters(parameter);
            reader.setPreparedStatementSetter(statementValues);
            reader.setRowMapper(new StudentRowMapper());
            return reader;
        }   
    }
}

So, the problem is I am not able to get the Retry piece working on my StoredProcedureItemReader after adding Retry on it. Please let me what mistake I am doing here. Thanks in advance!

Answer 1

You are adding @Retryable on a method that returns an item reader. This method is only called at configuration time. If you want to retry a read operation at runtime whenever an exception is thrown, you should add the annotation on the read method of your reader. The annotation based approach of adding retry capabilities can be tricky, that's why I recommend the programmatic way to see where the actual retry is happening. Here is a quick example:

import org.springframework.batch.item.ExecutionContext;
import org.springframework.batch.item.ItemStreamException;
import org.springframework.batch.item.ItemStreamReader;
import org.springframework.batch.item.database.StoredProcedureItemReader;
import org.springframework.retry.support.RetryTemplate;

public class RetryableItemReader<T> implements ItemStreamReader<T> {

    private final StoredProcedureItemReader<T> delegate;
    private final RetryTemplate retryTemplate;

    public RetryableItemReader(StoredProcedureItemReader<T> delegate, RetryTemplate retryTemplate) {
        this.delegate = delegate;
        this.retryTemplate = retryTemplate;
    }

    @Override
    public T read() throws Exception {
        return retryTemplate.execute(context -> delegate.read());
    }

    @Override
    public void open(ExecutionContext executionContext) throws ItemStreamException {
        delegate.open(executionContext);
    }

    @Override
    public void update(ExecutionContext executionContext) throws ItemStreamException {
        delegate.update(executionContext);
    }

    @Override
    public void close() throws ItemStreamException {
        delegate.close();
    }
}

Then you can use it as an item reader in your step:

@Bean
@StepScope
public RetryableItemReader<Student> storedProcItemReader() {
    RetryTemplate retryTemplate = new RetryTemplateBuilder()
            // define your retry policy
            .build();
    StoredProcedureItemReader<Student> delegateItemReader = new StoredProcedureItemReaderBuilder<Student>()
            // define your delegate item reader
            .build();
    return new RetryableItemReader<>(delegateItemReader, retryTemplate);
}