Hibernate+H2涉及临时表时事务回滚失效问题（多表批量查询）

基于Java 17、Hibernate 6.4.1.Final和H2数据库2.2.224的Maven项目。

包含两个无关实体（MyFirstEntity和MySecondEntity），第二个实体存在自引用（@OneToOne映射搭配@JoinTable）。这些实体对应三张表：

• MY_FIRST_ENTITY：id、title（唯一约束）
• MY_SECOND_ENTITY：id、title
• MY_SECOND_ENTITY_MAPPING：a（关联MY_SECOND_ENTITY）、b（关联MY_SECOND_ENTITY）

本文末尾提供了可复现问题的Java文件、Hibernate配置文件和Maven pom文件，可用于复现问题。

将Spring Boot应用从2版本升级到3版本时遇到此问题，本文示例是DataJpaTest测试类中问题的极简复现版本。

在一个事务中，持久化一条title为"a"的MyFirstEntity记录（该列有唯一约束）并刷新会话，随后执行更新语句：UPDATE MY_SECOND_ENTITY SET title=null（因表中无数据，此操作本应无实际效果），之后事务执行回滚。

接着开启另一个事务，再次持久化一条title为"a"的MyFirstEntity记录并提交事务，此时触发唯一约束违反错误：

Unique index or primary key violation: "PUBLIC.CONSTRAINT_INDEX_2 ON PUBLIC.MY_FIRST_ENTITY(TITLE NULLS FIRST) VALUES ( /* 1 */ 'a' )"

这不符合预期，因为第一个事务已回滚。Hibernate使用临时表优化该更新语句（多表批量操作）。若移除更新语句，一切正常，第二个事务不会触发约束违反；移除flush调用或将更新语句移至插入语句前，也可解决约束违反问题。

SQL语句 #

第一个事务执行的SQL：

Hibernate: insert into MY_FIRST_ENTITY (title,id) values (?,?)
Hibernate: create local temporary table HT_MY_SECOND_ENTITY(id uuid not null, primary key (id))
Hibernate: insert into HT_MY_SECOND_ENTITY(id) select mse1_0.id from MY_SECOND_ENTITY mse1_0
Hibernate: update MY_SECOND_ENTITY set title=null where id in (select temptable_.id from HT_MY_SECOND_ENTITY temptable_)
Hibernate: delete from HT_MY_SECOND_ENTITY

第二个事务执行的SQL：

Hibernate: insert into MY_FIRST_ENTITY (title,id) values (?,?)

问题咨询 #

1. 为何某些场景下回滚失效？
2. 这是预期行为还是可认定为bug？
3. 欢迎提供任何帮助或提示。

import java.util.UUID;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.OneToOne;

public class DemoHibernateApplication {

    private static SessionFactory sessionFactory;

    private static final String CREATION_TABLE_1 = """
            CREATE TABLE "MY_FIRST_ENTITY" (
                "ID" UUID NOT NULL,
                "TITLE" VARCHAR(100) UNIQUE NOT NULL,
                PRIMARY KEY ("ID")
            );
            """;
    private static final String CREATION_TABLE_2 = """
            CREATE TABLE "MY_SECOND_ENTITY" (
                "ID" UUID NOT NULL,
                "TITLE" VARCHAR(100) NOT NULL,
                PRIMARY KEY ("ID")
            );
            """;
    private static final String CREATION_TABLE_3 = """
            CREATE TABLE "MY_SECOND_ENTITY_MAPPING" (
                "A" UUID NOT NULL,
                "B" UUID NOT NULL,
                PRIMARY KEY ("A", "B"),
                FOREIGN KEY("A") REFERENCES MY_SECOND_ENTITY("ID"),
                FOREIGN KEY("B") REFERENCES MY_SECOND_ENTITY("ID")
            );
            """;

    public static void main(String[] args) {
        createTables();
        executeQueries();
    }

    private static void createTables() {
        try (Session session = getSessionFactory().openSession()) {
            Transaction transaction = session.beginTransaction();

            session.createNativeMutationQuery(CREATION_TABLE_1)
                    .executeUpdate();
            session.createNativeMutationQuery(CREATION_TABLE_2)
                    .executeUpdate();
            session.createNativeMutationQuery(CREATION_TABLE_3)
                    .executeUpdate();

            transaction.commit();
        }
    }

    private static void executeQueries() {
        try (Session session = getSessionFactory().openSession()) {
            Transaction transaction = session.beginTransaction();

            session.persist(newMyFirstEntityWithTitle("a"));
            session.flush();
            session.createMutationQuery("UPDATE MY_SECOND_ENTITY SET title=null")
                    .executeUpdate();

            transaction.rollback();
        }

        try (Session session = getSessionFactory().openSession()) {
            Transaction transaction = session.beginTransaction();

            session.persist(newMyFirstEntityWithTitle("a"));

            transaction.commit();
        }
    }

    private static MyFirstEntity newMyFirstEntityWithTitle(String title) {
        MyFirstEntity myFirstEntity = new MyFirstEntity();
        myFirstEntity.setId(UUID.randomUUID());
        myFirstEntity.setTitle(title);
        return myFirstEntity;
    }

    private static SessionFactory getSessionFactory() {
        if (sessionFactory == null) {
            StandardServiceRegistry registry = new StandardServiceRegistryBuilder().build();

            sessionFactory = new MetadataSources(registry)
                    .addAnnotatedClass(MyFirstEntity.class)
                    .addAnnotatedClass(MySecondEntity.class)
                    .buildMetadata()
                    .buildSessionFactory();
        }
        return sessionFactory;
    }

    @Entity(name = "MY_FIRST_ENTITY")
    public static class MyFirstEntity {

        @Id
        private UUID id;

        @Column(nullable = false, unique = true, columnDefinition = "nvarchar")
        private String title;

        public UUID getId() {
            return id;
        }

        public void setId(UUID id) {
            this.id = id;
        }

        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

    }

    @Entity(name = "MY_SECOND_ENTITY")
    public static class MySecondEntity {

        @Id
        private UUID id;

        @Column(nullable = false, columnDefinition = "nvarchar")
        private String title;

        @OneToOne
        @JoinTable(name = "MY_SECOND_ENTITY_MAPPING", joinColumns = { @JoinColumn(name = "a") }, inverseJoinColumns = { @JoinColumn(name = "b") })
        private MySecondEntity anotherMySecondEntity;

        public UUID getId() {
            return id;
        }

        public void setId(UUID id) {
            this.id = id;
        }

        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

        public MySecondEntity getAnotherMySecondEntity() {
            return anotherMySecondEntity;
        }

        public void setAnotherMySecondEntity(MySecondEntity anotherMySecondEntity) {
            this.anotherMySecondEntity = anotherMySecondEntity;
        }

    }

}

hibernate.properties #

hibernate.connection.url=jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1
hibernate.connection.username=sa
hibernate.connection.password=

hibernate.show_sql=true

hibernate.hbm2ddl.auto=none

pom.xml #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>demo-hibernate</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo-hibernate</name>

    <properties>
        <maven.compiler.release>17</maven.compiler.release>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.4.1.Final</version>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.2.224</version>
        </dependency>
    </dependencies>

</project>

内容的提问来源于stack exchange，提问作者eee