Situation

In https://github.com/mercedes-benz/sechub/issues/915 we wanted to update to Spring Boot 2.7.x. But our DB unit tests did fail now.

So I started to analyze and found a problem with the h2 database usage inside tests.

In a nutshell

When a test first stores an entity inside database, than load it again as an entity and does an update, we have an optimistic lock error

It does work with h2 version 1.4.200 but not with 2.x

Examle

        /* prepare */
        MyEntity entityA = new MyEntity();
        repositoryToTest.save(entityA);

        UUID uuid = entityA.getUUID();

        /* execute */
        MyEntity entityALoadedAndChanged = repositoryToTest.findById(uuid).get();
        entityALoadedAndChanged.justABooleanToHaveAnUpdate = true; 

        repositoryToTest.save(entityALoadedAndChanged); // not possible with h2 version >=2.x

        /* test */
        List<MyEntity> allTestData = repositoryToTest.findAll();
        assertEquals(1, allTestData.size());

Analyze

I created a very simple test gradle project (see attached zip file) which has a Junit test inside which reproduces the Problem

When the gradle project is imported and the test is started, an optimistic lock exception will occur:

org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update my_entity set enabled=?, version=? where uuid=? and version=?; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update my_entity set enabled=?, version=? where uuid=? and version=?
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:318)
    ...
Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update my_entity set enabled=?, version=? where uuid=? and version=?
    at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:67)
    ...

When inside the build.gradle file the h2 database is changed back to v1.4.200 (as used in Spring Boot 2.6.9) via 

// when next line is added - the test will work, because we use h2 version < 2.x
ext['h2.version'] = '1.4.200'

then the test is green..

h2-jpa-problem-demo-project.zip

Comment From: wilkinsona

Thanks for the analysis. As you have identified that the change in behaviour is due to changes in H2, this will have to be address in H2 itself or possibly in Hibernate's H2 support. There's nothing that we can do about it in Spring Boot (or, I suspect, in Data JPA either).

Comment From: de-jcup

@wilkinsona : A test should always have the possiiblity to define test data in database - e.g. for a spring boot service which updates the former prepared data... Spring boot has a h2 version 2 as a new dependency. The use case is not very exotic to have a test changing data. So this problem should be addressed.

Comment From: wilkinsona

I agree that it should be addressed, however there's nothing that we can change in Spring Boot to address it. Downgrading back to H2 1.4 is not an option due to the unaddressed security vulnerabilities that it contains. Anything else is out of our control as changes would have to be made to the code in H2, Hibernate, or Spring Data JPA. All of which are separately maintained projects.

Comment From: de-jcup

The H2 team helped me to find a workaround - read https://github.com/h2database/h2database/issues/3563#issuecomment-1169948389 for details.

The workaround used by myself now for the optimistic lock exception is, to add inside the Entity class for the PK not 

   @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    @Column(name = COLUMN_UUID, updatable = false, nullable = false)
    UUID uUID;

but additional define as column definition "UUID":

    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    @Column(name = COLUMN_UUID, updatable = false, nullable = false, columnDefinition="UUID")
    UUID uUID;

With the mentioned changes the test does no longer fail with optimistic lock exception and works as expected.

It seems to be a bug in hibernate - I created https://hibernate.atlassian.net/browse/HHH-15373 to address the problem.