Reg. transaction support for a spring batch job at job level

Question

Lets assume that i need to execute a spring batch job with 2 steps.step 1 is to read data from a postgres table and update values in the same table. step 2 is to read data from another postgres table and update this table. How can i achieve transactions at job level for this scenario?

That is, if the second step fails, then the first step should be rolled back.

Answer 1

Put a BEGIN statement before Step 1 and a COMMIT statement after Step 2.

Answer 2

i'm not sure if there even exists an solution with automatic chained/multi-level transaction handling that works reliable (or does not need a lot resources on database side)

if the second step fails, then the first step should be rolled back

well you could combine both steps into one:

• read from first table A
• use processor to update table A
• use processor to read from table B
• use writer to update table B

the performance will suffer a lot, because the read on table B will be a single read vs the cursor based for table a

i would go with a compensating strategy like this

• (optional) tables in use are temporary tables and not the real "production" tables, makes it easier to work with compensating with decoupling the datastores from the production
• a failed step 1 triggers another step or another job/script
• this step/job/script deletes as necessary (rows or complete table)

Answer 3

Have you considered using job-level transactionality?

Job level Transactionality in Spring Batch

It's important to consider the potential volume size you are managing, in order to avoid out-of-the-limit commits or rollbacks.