java - H2 中同一事务中切换两个唯一字段时违反唯一约束

Question

我有一个 Spring Boot 应用程序，使用 Spring Data 存储库以及使用 JPA 映射的实体。数据库是H2。该表是自动生成的。我在同一事务中进行两次更新。它们中的每一个单独都会违反唯一的约束，但是当两者一起应用时，结果应该很好。但是，我仍然遇到异常。

我尝试使用数据库工具(IntelliJ IDEA 中的数据库 View )进行相同的更新，以尝试查看是否有效，但出现此错误:

Unexpected update count received (Actual: 0, Expected: 1). All changes will be rolled back.

这是我的相关实体:

@Entity
@Table(
        name = "DATA_SET_COLUMN",
        uniqueConstraints = {
                @UniqueConstraint(columnNames = {"DATA_SET_ID", "ORDER_INDEX"})
        }
)
@Data
@EqualsAndHashCode(callSuper=true)
@NoArgsConstructor
@ToString(exclude = {"dataSet", "elements"})
public class DataSetColumn extends UniteFlowEntity {
    @Column(name = "ORDER_INDEX", nullable = false)
    private int order;

    @Column(name = "DATA_SET_ID", nullable = false)
    private String dataSetId;

    @ManyToOne(fetch= FetchType.LAZY)
    @JoinColumn(name="DATA_SET_ID", nullable = false, insertable = false, updatable = false)
    @JsonIgnore
    private DataSet dataSet;

    @OneToMany(mappedBy = "column", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JsonIgnore
    List<DataSetElement> elements;
}

请注意 DATA_SET_ID 和 ORDER_INDEX 的唯一约束。

我正在使用 Spring 数据存储库进行访问:

@Repository
public interface DataSetColumnRepository extends JpaRepository<DataSetColumn, String> {}

保存实体看起来像这样:

        repository.save(entity1);
        repository.save(entity2);

我有两个使用相同 DATA_SET_ID 存储的 DataSetColumn，并且想要切换 ORDER_INDEX(具有 0 的列获取 1，反之亦然)。这发生在同一个事务中(由 Spring 管理，由 Spring Boot 设置)。

我的期望是不会违反唯一约束，因为两次更新后表都应该有效。但是，我得到了这个:

update unitelabs.data_set_column set creation_date=?, modification_date=?, data_set_id=?, order_index=? where id=? [23505-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:101)
    at org.h2.mvstore.db.MVSecondaryIndex.requireUnique(MVSecondaryIndex.java:236)
    at org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:202)
    at org.h2.mvstore.db.MVTable.addRow(MVTable.java:732)
    at org.h2.table.Table.updateRows(Table.java:509)
    at org.h2.command.dml.Update.update(Update.java:177)
    at org.h2.command.CommandContainer.update(CommandContainer.java:102)
    at org.h2.command.Command.executeUpdate(Command.java:261)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:199)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:153)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3356)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3229)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3630)
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:146)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:532)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at uniteflow.dataset.api.rest.DataSetApi$$EnhancerBySpringCGLIB$$839b47f6.saveColumns(<generated>)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:189)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908)

我的期望是否错误并且原则上不起作用？还是我的代码有问题？

Answer 1

您的期望是错误的，要允许此类修改，唯一约束应显式定义为延迟(在支持可选功能 F721 的数据库中)，并且 H2 当前(截至 1.4.199)不支持此功能，有一个功能要求: https://github.com/h2database/h2database/issues/223

典型的解决方法是为第一行分配一个假值，然后将第二行更改为其新值，最后将第一行更改为其新值。