Java JPA 2 @SequenceGenerator @GeneratedValue 产生唯一约束违规

JPA 2 @SequenceGenerator @GeneratedValue producing unique constraint violation

提问by axiopisty

Problem Overview

问题概述

At seemingly random times we get an exception "postgresql duplicate key violates unique constraint." I do think I know what our problem"s" are but I don't want to make changes to the code without having a reproducible test case. But since we haven't been able to reproduce it in any environment other than randomly in production, I'm asking assistance from SO.

在看似随机的时间，我们会收到一个异常“postgresql 重复键违反唯一约束”。我确实认为我知道我们的问题是什么，但我不想在没有可重现的测试用例的情况下对代码进行更改。但是由于我们无法在生产中随机以外的任何环境中重现它，我正在寻求 SO 的帮助。

In this project we have multiple postgres databases, and a primary key sequence configured for each table in each database. These sequences are created like this:

在这个项目中，我们有多个 postgres 数据库，并为每个数据库中的每个表配置了一个主键序列。这些序列是这样创建的：

create sequence PERSONS_SEQ;
create sequence VISITS_SEQ;
etc...

We use these sequences to generate the primary keys for the entities like this:

我们使用这些序列为实体生成主键，如下所示：

@Entity
@Table(name = "visits")
public class Visit {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "seq", sequenceName = "visits_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
  private int id;
  ...
}

@Entity
@Table(name = "person")
public class Person {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "seq", sequenceName = "persons_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
  private int id;
  ...
}

Analysis

分析

I think I recognize 2 problems with this configuration:

我想我认识到这个配置有两个问题：

1) Both @SequenceGenerators specify the same name attribute even though they are supposed to map to different database sequences.

1) 两个@SequenceGenerators 指定相同的名称属性，即使它们应该映射到不同的数据库序列。

2) The @SequenceGenerator allocationSize attribute defaults to 50 (we're using hibernate as the JPA provider) so I think the create sequence syntax should specify how much the sequence should increment by, specifically by 50 to match the allocationSize.

2）@SequenceGenerator allocationSize 属性默认为 50（我们使用 hibernate 作为 JPA 提供者）所以我认为创建序列语法应该指定序列应该增加多少，特别是 50 以匹配分配大小。

Based on this guess, I think the code should be modified to something like this:

基于这个猜测，我认为代码应该修改成这样：

create sequence PERSONS_SEQ increment by 50;
create sequence VISITS_SEQ increment by 50;
etc...

@Entity
@Table(name = "visits")
public class Visit {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "visits_seq", sequenceName = "visits_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "visits_seq")
  private int id;
  ...
}

@Entity
@Table(name = "person")
public class Person {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "persons_seq", sequenceName = "persons_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "persons_seq")
  private int id;
  ...
}

I would just test this rather than asking the question on SO, but again, we have not been able to reproduce this production issue in any other environments. And even in production the unique constraint violation only occurs at seemingly random times.

我只是对此进行测试，而不是在 SO 上提问，但同样，我们无法在任何其他环境中重现此生产问题。即使在生产中，唯一的约束违反也只会在看似随机的时间发生。

Questions:

问题：

1) Am I correct in my analysis of what the changes should be to fix this unique constraint violation?

1）我在分析应该进行哪些更改以修复这种独特的约束违规时是否正确？

2) What are the best practices for using sequence generators when using hibernate as a JPA provider?

2) 使用 Hibernate 作为 JPA 提供程序时使用序列生成器的最佳实践是什么？

采纳答案by Andrei I

1. Yes, your analysis is correct. You identified correctly the problem (we had a similar problem). And... if you gonna put that in production, don't forget to:

   • either generate manually the sequence table for the new sequence generator WITH the correct initial value/initial ID (otherwise hibernate will begin from 1 and you will get again )
   • or set that value in Code (check initalValuein @SequenceGenerator).

2. I am not able to enumerate the best practices, but I suppose you could lower the limit of 50. Also I do not have experience with PostgreSQL, but in MySQL you have a simple table for the seq. generator and hibernate makes the entire stuff.

1. 是的，你的分析是正确的。您正确识别了问题（我们遇到了类似的问题）。而且......如果你要把它投入生产，不要忘记：

   • 要么使用正确的初始值/初始 ID 手动生成新序列生成器的序列表（否则休眠将从 1 开始，您将再次获得）
   • 或在代码中设置该值（签initalValue入@SequenceGenerator）。

2. 我无法列举最佳实践，但我想您可以降低 50 的限制。此外，我没有使用 PostgreSQL 的经验，但在 MySQL 中，您有一个简单的 seq 表。发电机和休眠使整个东西。

回答by Simon Ludwig

I hade a similar problem. In my case, I imported data directly via SQL. This led to a problem with the 'hibernate_sequence'. The hibernate_sequence was by id 123 but there were rows in my table where the id was greater than 123.

我有一个类似的问题。就我而言，我直接通过 SQL 导入数据。这导致了“hibernate_sequence”的问题。hibernate_sequence 的 ID 为 123，但我的表中有些行的 ID 大于 123。

回答by Vladimir Rozhkov

Had a same problem —?for some reason hibernate wasn't picked the right number from the sequence. Tried all approaches with no luck and finally came to this solution:

遇到了同样的问题——出于某种原因，hibernate 没有从序列中选择正确的数字。尝试了所有方法都没有运气，最后得出了这个解决方案：

@Entity
@Table(name = "events")
@SequenceGenerator(name = "events_id_seq", sequenceName = "events_id_seq", allocationSize = 1)
public class Event {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "events_id_seq")
    private BigInteger id;

I've had to put @SequenceGenerator on top of class, not the method, also allocation size was set to 1 (if you'll left this value as default, it will start to produce negative ids).

我不得不将@SequenceGenerator 放在类的顶部，而不是方法的顶部，分配大小也设置为 1（如果您将此值保留为默认值，它将开始产生负 ID）。

spring-data-jpa 2.1.2, hibernate 5.3.7, pg 42.2.5

spring-data-jpa 2.1.2, hibernate 5.3.7,pg 42.2.5

回答by hari kanure

I gone through the same problem. and I tried this to fix the problem. may be this is not the best solution but i hope it will solve your problem for now.

我遇到了同样的问题。我试过这个来解决这个问题。可能这不是最好的解决方案，但我希望它现在可以解决您的问题。

@SequenceGenerator(schema = "DS_TEST",name = "SEQ_PR_TEXT",sequenceName = "SEQ_PR_TEXT",
        allocationSize = 1)
public class TextEntity {
    @Id
    @GeneratedValue(generator = SequenceConstant.SEQ_PR_TEXT,
            strategy = GenerationType.SEQUENCE)
    @Column(name = "PR_TEXT_ID")
    private Long id;
}