In my application I import measurement data into a database using Hibernate with Spring Data. The source could provide data for timestamps with already existing db data in order to make modifications. Therefore all entries within the time period (given by the source data) is deleted before the entries are being written again.

In the case of existing data I get this exception on saveAll():

"Row was updated or deleted by another transaction" (or unsaved-value mapping was incorrect)

However, the row should be deleted in the same transaction and I expect Hibernate to understand that it has to re-INSERT the entry instead of performing an UPDATE.

The repository methods are provided by Spring Data. Here is a simplified version of my code:

@Transactional(rollbackFor = Exception.class)
private void import(List<Entry> entries) {
    // ...
    this.measurementRepo.removeAllByTimeIsBetween(firstDt, lastDt);
    this.measurementRepo.saveAll(measurements);
}

What is the problem here? Is this unallowed? Or is it a false assumption that both operations belong to a single transaction? However, it should work, even if there are two transactions. Is there a timing problem? Is it guaranteed that the operations are executed in the given order? Is it a problem with Spring Data?

I can't do these operations in two separate transactions (if this could be a solution), because I only want to delete rows if I'm sure that the new rows are inserted as well.

My work-around idea for this problem is to detect existing rows and to perform an update instead of delete, for entries with matching time stamps. I'm not sure if this will work yet.

CodePudding user response：

There is a "problem" when using Hibernate. Hibernate optimizes the statement execution order, and deletes are the last ones being done (the precise details I do not have right know), hence the problem you are getting .

To solve this use deleteInBatch. This way, deletes will occur first, and the problem is solved. I assume you have a method getAllByTimeIsBetween. But adapt the following to your needs:

@Transactional(rollbackFor = Exception.class)
private void import(List<Entry> entries) {
    // ...
    Object elementsToRemove = this.measurementRepo.getAllByTimeIsBetween(firstDt, lastDt);
    measurementRepo.deleteInBatch(elementsToRemove);

    this.measurementRepo.saveAll(measurements);
}

The following question can give you more details over deleteInBatch. It is not the same problem but some answers has some details over the Spring JPA and Hibernate.

CodePudding user response：

@Transactional doesn't work for bean internal calls. Spring creates a proxy for those objects but it cannot intercept the method calls inside the class itself (e.g. the call of this.import()).

Even after moving import() to a separate class it didn't work. That's because accidentally it was protected instead of public. I thought it's public, because I didn't remember that in Java foreign classes are allowed to access protected members of other classes (if they are in the same package). That's why I missed the important point from the documentation:

Another caveat of using proxies is that only public methods should be annotated with @Transactional. Methods of any other visibilities will simply ignore the annotation silently as these are not proxied.

It works now but I still don't know why it doesn't work with two separate transactions. One transaction that deletes the data and afterwards another transaction which creates new entries shouldn't be a problem.