重复的键值违反唯一约束“x_pkey”

Question

当并发客户机试图将数据插入子表时，我们面临唯一的约束冲突问题。

假设我们有1以下的表格。用户user_id、first_name、last_name。2.项目project_idproject_name和project_description。

两者都有着多对多的关系。

当两个客户端试图创建一个新用户时。假设client1创建了user1（id=aa1），子记录项目（id=1）。Client2还创建了user2（id=aa2），子记录是项目（id=1）。当两个客户端同时保存记录user1，并且子客户端也插入时，当第二个客户端尝试作为项目（id-1）已经可用时，它抛出唯一的密钥约束冲突。

在关联子记录之前，我们正在检查findby（projectx）。

我们很少面对这个问题。当出现异常时，我们已实施重试操作。即使使用重试操作，问题也无法一致解决。请在这方面指导我。

异常为：HH000346:托管刷新期间出错[org.hibernate.Exception.ConstraintViolationException:无法执行语句。错误[org.hibernate.engine.jdbc.spi.SqlExceptionHelper]（ForkJoinPool.commonPool-worker-1）错误：重复键值违反唯一约束“x_key”

我们正在使用这个示例代码。

@Entity
@Table(name = "tbl_users", schema = "abcd")
public class user implements Serializable {
    private static final long serialVersionUID = -8153443692098431986L;
    @Id
    @Column(name = "userId", nullable = false)
    private String userid;

    @Column(name = "last_name", nullable = false)
    private String lastName;

    @Column(name = "first_name", nullable = false)
    private String firstName;

    @ManyToMany(mappedBy = "projectsSet", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<Project> projectsSet;
}

@Entity
@Table(name = "tbl_projects", schema = "abcd")
public class Project implements Serializable {

    private static final long serialVersionUID = -8153443692098431986L;
    @Id
    @Column(name = "project_id", nullable = false)
    private String projectId;

    @Column(name = "project_Name", nullable = false)
    private String projectName;

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

    @ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST}, fetch = FetchType.LAZY)
    @JoinTable(name = "users_projects", schema = "abcd", joinColumns = {@JoinColumn(name = "_projectId")},
            inverseJoinColumns = {@JoinColumn(name = "_userId")})
    private Set<User> usersSet;

}

@Transactional
public void saveProject(ProjectDTO projectDTO, Set<UserDTO> userDTOSet) {
    Optional<Project> optionalProject = getProjectFromDB(projectDTO.getProjectId());
    List<User> existUsers = null;

    if (!optionalProject.isPresent()) {

        Map<String, UserDTO> userDTOMap = userDTOSet.stream().collect(Collectors.toMap(UserDTO::getUserId, userDTO -> userDTO));
        if (MapUtils.isNotEmpty(userDTOMap)) {
            existingUsers = getUsersFromDB(userDTOMap.keySet());
        }
        Project project = new Project();
        try {
            BeanUtils.copyProperties(project, projectDTO)
            updateExistingUsers(project, userDTOMap, existingUsers);
            addNewUsers(project, userDTOMap);
        } catch (IllegalAccessException e) {
            throw new BeanCopyException("Exception raised while copying the data", e);
        } catch (InvocationTargetException e) {
            throw new BeanCopyException("Exception raised while copying the data", e);
        }

        try {
            ProjectRepository.save(user);
        } catch (Exception e) {

            throw new CustomException("Exception raised while saving the new Project", e);
        }

    }


    private void updateExistingUsers (Project
    newProject, Map < String, UserDTO > newUsers, List < User > existingUsers){
        if (CollectionUtils.isNotEmpty(existingUsers)) {
            existingUsers.stream().forEach(existinguser -> {
                newProject.addNewUser(existinguser);
                newUsers.remove(existinguser.getUserId());
            });
        }
    }


    private void addNewUsers(Project newProject, Map < String, UserDTO > userDTOMap){
        userDTOMap.values().stream().forEach(userDTO -> {
            User user = convertToModel(userDTO);
            newProject.addNewUser(newUser);
        });
    }

Answer 1

您可以尝试使用@SQLInsert为User使用自定义的“upsert”语句，因为我已经回答了几次了。请参见此处使用attachDirty（saveOrUpdate）Hibernate事务和并发