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

问题描述

问题概述

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

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

  create sequence PERSONS_SEQ; 
创建序列VISITS_SEQ; 
等... 
  

我们使用这些序列为实体生成主键这：

  @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; 
 ... 
} 
  

分析

我认为我认识到这个配置存在两个问题：
$ b 1）两个@SequenceGenerator都指定了相同的名称属性虽然他们应该映射到不同的数据库序列。
$ b 2）@SequenceGenerator的分配大小属性默认为50（我们使用hibernate作为JPA提供程序），所以我认为创建序列语法应该指定序列应该增加多少，特别是50来匹配分配大小。

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

 创建序列PERSONS_SEQ增量50; 
创建序列VISITS_SEQ增量50; 
等... 
 
 @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; 
 ... 
} 
  

我只是测试这个而不是问关于SO的问题，但是再一次，我们无法在任何其他环境中重现此生产问题。即使在生产过程中，唯一的约束违规行为也只发生在看似随机的时间。

问题：

）1）我是否正确分析了应该修改这个唯一约束违规的变化？使用hibernate作为JPA提供者时的生成器？

解决方案

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

   
   
   • 要么手动生成序列表对于具有正确初始值/初始ID的新序列生成器（否则hibernate将从1开始，您将再次获得）
     
   • 或在Code中设置该值（检查 initalValue in @SequenceGenerator ）。
   
   

我无法列举最佳实践，但我想你可以降低50的限制。我也没有使用PostgreSQL的经验，但在MySQL中，你有一个简单的seq表。生成器和休眠使得整个东西。

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.

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:

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.

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.

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.

Questions:

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

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

解决方案

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 initalValue in @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.

这篇关于JPA 2 @SequenceGenerator @GeneratedValue产生唯一的约束违规的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！