I have an Entity Task and it has ManyToMany relationship with itself.
Task
@Entity
@Table(name = "tasks")
public class Task {
@Id
@Type(type = "pg-uuid")
private UUID id;
@Column(nullable = false)
private String title = "";
@ManyToMany
@JoinTable(name = "task_dependencies",
joinColumns = {@JoinColumn(name = "task_id")},
inverseJoinColumns = {@JoinColumn(name = "dependency_id")})
private Set<Task> dependencies = new HashSet<>();
}
Task Table
CREATE TABLE tasks (
id uuid PRIMARY KEY ,
title text NOT NULL);
Task Dependencies
CREATE TABLE task_dependencies (
task_id uuid NOT NULL,
dependency_id uuid NOT NULL,
CONSTRAINT task_dependencies_pk PRIMARY KEY(task_id, dependency_id));
When I updating Task dependencies sometimes it throws exception org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "task_dependencies_pk"
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "task_dependencies_pk"
issues.1.vwgmg5dpabmo@docker-master | Detail: Key (task_id, dependency_id)=(1ccf9dc6-77f5-4dc7-91c7-d0c23e364086, a77560c1-5979-4e46-8399-0b2338475ced) already exists.
issues.1.vwgmg5dpabmo@docker-master | at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477) ~[postgresql-42.1.4.jar!/:42.1.4]
issues.1.vwgmg5dpabmo@docker-master | at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190) ~[postgresql-42.1.4.jar!/:42.1.4]
issues.1.vwgmg5dpabmo@docker-master | at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300) ~[postgresql-42.1.4.jar!/:42.1.4]
issues.1.vwgmg5dpabmo@docker-master | at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428) ~[postgresql-42.1.4.jar!/:42.1.4]
issues.1.vwgmg5dpabmo@docker-master | at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354) ~[postgresql-42.1.4.jar!/:42.1.4]
issues.1.vwgmg5dpabmo@docker-master | at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169) ~[postgresql-42.1.4.jar!/:42.1.4]
issues.1.vwgmg5dpabmo@docker-master | at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136) ~[postgresql-42.1.4.jar!/:42.1.4]
issues.1.vwgmg5dpabmo@docker-master | at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-2.7.3.jar!/:na]
issues.1.vwgmg5dpabmo@docker-master | at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.3.jar!/:na]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.persister.collection.AbstractCollectionPersister.insertRows(AbstractCollectionPersister.java:1567) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.action.internal.CollectionUpdateAction.execute(CollectionUpdateAction.java:85) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:589) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1437) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:493) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3207) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2413) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:156) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:68) ~[hibernate-core-5.2.12.Final.jar!/:5.2.12.Final]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536) ~[spring-orm-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:742) ~[spring-tx-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:710) ~[spring-tx-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:532) ~[spring-tx-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304) ~[spring-tx-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.0.2.BUILD-SNAPSHOT.jar!/:5.0.2.BUILD-SNAPSHOT]
issues.1.vwgmg5dpabmo@docker-master | at com.docutools.issues.service.TaskService$$EnhancerBySpringCGLIB$$d898669c.addDependencyToTask(<generated>) ~[classes!/:1.0.0-RELEASE]
issues.1.vwgmg5dpabmo@docker-master | at com.docutools.issues.api.v2.controllers.TasksApi.addDependencyToTask(TasksApi.java:170) ~[classes!/:1.0.0-RELEASE]
issues.1.vwgmg5dpabmo@docker-master | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151]
issues.1.vwgmg5dpabmo@docker-master | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_151]
issues.1.vwgmg5dpabmo@docker-master | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_151]
issues.1.vwgmg5dpabmo@docker-master | at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_151]
Update Method
public TaskDTO updateDependencies(TaskDTO updateTask, UUID taskId) {
Task task = taskRepository.findById(taskId).orElseThrow(() ->
new EntityNotFoundException("Task", taskId));
if (updateTask.getDependencyIds() != null) {
List<Task> dependencies = taskRepository.findAllById(updateTask.getDependencyIds().stream().filter(Objects::nonNull).collect(Collectors.toList()));
task.getDependencies().addAll(dependencies);
taskRepository.save(task);
}
return new TaskDTO(task);
}
There is also one more method that removes the dependencies. May be both occur same time then this exception occurs. I don't have any clue.
When try to add same dependency same time twice it occurs. For instance, client one try to add dependency A and also client two same time same dependency. Both found A not in dependency set while fetching Task record form JPA Repository and add it to set and try to save. Who attempt first able to save and second got failed with duplicate key.
is there any way to avoid this situation in multiple instance environment ?