Correct way to prevent rollback of large transaction for simple primary key violations on a few of the many records?

[Aaron Daubman]

Greetings,

What is the correct way to allow MyBatis to commit a large batch insert operation where some of the values being inserted violate a primary key constraint?

e.g. primary key constraint violation should be ignored/logged and not cause the entire (very large) transaction to be rolled back.

Put another way, the data coming in may be 'dirty' in that some records are duplicated and can safely be ignored, while the many other valid records should still be preserved (committed).

Right now, I am sending a list with three objects, one of which has the same value for primary key field as a record already in the DB. 

This causes:

### Error committing transaction.  Cause: org.apache.ibatis.executor.BatchExecutorException: dataCore.batchInsertdataCore (batch index #1) failed. Cause: java.sql.BatchUpdateException: Duplicate entry '222' for key 'PRIMARY'

### Cause: org.apache.ibatis.executor.BatchExecutorException: dataCore.batchInsertdataCore (batch index #1) failed. Cause: java.sql.BatchUpdateException: Duplicate entry '222' for key 'PRIMARY'

The records that would have been inserted for the other two valid objects are 'lost' due to this one record failing.

Thanks once again,

     Aaron

[Aaron Daubman]

To add some further details:

I could simply switch to INSERT IGNORE, however, IIRC, this will _silently_ ignore primary key constraint violations, and I would still like to know about them. Is there a way to log/warn on them (as is done now through MyBatis) but NOT rollback the transaction as MyBatis does now for these errors?

[Chema]

2012/4/8 Aaron Daubman <dau...@gmail.com>:

> To add some further details:
>
> I could simply switch to INSERT IGNORE, however, IIRC, this will _silently_
> ignore primary key constraint violations, and I would still like to know
> about them. Is there a way to log/warn on them (as is done now through
> MyBatis) but NOT rollback the transaction as MyBatis does now for these
> errors?
>

Do you have tried to catch these exceptions and execute a commit()
when batch task is finished ?

[donal...@noaa.gov]

I am having the same problem.  Did you find a solution?

Thanks!

On Sunday, April 8, 2012 1:09:48 AM UTC-4, Aaron Daubman wrote:

[Steve Hill]

The transaction manager that you are using it what is determining whether to commit or rollback.  If you didn't want the data to rollback if there were exceptions thrown you could run this insert outside of a transaction.  Alternatively (and possibly preferably) you could loop through your records and perform separate inserts from some place in your application that is outside of the transaction boundary.

I would advise caution though, if the application were shut down, the database went away etc, it would be very easy to lose the integrity of the data and knowledge of the state of the data in the database.  You may want to take another approach.

1.  You could remove the integrity constraint in the database and after processing, deduplicate the data before committing the transaction.
2.  Before each insert, you could check to see if the record already existed and if it did, skip the insert or make it an update.
3.  If there will be no conflicts with previously inserted data, you could perform the deduplication in Java prior to inserting the data.

Thanks!
Steve.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Hugh Ross]

I'd suggest either:

1 -- Don't use Batches; or,

2 -- Use INSERT ... ON DUPLICATE KEY UPDATE (or MERGE or UPSERT, depending on your DBMS)

 

By examining the DetailedBatchResults, you should be able to detect that an Update occurred..

If you do not want the Update to occur, either, you might (again, depending on the DBMS) be able to code it such that the Update always gets ROW_NOT_FOUND..