"Row was updated or deleted by another transaction" - but it's deleted in the same transaction

Question

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.

How are you doing the deletes and saves? If you worked purely with managed entities it should work, you can go from `persistent` to `removed` and back, but if you mix in other operations it might get confused. Also, is it a hard requirement that database gets the DELETE and INSERT, or is it fine if it gets optimized out at hibernate level? — Deltharis, Feb 28 '22 at 09:21
I only call the Spring Data repository methods. Even the `remove...` method is implemented automatically by Spring Data. It's not a hard requirement with DELETE/INSERT. Actually wrote it this way to have it as simple as possible. Checking if an entry already exists is more complex and error prone than just deleting them all. — fishbone, Feb 28 '22 at 09:37

pringi · Answer 1 · 2022-02-28T09:44:28.827

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.

Even if this is correct in general - it seems that it was a different problem here. See my answer. I still wonder why it didn't work with two separate transactions. The problem you stated in your answer should only be a problem within a single transaction because I doubt that Hibernate will try to optimize queries among multiple transactions (?) I wonder if this is technically possible at all. — fishbone, Feb 28 '22 at 14:12

score 0 · Accepted Answer · answered Feb 28 '22 at 14:09

@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.

"Row was updated or deleted by another transaction" - but it's deleted in the same transaction

2 Answers2