Hibernate + MySql and 'Duplicate entry' on insert followed by update or on multiple updates

Hibernate ORM
1

I came across this false “Duplicate entry” problem because mysql is checking on each sql statement the unique constraints. More over, I do not seem to find a well documentation solution somewhere.

The problem: With hibernate+mysql, we want to modify an entire collection (using insert/update instead od deleteAll/insertAll), then even though your new collections is compliant with the unique constraint, you can get ‘Duplicate entry’ since each time one entity of the collection is insert or update, the unique constaint get applied and it will detect that the collection is, in some point in time, having duplicate entry even though at the end of the transaciton it would be just fine.

Examples:
If we have a unique constraint on ValueUniqueA
Then the problem occurs with the 2 use cases below

Use case 1 on insert/update:

Transaciton 1:
insert into TABLE(ID,ValueUniqueA) VALUES(1,1)

Transaction 2:
insert TABLE(ID,ValueUniqueA) VALUES(2,1) <— mysql detect ‘Duplicat entry’
update TABLE(ID,ValueUniqueA) VALUES(1,2)

Use case 2 on multiple update (swap the values on updates):

Transaciton 1:
insert TABLE(ID,ValueUniqueA) VALUES(1,1)
insert TABLE(ID,ValueUniqueA) VALUES(2,2)

Transaction 2:
update TABLE(ID,ValueUniqueA) VALUES(2,1) <— mysql detect ‘Duplicat entry’
update TABLE(ID,ValueUniqueA) VALUES(1,2)

What can be done with hibernate to make those second transactions works???
In Oracle, it is simple you delay the unique constraint at the end of the transaction but mysql?

2

For use case 1 where we have a insert/update causing the ‘duplicate entry’ we can fix it using INSERT INTO ON DUPLICATE KEY like this

@Entity
@SQLInsert(sql = "INSERT INTO TABLE (ValueUniqueA) VALUES (?) ON DUPLICATE KEY UPDATE ValueUniqueA = VALUES(ValueUniqueA)")

However, use case 2 with 2 updates it is still a problem.

3

Finally, this is nearly impossible. The two workaround I found:

  1. Execute a findAll deleteAll reinsertAll (could keep entityId for the entities that was already existing but not sure how to do so)

  2. Update All Rows to have unique values not in the range used (i.e. ValueUniqueA= ValueUniqueA +10000 or ValueUniqueA=-ValueUniqueA). Then insert and update the new entities

I miss so much the defferable constraint of oracle…