JPA and multithread merge generate exception id already exists

Question

I have an entity where the id field is describe as such :

@Id
@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = true)
@SequenceGenerator(name = "records_id_seq", sequenceName = "records_id_seq", schema = "recorder", initialValue = 1, allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "records_id_seq")
private Long id;

And it seems that when multiple thread tries to insert new data, there is a problem as my application wants to insert 2 entities with the same id value, and my database refuse both of them as the id is unique.

What should be done to provents this ?

Is where a way to tell my entity to not try to insert the id value and let the database generate it on its own during insertion phase ?

Thanks for your help.

Edit : exception :

11:17:47.407 ERROR - Uncatched exception at 201507091117 for thread Thread[RecorderBase-Pool-Thread-1,5,RMI Runtime] : 
javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20140809-296a69f): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pkey_records_id"
  Détail : Key (id)=(280) already exists.
Error Code: 0
Call: INSERT INTO recorder.records (id, name, number, DELETED, frid, frname, end, start, hid, REFERENCE, toid, toname) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [12 parameters bound]
Query: InsertObjectQuery(Record[280/20014364334653801])
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:159) ~[ModuleA-2.1.0.jar:na]
    at DBHelper.save(DBHelper.java:215) ~[ModuleA-2.1.0.jar:na]
    at DBHelper.save(DBHelper.java:230) ~[ModuleA-2.1.0.jar:na]
    at RecordEngine$1.run(RecordEngine.java:253) ~[ModuleA-2.1.0.jar:na]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[na:1.8.0_40]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[na:1.8.0_40]
    at java.lang.Thread.run(Unknown Source) ~[na:1.8.0_40]
Caused by: org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pkey_records_id"
  Détail : Key (id)=(280) already exists.
Error Code: 0
Call: INSERT INTO recorder.records (id, name, number, DELETED, frid, frname, end, start, hid, REFERENCE, toid, toname) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [12 parameters bound]
Query: InsertObjectQuery(Record[280/20014364334653801])
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1611) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:962) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:631) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2000) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:298) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:377) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:165) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:489) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:301) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1802) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1784) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1735) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.CommitManager.commitChangedObjectsForClassWithChangeSet(CommitManager.java:273) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:193) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:139) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4205) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1441) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:278) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1169) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:134) ~[ModuleA-2.1.0.jar:na]
    ... 6 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pkey_records_id"
  Détail : Key (id)=(280) already exists.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198) ~[ModuleA-2.1.0.jar:na]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927) ~[ModuleA-2.1.0.jar:na]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[ModuleA-2.1.0.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561) ~[ModuleA-2.1.0.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419) ~[ModuleA-2.1.0.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:365) ~[ModuleA-2.1.0.jar:na]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:890) ~[ModuleA-2.1.0.jar:na]
    ... 38 common frames omitted

It shouldn't happen: sequences are thead-safe :http://stackoverflow.com/questions/14215151/postgresql-is-using-select-nextval-generator-thread-safe-in-harsh-multiuser-en The problem may be somewhere else — pdem, Jul 09 '15 at 11:31
What about this: `@Id @GeneratedValue(strategy=GenerationType.AUTO, generator="records_id_seq") @SequenceGenerator(name="records_id_seq", sequenceName="MY_ENTITY_SEQ") private Long id;` — Abder KRIMA, Jul 09 '15 at 11:35
Why would your application try to insert two objects with the same id at the same time? Are they the same object, or is only the ID the same? How are you getting to the point that the ID is the same? Like pdem says, I think that you might have a flaw in the logic of your program, which leads to the problem (not necessarily being multithreading) — Mackiavelli, Jul 09 '15 at 12:22
It sounds to me like multiple threads must be accessing the *same objects*, so you try to insert the same object from multiple threads. That won't work. — Craig Ringer, Jul 09 '15 at 12:32
In multiple thread I am creating a new Record entity instance, the constructor don't do anything, and the id is null, then I am merging the entity (it doesn't exists in the database before that). I am using the same entityManager for every threads can it be the problem ? I am using a transation for each merge. — iXô, Jul 09 '15 at 12:37
Which way you are creating your SEQUENCE object in the database? Let the persistence provider do the job or doing it by yourself? — Lars, Jul 09 '15 at 13:23
Btw you should use persist() over merge() for new entities because it involves less overhead — Lars, Jul 09 '15 at 13:26
My sequence is automaticaly created with the use of the bigserial type in postgresql. And the result is : CREATE SEQUENCE recorder.records_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; — iXô, Jul 09 '15 at 14:08

score 0 · Accepted Answer · answered Jul 15 '15 at 19:25

Ok, I've found the problem.

I even tried to change the table schema, deleting auto generated column, so no sequence, but still primary key with application generated value. But still exceptions.

And the problem was easy to fix : use a new entity manager for each thread.

And there ! No insertion exception anymore ;)

Thanks for all.

JPA and multithread merge generate exception id already exists

1 Answers1