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);
}

score 3 · Accepted Answer · answered Feb 01 '23 at 11:51

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:

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

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

score 1 · Answer 2 · edited Feb 02 '23 at 10:58

1

According to me, you should perform deleteAll() operation separately as you are using @Transactional annotation and changes are not committed until the annotated function successfully returns. Because of that, your primary keys are still duplicated.

edited Feb 02 '23 at 10:58

maricn

593
1
6
21

answered Jan 30 '23 at 10:31

Tania Gupta

11
2

that's probably it, but I do both in the same transaction because I want it to undo changes if any error occurrs. Is there someway to update the indexes? – thmasker Jan 30 '23 at 10:33
1

You can write a different method for insertion of data and add @Transactional(propagation = Propagation.REQUIRES_NEW) annotation on it. Call the method in your update(...) method then. – Tania Gupta Jan 30 '23 at 11:19
your suggestion does not work – thmasker Jan 30 '23 at 12:18
1

Can you share the code snippet, how you implemented it. – Tania Gupta Jan 31 '23 at 05:54
updated the question with it! – thmasker Jan 31 '23 at 08:58

score 1 · Answer 3 · edited Feb 01 '23 at 14:25

1

You are getting this error because you put an unique constraint in the table index "productIndex"

You are attempting to delete all records in the first line

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

but I think the records remain in the index named "productIndex"

edited Feb 01 '23 at 14:25

maricn

593
1
6
21

answered Jan 30 '23 at 10:33

Ertan Cakir

21
5

yeah, that is basically what is happening, but I'm looking for a way to update that index table as well... I thought it was automatically handled – thmasker Jan 30 '23 at 11:01

score 1 · Answer 4 · answered Jan 31 '23 at 09:47

1

Can you try this code

public void update(List<Product> products) {
 deleteAll()
 insertProducts(products);
}

@Transactional
public void deleteAll() {
 productRepository.deleteAll();
}

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

answered Jan 31 '23 at 09:47