Thread-safe findOrSave in Spring Data JPA

Question

Instead of naive implementation of findOrSave operation ...

public Foo findOrSaveFoo(Foo foo) {
    return fooRepository
            .findFoo(foo)
            .orElseGet(() -> saveFoo(foo));
}

... which has check-than-act concurrency problem, I need something like this:

public Foo findOrSaveFoo(Foo foo) {
    try {
        return saveFoo(foo);
    } catch (DataIntegrityViolationException ignore) {
        return fooRepository
                .findFoo(foo)
                .orElseThrow(AssertionError::new);
    }
}

But it causes problems with Session at .findFoo(foo) line:

null id in ... entry (don't flush the Session after an exception occurs)
org.hibernate.AssertionFailure: null id in ... entry (don't flush the Session after an exception occurs)

Is there a way to avoid this exception or any alternative thread-safe implementation for findOrSaveFoo?

No way to do this in a database agnostic correct way. There is no row to lock on. With something like MySQL you can `INSERT IGNORE` then `SELECT`. Threading isn’t the only issue here - there will be more than one application using the database. — Boris the Spider, Jun 04 '21 at 19:33
DB does the locking. It has unique constraint that won't allow duplicate rows in table, that's why saveFoo may fail. And when it fails, findFoo fill fetch that row in DB. I'm sure this would work in plain JDBC. — Nikola, Jun 04 '21 at 19:38
Not sure you’re correct. That’s not how transaction isolation levels work I’m afraid. — Boris the Spider, Jun 04 '21 at 19:58
So you claim you could end up with duplicate rows in a table protected with unique constraint due to race condition? — Nikola, Jun 04 '21 at 20:09
No. I claim that the way you’re handling errors wouldn’t handle that case and your application would crash rather than proceed. — Boris the Spider, Jun 04 '21 at 20:30
So the only workaround is to retry the first version if it fails due to row duplication? — Nikola, Jun 04 '21 at 20:50

score 0 · Answer 1 · answered Jun 07 '21 at 09:46

You will have to run this in a dedicated transaction by annotating the method with @Transactional(propagation = REQUIRES_NEW) because a constraint violation exception that occurs during a transaction will cause a transaction rollback. Some databases have a way to avoid the constraint violation error and instead do updates or ignore the failure, but that requires custom SQL. You can try the approach I outlined here: Hibernate Transactions and Concurrency Using attachDirty (saveOrUpdate)

Thread-safe findOrSave in Spring Data JPA

1 Answers1