ConstraintViolationException during transaction for entity with index

Question

I have the following entity:

@Entity
@Table(name = "product",
        indexes = {@Index(name = "productIndex", columnList = "family, group, type", unique = true)})
public class Product implements Serializable {

    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "family")
    private String family;

    @Column(name = "group")
    private String group;

    @Column(name = "type")
    private String type;

}

And I'm trying to empty its database table, and insert new rows:

@Transactional
public void update(List<Product> products) {
    productRepository.deleteAll();
    productRepository.batchInsert(products);
}

ProductRepository is:

public interface ProductRepository extends JpaRepository<Product, Long>, BatchRepository<Product> { }

And BatchRepository:

@Repository
public class BatchRepository<T> {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public int batchInsert(List<T> entities) {
        int count = 0;

        for (T entity : entities) {
            entityManager.persist(entity);
            count++;

            if (count % 2000 == 0) {
                entityManager.flush();
                entityManager.clear();
            }
        }

        entityManager.flush();
        entityManager.clear();

        return count;
    }

}

Trying to perform the insertion fails with java.sql.BatchUpdateException: ORA-00001: unique constraint (productIndex) violated, but shouldn't the previous deletion make this violation impossible to happen?

Update #1

@Transactional
public void update(List<Product> products) {
    productRepository.deleteAll();
    insertProducts(products);
}

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void insertProducts(List<Product> products) {
    productRepository.batchInsert(products);
}

Answer 1

I think I've found a few answers that address your problem.

Solution 1 - #link

Annotate your transactional method with:

@Modifying(flushAutomatically = true)

That will make sure that deleteAll call is flushed, ie. indexes are updated, before the following batchInsert call.

Solution 2 - #link

Use deleteAllInBatch from JpaRepository instead of deleteAll.

Solution 3 - #link

Manually invoke flush after deleteAll in the transactional method.

Solution 4

A more custom SQL solution would be to batch insert new records into a temporary table. Then you can either:

1. Drop the existing product table and rename the temporary one into product.
2. SELECT * FROM #temp_table INTO product - put all records from temp table to product.

Let me know if any of that works. Good luck!