How to have next Steps read data on tables updated by previous Steps in Spring Batch?

Question

I have a job that has 2 steps, with their own ItemReader and ItemWriter. The 1st Step reads and updates a table. The 2nd Step should read from the same updated table then transfers entries to another database. However, when testing, the 2nd Step does not read the updated data from the table, it reads the data prior to the update done by the 1st Step. Is there a way for a second Step in a Job to read a table that's been updated by a previous Step?

From what I've researched so far, this involves isolation and propagation. Would anyone be able to give insight on this? Any help would be much appreciated, thank you!

A sample structure of my job config is below:

<job id="example-job" job-repository="jobRepository" xmlns="http://www.springframework.org/schema/batch">         
           <step id="update_table" parent="simpleStep" next="use_updated_data"> <!-- step 1 -->
             <tasklet>
                 <chunk writer="updateTableItemWriter" reader="updateTableItemReader" commit-interval="100"/>
             </tasklet>            
             </step>
           <step id="use_updated_data" parent="simpleStep"> <!-- step 2 -->
             <tasklet>
                 <chunk writer="useUpdatedTableItemWriter" reader="useUpdatedTableItemReader" commit-interval="100"/>
             </tasklet>            
             </step>
 </job>

The Job's first step checks for characters in entries on the database table and updates those rows to remove those characters. Then the second step takes those entries and moves to another database entirely. Upon running, the second step doesn't see the update of the first step on the table.

EDIT:

From how I understand, transactions should commit contained in each step when ItemWriter is invoked.

My ItemWriters and ItemReaders

    <beans:bean name="updateTableItemReader" scope="step" class="org.springframework.batch.item.database.JdbcCursorItemReader">
         <beans:property name="dataSource" ref="oracleDS" />
         <beans:property name="sql" value="${read.findEntriesWithChar}">
         </beans:property>
         <beans:property name="rowMapper">
             <beans:bean name="mapper" class="com.myProj.batch.tableRowMapper" />
         </beans:property>
     </beans:bean>    
     
     <beans:bean  id="updateTableItemWriter" class="org.springframework.batch.item.database.JdbcBatchItemWriter">
          <beans:property name="assertUpdates" value="false" />
          <beans:property name="itemSqlParameterSourceProvider">
               <beans:bean name="sqlParameterSourceProvider" class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
          </beans:property>
          <beans:property name="sql" value="${write.updateEntriesReplaceChar}"/>  
          <beans:property name="dataSource" ref="oracleDS" />
     </beans:bean>
 
     <beans:bean name="useUpdatedTableItemReader" scope="step" class="org.springframework.batch.item.database.JdbcCursorItemReader">
         <beans:property name="dataSource" ref="oracleDS" />
         <beans:property name="sql" value="${read.useUpdatedTableNoChar}">
         </beans:property>
         <beans:property name="rowMapper">
             <beans:bean name="mapper" class="com.myProj.batch.tableRowMapper" />
         </beans:property>
     </beans:bean>    
     
     <beans:bean  id="useUpdatedTableItemWriter" class="org.springframework.batch.item.database.JdbcBatchItemWriter">
          <beans:property name="assertUpdates" value="false" />
          <beans:property name="itemSqlParameterSourceProvider">
               <beans:bean name="sqlParameterSourceProvider" class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
          </beans:property>
          <beans:property name="sql" value="${write.useUpdatedTableNoCharTransfer}"/>  
          <beans:property name="dataSource" ref="sybaseDS" />
     </beans:bean>
 
     <beans:bean id="simpleStep" class="org.springframework.batch.core.step.item.SimpleStepFactoryBean" abstract="true">
         <beans:property name="transactionManager" ref="transMan_sybase" />
         <beans:property name="jobRepository" ref="jobRepository" />
         <beans:property name="startLimit" value="100" />
         <beans:property name="commitInterval" value="1" />
     </beans:bean>

EDIT2

Turns out the reader and writers were fine but the queries themselves were the issue.

I'm trying to implement the queries from a separate .properties file with these SET and WHERE conditions:

... SET desc = replace(desc, UNISTR('\2013'), '-') WHERE desc like '%' || UNISTR('\2013') || '%'

These don't work by themselves and are the cause of the job failure. What do I need to do to implement them? Do I need to enclose them in backticks?

Wouldn't that be more efficient in a single step? That being said that shouldn't happen, what do your readers look like? Do they use JPA? SQL? what are the readers like. — M. Deinum, Jul 25 '23 at 07:43
They don't use JPA and we use SQL. Edited to add the readers and writers — Vincere, Jul 25 '23 at 10:51
I found that it was my queries themselves that were causing the problem. I have them listed on a separate .properties, called by a bean with class as PropertyPlaceholderConfigurer. I've edited my post and included the SQL. How would one implement these in the property with intended results? Do I need to contain them in backticks? — Vincere, Jul 25 '23 at 20:49
I don't see what would be wrong, what is the resulting query you get? — M. Deinum, Jul 26 '23 at 06:09
Turns out, UNISTR('\2013') needs to have an additional backslash when used in database operations in JDBC. https://docs.oracle.com/en/database/other-databases/timesten/22.1/java-developer/database-operations-jdbc.html#GUID-67A44218-53C3-45E8-B984-13C2D0F9E287 so it should be UNISTR('\\2013') — Vincere, Jul 26 '23 at 07:21

Vladimir Medvedev · Answer 1 · 2023-07-25T08:24:34.300

0

seems that it requires a new transaction, try to add in config

<step id="use_updated_data" parent="simpleStep">
  <tasklet>
   <transaction-attributes propagation="REQUIRES_NEW">
   <chunk writer=..... />
  </tasklet>

edited Jul 25 '23 at 08:24

answered Jul 25 '23 at 07:53

Vladimir Medvedev

1
3

Will this only apply to the step? (I.e. other steps will not change propagation) and how does REQUIRES_NEW fix the issue? I was under the impression that each step should be able to read-process-write/commit separately but it seems in the job, step 2 still sees the initial read at step 1. – Vincere Jul 25 '23 at 10:53
It should work without this additional attributes, but seems that transaction not commeted. I think you should add to your readers and writers scope = step. Probably this is why transaction not closed. Or something wrong with queries. Are you able to check data in DB after the first step? – Vladimir Medvedev Jul 25 '23 at 11:36
The DB wasn't updated when I checked. The job fails entirely so it seems the job rollsback all the changes, even after step 1 executes. – Vincere Jul 25 '23 at 11:53

How to have next Steps read data on tables updated by previous Steps in Spring Batch?

1 Answers1