Appropriate Solution to insert in one table and delete from another + Spring batch

[Sanjeevkumar]

Hi,
I have a requirement where i have to read from a file and insert them to a table after processing the read records. Also at the same time it has to delete from another table.

The deletion should happen only after the first transaction is successful, however any problem in the deletion should not cause a rollback of the prior i inserted record.

What would be the best way to achieve this? Currently i want to plugin the delete logic in the same itemwriter which inserts the records and spawn a new transaction for delete. But this approach seems to have some issues. Would CompositeItemWriter be another solution?

Thanks in advance.

[DHGarrette]

The transaction wraps around the entire ItemWriter.write() call, so putting both calls in the same ItemWriter or using a CompositeItemWriter wouldn't help you. It seems like the easiest thing would just be to have two steps. The first step would insert records. The second step could just say "delete from T where indicator=SOMETHING".

[K. Arnold]

Another option to try is using the @Transaction annotation to create a transactional boundary. You could create separate methods to handle your insert and delete and put them in your ItemWriter.write().

ItemWriter.write() {
writeInsert();
writeDelete();
}


@Transactional
private void writeInsert() {
}

@Transactional
private void writeDelete() {
}

[Sanjeevkumar]

If i create two steps, step2 will execute only after step1. Does this mean
that i will have to read the file all over again or the entire data is cached in memory during the execution of step2.

If not then i will possibly then consider this approach.

Thanks.

[DHGarrette]

No, when you insert the records, have a field that's like NEED_TO_DELETE_OTHER_THING=TRUE.

Then your delete statement can say:

delete from T 
    inner join INSERT_TABLE on T.key=INSERT_TABLE.key 
where INSERT_TABLE.NEED_TO_DELETE_OTHER_THING=TRUE

Then you can clean it up with:

update INSERT_TABLE set NEED_TO_DELETE_OTHER_THING=FALSE

Note that this will be faster than the approach of creating nested transactions because the transaction handling will make your job run considerably slower.

[K. Arnold]

Some time trial tests on my complex batch steps utilizing hibernate (which might be the key difference) has my transactions perform faster when I utilize the @Transaction annotation, and even faster if I just manage the transaction using the hibernate Session api myself.