'Batch update returned unexpected row count from update' - after update to Micronaut 3.1+

Question

I'm attempting to upgrade to Micronaut 3.2, but starting from 3.1 some write operations on the db started failing. I have created a sample project to showcase this: https://github.com/dpozinen/optimistic-lock

furthermore I have created an issue at https://github.com/micronaut-projects/micronaut-data/issues/1230

Briefly, my entities:

@MappedSuperclass
public abstract class BaseEntity {

  @Id
  @GeneratedValue(generator = "system-uuid")
  @GenericGenerator(name = "system-uuid", strategy = "uuid2")
  @Column(updatable = false, nullable = false, length = 36)
  @Type(type = "optimistic.lock.extra.UuidUserType")
  private UUID id;

  @Version
  @Column(nullable = false)
  private Integer version;
}

public class Game extends BaseEntity {
  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  @ToString.Exclude
  @OrderColumn(name = "sort_index")
  private List<Question> questions = new ArrayList<>();
}

@Inheritance(strategy = InheritanceType.JOINED)
@Table(name = "question")
public abstract class Question extends BaseEntity {
  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  @ToString.Exclude
  @OrderColumn(name = "sort_index")
  private List<AnswerOption> answerOptions = new ArrayList<>();
}

public class ImageSingleChoiceQuestion extends Question {
  @OneToOne(cascade = CascadeType.ALL)
  private AnswerOption validAnswer;
}

@Table(name = "answer_option")
public class AnswerOption extends BaseEntity {}

Pretty basic setup. The exception occurs when I'm deleting the question from the game:

Game game = gameRepository.findById(gameId).orElseThrow()
Question question = questionRepository.findByGameAndId(gameId, questionId).orElseThrow()

game.getQuestions().remove(question)
gameRepository.saveAndFlush(game) // optional

Expected result: the question gets detached from the game and deleted, cascading the deletion to answerOptions. This was working up until Micronaut 3.0.3, you can change the version in the sample project and the test will succeed.

Actual result:

javax.persistence.OptimisticLockException: 
Batch update returned unexpected row count from update [0];
actual row count: 0; expected: 1; 
statement executed: delete from question where id=? and version=?

Here is the SQL that gets executed, notice the version numbers getting messed up. Any help would be greatly appreciated.

Edit 1:

Problem occurs only when applying ImageSingleChoiceQuestion#setValidAnswer with an instance that is also used in Question#setAnswerOptions

why is that the case, since this worked in Micronaut 3.0.3?

Edit 2:

• verified to be working on spring

Edit 3:

• confirmed as bug and fixed in PR

Answer 1

Update As @saw303 correctly points to, it is a bug in Micronaut Data version <= 3.2.0. The bug was fixed in io.micronaut.data:micronaut-data-hibernate-jpa:3.2.1 which was released 2021-12-07 (today when writing this).

https://github.com/micronaut-projects/micronaut-data/releases

I updated my PR with this change.

---

I was able to reproduce the issue using code from your repo. Micronaut Data change log reveals that there are relevant changes in v3.1.0. https://github.com/micronaut-projects/micronaut-data/releases

Problem occurs only when applying ImageSingleChoiceQuestion#setValidAnswer with an instance that is also used in Question#setAnswerOptions.

Hence problem goes away when doing like this: question.setValidAnswer(new AnswerOption());. Explanation: When using same AnswerOption instance both in one-to-many and in one-to-one, Hibernate tries to delete instances in two places.

The reason for this seems to be CascadeType.ALL. When removing it from class Question, test is passing.

The following solution pass the test, but currently I'm not able to explain why it is working based on the generated SQL. Some further investigation is required for this approach.

@Getter
@Setter
@ToString
@Entity(name = "ImageSingleChoiceQuestion")
@Table(name = "image_single_choice_question")
public class ImageSingleChoiceQuestion extends Question {

    @OneToOne
    @PrimaryKeyJoinColumn
    private AnswerOption validAnswer;
}

Best explainable solution that passes the test so far, is to replace @OneToOne with @OneToMany like below. Hibernate will with this approach create join-table image_single_choice_question_valid_answers. This is of course not optimal.

@Getter
@Setter
@ToString
@Entity(name = "ImageSingleChoiceQuestion")
@Table(name = "image_single_choice_question")
public class ImageSingleChoiceQuestion extends Question {

    @OneToMany
    private Set<AnswerOption> validAnswers = new HashSet<>();

    public AnswerOption getValidAnswer() {
        return validAnswers.stream().findFirst().orElse(null);
    }

    public void setValidAnswer(final AnswerOption answerOption) {
        validAnswers.clear();
        validAnswers.add(answerOption);
    }
}

PR here: https://github.com/dpozinen/optimistic-lock/pull/1

I added testcontainers to your project, and test class look like this now:

package optimistic.lock

import optimistic.lock.answeroption.AnswerOption
import optimistic.lock.image.ImageSingleChoiceQuestion
import optimistic.lock.testframework.ApplicationContextSpecification
import optimistic.lock.testframework.testcontainers.MariaDbFixture

import javax.persistence.EntityManager
import javax.persistence.EntityManagerFactory

class NewOptimisticLockSpec extends ApplicationContextSpecification
        implements MariaDbFixture {

    @Override
    Map<String, Object> getCustomConfiguration() {
        [
                "datasources.default.url"     : "jdbc:mariadb://localhost:${getMariaDbConfiguration().get("port")}/opti",
                "datasources.default.username": getMariaDbConfiguration().get("username"),
                "datasources.default.password": getMariaDbConfiguration().get("password"),
        ]
    }

    GameRepository gameRepository
    QuestionRepository questionRepository

    TestDataProvider testDataProvider
    GameTestSvc gameTestSvc

    EntityManagerFactory entityManagerFactory
    EntityManager entityManager

    @SuppressWarnings('unused')
    void setup() {
        gameRepository = applicationContext.getBean(GameRepository)
        questionRepository = applicationContext.getBean(QuestionRepository)

        testDataProvider = applicationContext.getBean(TestDataProvider)
        gameTestSvc = applicationContext.getBean(GameTestSvc)

        entityManagerFactory = applicationContext.getBean(EntityManagerFactory)
        entityManager = entityManagerFactory.createEntityManager()
    }

    void "when removing question from game, game has no longer any questions"() {
        given:
        def testGame = testDataProvider.saveTestGame()
        and:
        Game game = gameRepository.findById(testGame.getId()).orElseThrow()
        and:
        Question question = testGame.questions.first()
        assert question != null
        and:
        def validAnswer = ((ImageSingleChoiceQuestion)question).getValidAnswer()
        assert validAnswer != null

        when:
        gameTestSvc.removeQuestionFromGame(game.getId(), question.getId())

        then:
        noExceptionThrown()
        and:
        0 == entityManager.find(Game.class, game.getId()).getQuestions().size()
        and:
        null == entityManager.find(AnswerOption.class, validAnswer.getId())
    }
}

Answer 2

You are suffering from a bug that was introduced in Micronaut Data 3.1. In your case the version property is incorrectly incremented. This bug has been confirmed by the Micronaut Data team and there is a pending pull request (see PR) that will fix the problem.

It's about the io.micronaut.data.runtime.event.listeners.VersionGeneratingEntityEventListener that incorrectly increments the version property of your JPA entity (see code pointer).

I think you can expect a fix in the upcoming Micronaut release 3.2.2