I am currently writing a Java application and using Batch Insertion in autocommit mode. My question is if I insert 4 rows in a batch and a BatchUpdateException is thrown because the second row of the batch have trigger a Duplicate Key violation! Does the DBC driver continue to process the 2 remaining row leaving the database with 3 inserted rows? Or does it stop at row 2 leaving the database with 1 inserted rows ? Or it rollback the whole batch leaving the database state with 0 inserted rows?
1 Answer
It works as this:
You have the chunk size mentioned in the step. Say for example the chunk size is 10.
So, every time a batch of 10 items will be committed.
Say, in a batch of 10 item, the 4th item throws duplicate key exception as is your case.
In that case, the whole batch will be rejected and the job will stop (if the skip policy is not implemented).
However, all the previous correct chunks which are already committed, will not be rolled back.
To add further, if after removing the incorrect data, if the same job is restarted, then the job will start exactly from the chunk where it errored last.
So, nothing happens to the data already written.
1 Comment
Serkan Arıkuşu
This is the tricky part: " if after removing the incorrect data, if the same job is restarted, then the job will start exactly from the chunk where it errored last."
autocommiton, I suspect that statements are potentially being executedWITH NC, meaning some of the rows may be updated. Which rows... is dependent on how any 'ordering' is implemented in the driver - if it decides to thread it, results are undefined.