Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » @TableGenerator generates duplicate primary keys when used concurrently
@TableGenerator generates duplicate primary keys when used concurrently [message #550485] Fri, 30 July 2010 14:08 Go to next message
Christoph Grothaus is currently offline Christoph GrothausFriend
Messages: 12
Registered: July 2010
Location: Germany
Junior Member
Hi @all.

We are facing the problem that @TableGenerator generates duplicate primary keys when used concurrently.

We are using EclipseLink 2.0.0.v20091127-r5931-2.0. We are using JPA in Java SE mode via Persistence.createEntityManagerFactory(String persistenceUnitName, Map jpaConf). Each time we want to persist one of our objects, we use a fresh EntityManager acquired via emf.createEntityManager().

If we try to persist some objects concurrently, the TableGenerator generates duplicate primary keys, so that we get a database error. This happens on Microsoft SQL Server 2008, on HSQL DB, and on Oracle. Notably, we need more concurrent threads (10) to provoke the error on Oracle, while on MS SQL, 2 threads suffice.

What can we do? Is there a way to make primary key generation concurrency safe?

Update: this happens also with EclipseLink 2.1.0.v20100614-r7608.

PS: The code looks like this:

EntityManager usage:
EntityManager em = jpaConnector.createEntityManager(persistenceUnit);
try {
	em.getTransaction().begin();
	em.merge(invoice)
	em.getTransaction().commit();
} finally {
	if (em.getTransaction().isActive()) {
		em.getTransaction().rollback();
	}
	em.close();
}


The @TableGenerator configuration:
@Id
@GeneratedValue(strategy = GenerationType.TABLE, generator = "INVOICE")
@TableGenerator(name = "INVOICE", allocationSize = 1, table = Constants.SEQUENCE_TABLE, schema = Constants.DATABASE_SCHEMA)
@Column(length = 40)
private String id;

[Updated on: Fri, 30 July 2010 14:18]

Report message to a moderator

Re: @TableGenerator generates duplicate primary keys when used concurrently [message #550909 is a reply to message #550485] Wed, 04 August 2010 15:42 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The table generator will never generate duplicate ids.

I can't see how this could occur, please include an EclipseLink log on finest that shows a concurrency issue.

Check that you have your transactions and data source configured correctly.


James : Wiki : Book : Blog : Twitter
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #551114 is a reply to message #550909] Thu, 05 August 2010 09:57 Go to previous messageGo to next message
Christoph Grothaus is currently offline Christoph GrothausFriend
Messages: 12
Registered: July 2010
Location: Germany
Junior Member
Hello James,

now I know what produced the error. As you can see in my original post, the allocationSize is set to 1 in the @TableGenerator annotation. If I set the allocationSize to any value greater than 1, then EclipseLink does not generate duplicate ids.

So, this is not an important issue for me anymore. But I think it is still an issue. With allocationSize = 1, then EclipseLink definitely generates duplicate ids. I have put together a very simple Eclipse project that demonstrates this behaviour. You can download it from http://www.file-upload.net/download-2724393/eclipselink-tabl egenerator-testcase.zip.html.

It already contains the necessary dependencies. Just build it within Eclipse and run the unit test. I have put the logging output at the end of this post. Some notes: I have set up the project to be close to our real setting, so I included Apache commons-dbcp. However, the error also occurs without it. Furthermore, I have set it up to use HSQL DB, but the error occurs also on Microsoft SQL Server and on Oracle.

I would appreciate if you could have a look at it. Should I file a bug in bugzilla?

Regards,
Christoph Grothaus

PS: the different behaviour between allocationSize == 1 and allocationSize > 1 seems to come from the class org.eclipse.persistence.internal.sequencing.SequencingManage r, or to be more precise, from the inner classes Preallocation_Transaction_NoAccessor_State, Preallocation_Transaction_Accessor_State and Preallocation_NoTransaction_State. There you find the following if clause:
if(sequence.getPreallocationSize() > 1) { ...} else {...}
. So the error must be in the else clause.

PPS: here is the logging output:
[EL Finest]: 2010-08-05 11:35:44.958--ServerSession(20358204)--Thread(Thread[main,5,main])--Begin predeploying Persistence Unit com.example; session file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/_com.example_jtaDataSource=7056873_nonJtaDataSource=7056873; state Initial; factoryCount 0
[EL Finest]: 2010-08-05 11:35:45.0--ServerSession(20358204)--Thread(Thread[main,5,main])--property=eclipselink.orm.throw.exceptions; default value=true
[EL Finest]: 2010-08-05 11:35:45.029--ServerSession(20358204)--Thread(Thread[main,5,main])--property=eclipselink.jpa.uppercase-column-names; default value=false
[EL Finer]: 2010-08-05 11:35:45.03--ServerSession(20358204)--Thread(Thread[main,5,main])--Searching for default mapping file in file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/
[EL Finer]: 2010-08-05 11:35:45.033--ServerSession(20358204)--Thread(Thread[main,5,main])--Searching for default mapping file in file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/
[EL Config]: 2010-08-05 11:35:45.154--ServerSession(20358204)--Thread(Thread[main,5,main])--The access type for the persistent class [class com.example.Foo] is set to [FIELD].
[EL Config]: 2010-08-05 11:35:45.185--ServerSession(20358204)--Thread(Thread[main,5,main])--The alias name for the entity class [class com.example.Foo] is being defaulted to: Foo.
[EL Config]: 2010-08-05 11:35:45.186--ServerSession(20358204)--Thread(Thread[main,5,main])--The table name for entity [class com.example.Foo] is being defaulted to: FOO.
[EL Config]: 2010-08-05 11:35:45.203--ServerSession(20358204)--Thread(Thread[main,5,main])--The column name for element [field randomInt] is being defaulted to: RANDOMINT.
[EL Config]: 2010-08-05 11:35:45.205--ServerSession(20358204)--Thread(Thread[main,5,main])--The column name for element [field id] is being defaulted to: ID.
[EL Finest]: 2010-08-05 11:35:45.208--ServerSession(20358204)--Thread(Thread[main,5,main])--End predeploying Persistence Unit com.example; session file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/_com.example_jtaDataSource=7056873_nonJtaDataSource=7056873; state Predeployed; factoryCount 0
[EL Finer]: 2010-08-05 11:35:45.208--Thread(Thread[main,5,main])--JavaSECMPInitializer - transformer is null.
[EL Finest]: 2010-08-05 11:35:45.209--ServerSession(20358204)--Thread(Thread[main,5,main])--Begin predeploying Persistence Unit com.example; session file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/_com.example_jtaDataSource=7056873_nonJtaDataSource=7056873; state Predeployed; factoryCount 0
[EL Finest]: 2010-08-05 11:35:45.209--ServerSession(20358204)--Thread(Thread[main,5,main])--End predeploying Persistence Unit com.example; session file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/_com.example_jtaDataSource=7056873_nonJtaDataSource=7056873; state Predeployed; factoryCount 1
2010-08-05 11:35:45.212 - ConcurrentTest - Starting concurrent test with 2 threads
2010-08-05 11:35:45.214 - ConcurrentTest - Waiting for all threads
2010-08-05 11:35:45.214 - ConcurrentTest - This is thread 1, now saving 3 foos
2010-08-05 11:35:45.214 - ConcurrentTest - This is thread 2, now saving 3 foos
[EL Finest]: 2010-08-05 11:35:45.214--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--Begin deploying Persistence Unit com.example; session file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/_com.example_jtaDataSource=7056873_nonJtaDataSource=7056873; state Predeployed; factoryCount 1
[EL Finer]: 2010-08-05 11:35:45.226--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--Could not initialize Validation Factory. Encountered following exception: java.lang.NoClassDefFoundError: javax/validation/Validation
[EL Finest]: 2010-08-05 11:35:45.232--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--property=eclipselink.logging.level; value=ALL
[EL Finest]: 2010-08-05 11:35:45.232--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--property=eclipselink.logging.level; value=ALL
[EL Finest]: 2010-08-05 11:35:45.232--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--property=javax.persistence.jtaDataSource; value=org.apache.commons.dbcp.BasicDataSource@6bade9
[EL Finest]: 2010-08-05 11:35:45.232--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--property=javax.persistence.nonJtaDataSource; value=org.apache.commons.dbcp.BasicDataSource@6bade9
[EL Warning]: 2010-08-05 11:35:45.232--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--PersistenceUnitInfo com.example has transactionType RESOURCE_LOCAL and therefore jtaDataSource will be ignored
[EL Info]: 2010-08-05 11:35:45.234--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.1.0.v20100614-r7608
[EL Finest]: 2010-08-05 11:35:45.413--Thread(Thread[ConcurrentTestThread-1,5,main])--DBPlatform: org.eclipse.persistence.platform.database.HSQLPlatform, RegularExpression: (?i)hsql.*.
[EL Fine]: 2010-08-05 11:35:45.413--Thread(Thread[ConcurrentTestThread-1,5,main])--Detected Vendor platform: org.eclipse.persistence.platform.database.HSQLPlatform
[EL Config]: 2010-08-05 11:35:45.426--ServerSession(20358204)--Connection(18623335)--Thread(Thread[ConcurrentTestThread-1,5,main])--connecting(DatabaseLogin(
	platform=>HSQLPlatform
	user name=> ""
	connector=>JNDIConnector datasource name=>null
))
[EL Config]: 2010-08-05 11:35:45.427--ServerSession(20358204)--Connection(6659511)--Thread(Thread[ConcurrentTestThread-1,5,main])--Connected: jdbc:hsqldb:mem:testdb
	User: SA
	Database: HSQL Database Engine  Version: 1.8.1
	Driver: HSQL Database Engine Driver  Version: 1.8.1
[EL Config]: 2010-08-05 11:35:45.427--ServerSession(20358204)--Connection(22375698)--Thread(Thread[ConcurrentTestThread-1,5,main])--connecting(DatabaseLogin(
	platform=>HSQLPlatform
	user name=> ""
	connector=>JNDIConnector datasource name=>null
))
[EL Config]: 2010-08-05 11:35:45.427--ServerSession(20358204)--Connection(23848591)--Thread(Thread[ConcurrentTestThread-1,5,main])--Connected: jdbc:hsqldb:mem:testdb
	User: SA
	Database: HSQL Database Engine  Version: 1.8.1
	Driver: HSQL Database Engine Driver  Version: 1.8.1
[EL Finest]: 2010-08-05 11:35:45.461--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--sequencing connected, state is Preallocation_Transaction_NoAccessor_State
[EL Finest]: 2010-08-05 11:35:45.461--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--sequence Foo: preallocation size 1
[EL Info]: 2010-08-05 11:35:45.509--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/_com.example_jtaDataSource=7056873_nonJtaDataSource=7056873 login successful
[EL Finest]: 2010-08-05 11:35:45.527--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--Execute query DataModifyQuery(sql="CREATE TABLE FOO (ID BIGINT NOT NULL, RANDOMINT INTEGER, PRIMARY KEY (ID))")
[EL Finest]: 2010-08-05 11:35:45.528--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--reconnecting to external connection pool
[EL Fine]: 2010-08-05 11:35:45.528--ServerSession(20358204)--Connection(5863106)--Thread(Thread[ConcurrentTestThread-1,5,main])--CREATE TABLE FOO (ID BIGINT NOT NULL, RANDOMINT INTEGER, PRIMARY KEY (ID))
[EL Finest]: 2010-08-05 11:35:45.538--Thread(Thread[ConcurrentTestThread-1,5,main])--The table (FOO) is created.
[EL Finest]: 2010-08-05 11:35:45.538--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--Execute query DataModifyQuery(sql="CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT NUMERIC(38), PRIMARY KEY (SEQ_NAME))")
[EL Finest]: 2010-08-05 11:35:45.539--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--reconnecting to external connection pool
[EL Fine]: 2010-08-05 11:35:45.539--ServerSession(20358204)--Connection(19589694)--Thread(Thread[ConcurrentTestThread-1,5,main])--CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT NUMERIC(38), PRIMARY KEY (SEQ_NAME))
[EL Finest]: 2010-08-05 11:35:45.539--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--Execute query DataReadQuery(sql="SELECT * FROM SEQUENCE WHERE SEQ_NAME = 'Foo'")
[EL Finest]: 2010-08-05 11:35:45.539--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--reconnecting to external connection pool
[EL Fine]: 2010-08-05 11:35:45.54--ServerSession(20358204)--Connection(7912507)--Thread(Thread[ConcurrentTestThread-1,5,main])--SELECT * FROM SEQUENCE WHERE SEQ_NAME = Foo
[EL Finest]: 2010-08-05 11:35:45.548--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--Execute query DataModifyQuery(sql="INSERT IGNORE INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values ('Foo', 0)")
[EL Finest]: 2010-08-05 11:35:45.548--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--reconnecting to external connection pool
[EL Fine]: 2010-08-05 11:35:45.548--ServerSession(20358204)--Connection(25567987)--Thread(Thread[ConcurrentTestThread-1,5,main])--INSERT IGNORE INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values (Foo, 0)
[EL Finer]: 2010-08-05 11:35:45.567--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--Canonical Metamodel class [com.example.Foo_] not found during initialization.
[EL Finest]: 2010-08-05 11:35:45.567--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--End deploying Persistence Unit com.example; session file:/data/work/eclipse-workspaces/00MainEclipseWorkspace/eclipselink-tablegenerator-testcase/target/classes/_com.example_jtaDataSource=7056873_nonJtaDataSource=7056873; state Deployed; factoryCount 1
[EL Finer]: 2010-08-05 11:35:45.598--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-2,5,main])--client acquired
[EL Finer]: 2010-08-05 11:35:45.598--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-1,5,main])--client acquired
[EL Finest]: 2010-08-05 11:35:45.604--UnitOfWork(18885993)--Thread(Thread[ConcurrentTestThread-1,5,main])--Merge clone with references com.example.Foo@1855562
[EL Finest]: 2010-08-05 11:35:45.604--UnitOfWork(30685694)--Thread(Thread[ConcurrentTestThread-2,5,main])--Merge clone with references com.example.Foo@2b7db1
[EL Finer]: 2010-08-05 11:35:45.605--ClientSession(25068634)--Connection(19097823)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin transaction
[EL Finest]: 2010-08-05 11:35:45.606--ClientSession(25068634)--Thread(Thread[ConcurrentTestThread-2,5,main])--reconnecting to external connection pool
[EL Finer]: 2010-08-05 11:35:45.605--ClientSession(28970806)--Connection(3975755)--Thread(Thread[ConcurrentTestThread-1,5,main])--begin transaction
[EL Finest]: 2010-08-05 11:35:45.606--ClientSession(28970806)--Thread(Thread[ConcurrentTestThread-1,5,main])--reconnecting to external connection pool
[EL Finest]: 2010-08-05 11:35:45.606--ClientSession(25068634)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query DataModifyQuery(sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + #PREALLOC_SIZE WHERE SEQ_NAME = #SEQ_NAME")
[EL Finest]: 2010-08-05 11:35:45.606--ClientSession(28970806)--Thread(Thread[ConcurrentTestThread-1,5,main])--Execute query DataModifyQuery(sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + #PREALLOC_SIZE WHERE SEQ_NAME = #SEQ_NAME")
[EL Fine]: 2010-08-05 11:35:45.607--ClientSession(28970806)--Connection(7031149)--Thread(Thread[ConcurrentTestThread-1,5,main])--UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
	bind => [1, Foo]
[EL Fine]: 2010-08-05 11:35:45.607--ClientSession(25068634)--Connection(27366488)--Thread(Thread[ConcurrentTestThread-2,5,main])--UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
	bind => [1, Foo]
[EL Finest]: 2010-08-05 11:35:45.608--ClientSession(28970806)--Thread(Thread[ConcurrentTestThread-1,5,main])--Execute query ValueReadQuery(sql="SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = #SEQ_NAME")
[EL Finest]: 2010-08-05 11:35:45.608--ClientSession(25068634)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query ValueReadQuery(sql="SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?")
[EL Fine]: 2010-08-05 11:35:45.608--ClientSession(28970806)--Connection(7031149)--Thread(Thread[ConcurrentTestThread-1,5,main])--SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?
	bind => [Foo]
[EL Fine]: 2010-08-05 11:35:45.608--ClientSession(25068634)--Connection(27366488)--Thread(Thread[ConcurrentTestThread-2,5,main])--SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?
	bind => [Foo]
[EL Finer]: 2010-08-05 11:35:45.609--ClientSession(25068634)--Connection(27366488)--Thread(Thread[ConcurrentTestThread-2,5,main])--commit transaction
[EL Finer]: 2010-08-05 11:35:45.609--ClientSession(28970806)--Connection(7031149)--Thread(Thread[ConcurrentTestThread-1,5,main])--commit transaction
[EL Finest]: 2010-08-05 11:35:45.609--UnitOfWork(30685694)--Thread(Thread[ConcurrentTestThread-2,5,main])--assign sequence to the object (2 -> com.example.Foo@1c5466b)
[EL Finest]: 2010-08-05 11:35:45.609--UnitOfWork(18885993)--Thread(Thread[ConcurrentTestThread-1,5,main])--assign sequence to the object (2 -> com.example.Foo@922804)
[EL Finer]: 2010-08-05 11:35:45.611--UnitOfWork(18885993)--Thread(Thread[ConcurrentTestThread-1,5,main])--begin unit of work commit
[EL Finer]: 2010-08-05 11:35:45.611--UnitOfWork(30685694)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin unit of work commit
[EL Finer]: 2010-08-05 11:35:45.618--ClientSession(25068634)--Connection(27660658)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin transaction
[EL Finest]: 2010-08-05 11:35:45.618--ClientSession(25068634)--Thread(Thread[ConcurrentTestThread-2,5,main])--reconnecting to external connection pool
[EL Finer]: 2010-08-05 11:35:45.619--ClientSession(28970806)--Connection(9870377)--Thread(Thread[ConcurrentTestThread-1,5,main])--begin transaction
[EL Finest]: 2010-08-05 11:35:45.619--ClientSession(28970806)--Thread(Thread[ConcurrentTestThread-1,5,main])--reconnecting to external connection pool
[EL Finest]: 2010-08-05 11:35:45.619--UnitOfWork(30685694)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query InsertObjectQuery(com.example.Foo@1c5466b)
[EL Fine]: 2010-08-05 11:35:45.622--ClientSession(25068634)--Connection(7263010)--Thread(Thread[ConcurrentTestThread-2,5,main])--INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)
	bind => [2, 324264678]
[EL Finest]: 2010-08-05 11:35:45.624--UnitOfWork(18885993)--Thread(Thread[ConcurrentTestThread-1,5,main])--Execute query InsertObjectQuery(com.example.Foo@922804)
[EL Fine]: 2010-08-05 11:35:45.624--ClientSession(28970806)--Connection(12518719)--Thread(Thread[ConcurrentTestThread-1,5,main])--INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)
	bind => [2, -772712444]
[EL Fine]: 2010-08-05 11:35:45.625--ClientSession(28970806)--Thread(Thread[ConcurrentTestThread-1,5,main])--CALL 1
[EL Warning]: 2010-08-05 11:35:45.628--UnitOfWork(18885993)--Thread(Thread[ConcurrentTestThread-1,5,main])--Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100614-r7608): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Violation of unique constraint SYS_PK_47: duplicate value(s) for column(s) ID in statement [INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)]
Error Code: -104
Call: INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)
	bind => [2, -772712444]
Query: InsertObjectQuery(com.example.Foo@922804)
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:797)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:863)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:583)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:980)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:206)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:192)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:341)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:465)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:290)
	at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:736)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:643)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2909)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1291)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1273)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1233)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:224)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:123)
	at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3348)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1422)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:546)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1527)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:200)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1148)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)
	at com.example.ConcurrentInsertTest.saveFoo(ConcurrentInsertTest.java:112)
	at com.example.ConcurrentInsertTest.access$1(ConcurrentInsertTest.java:107)
	at com.example.ConcurrentInsertTest$1.run(ConcurrentInsertTest.java:96)
	at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Violation of unique constraint SYS_PK_47: duplicate value(s) for column(s) ID in statement [INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:788)
	... 36 more

[EL Finer]: 2010-08-05 11:35:45.629--ClientSession(25068634)--Connection(7263010)--Thread(Thread[ConcurrentTestThread-2,5,main])--commit transaction
[EL Finer]: 2010-08-05 11:35:45.631--ClientSession(28970806)--Connection(12518719)--Thread(Thread[ConcurrentTestThread-1,5,main])--rollback transaction
[EL Finer]: 2010-08-05 11:35:45.631--UnitOfWork(18885993)--Thread(Thread[ConcurrentTestThread-1,5,main])--release unit of work
[EL Finer]: 2010-08-05 11:35:45.631--ClientSession(28970806)--Thread(Thread[ConcurrentTestThread-1,5,main])--client released
[EL Finer]: 2010-08-05 11:35:45.633--UnitOfWork(30685694)--Thread(Thread[ConcurrentTestThread-2,5,main])--end unit of work commit
[EL Finer]: 2010-08-05 11:35:45.633--UnitOfWork(30685694)--Thread(Thread[ConcurrentTestThread-2,5,main])--resume unit of work
Exception in thread "ConcurrentTestThread-1" [EL Finer]: 2010-08-05 11:35:45.633--UnitOfWork(30685694)--Thread(Thread[ConcurrentTestThread-2,5,main])--release unit of work
[EL Finer]: 2010-08-05 11:35:45.634--ClientSession(25068634)--Thread(Thread[ConcurrentTestThread-2,5,main])--client released
[EL Finer]: 2010-08-05 11:35:45.634--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-2,5,main])--client acquired
[EL Finest]: 2010-08-05 11:35:45.634--UnitOfWork(33459432)--Thread(Thread[ConcurrentTestThread-2,5,main])--Merge clone with references com.example.Foo@d4d66b
[EL Finer]: 2010-08-05 11:35:45.634--ClientSession(30463067)--Connection(19255406)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin transaction
javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100614-r7608): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Violation of unique constraint SYS_PK_47: duplicate value(s) for column(s) ID in statement [INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)]
Error Code: -104
Call: INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)
	bind => [2, -772712444]
Query: InsertObjectQuery(com.example.Foo@922804)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:102)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)
	at com.example.ConcurrentInsertTest.saveFoo(ConcurrentInsertTest.java:112)
	at com.example.ConcurrentInsertTest.access$1(ConcurrentInsertTest.java:107)
	at com.example.ConcurrentInsertTest$1.run(ConcurrentInsertTest.java:96)
	at java.lang.Thread.run(Thread.java:595)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100614-r7608): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Violation of unique constraint SYS_PK_47: duplicate value(s) for column(s) ID in statement [INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)]
Error Code: -104
Call: INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)
	bind => [2, -772712444]
Query: InsertObjectQuery(com.example.Foo@922804)
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:797)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:863)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:583)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:980)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:206)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:192)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:341)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:465)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
	at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
	at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:290)
	at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:736)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:643)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2909)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1291)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1273)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1233)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:224)
	at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:123)
	at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3348)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1422)
[EL Finest]: 2010-08-05 11:35:45.634--ClientSession(30463067)--Thread(Thread[ConcurrentTestThread-2,5,main])--reconnecting to external connection pool
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:546)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1527)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:200)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1148)
[EL Finest]: 2010-08-05 11:35:45.635--ClientSession(30463067)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query DataModifyQuery(sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
	... 5 more
Caused by: java.sql.SQLException: Violation of unique constraint SYS_PK_47: duplicate value(s) for column(s) ID in statement [INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)[EL Fine]: 2010-08-05 11:35:45.635--ClientSession(30463067)--Connection(26670261)--Thread(Thread[ConcurrentTestThread-2,5,main])--UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
	bind => [1, Foo]

	at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:788)
	... 36 more
[EL Finest]: 2010-08-05 11:35:45.635--ClientSession(30463067)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query ValueReadQuery(sql="SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?")
[EL Fine]: 2010-08-05 11:35:45.64--ClientSession(30463067)--Connection(26670261)--Thread(Thread[ConcurrentTestThread-2,5,main])--SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?
	bind => [Foo]
[EL Finer]: 2010-08-05 11:35:45.641--ClientSession(30463067)--Connection(26670261)--Thread(Thread[ConcurrentTestThread-2,5,main])--commit transaction
[EL Finest]: 2010-08-05 11:35:45.641--UnitOfWork(33459432)--Thread(Thread[ConcurrentTestThread-2,5,main])--assign sequence to the object (3 -> com.example.Foo@149105b)
[EL Finer]: 2010-08-05 11:35:45.641--UnitOfWork(33459432)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin unit of work commit
[EL Finer]: 2010-08-05 11:35:45.641--ClientSession(30463067)--Connection(18724539)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin transaction
[EL Finest]: 2010-08-05 11:35:45.642--ClientSession(30463067)--Thread(Thread[ConcurrentTestThread-2,5,main])--reconnecting to external connection pool
[EL Finest]: 2010-08-05 11:35:45.642--UnitOfWork(33459432)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query InsertObjectQuery(com.example.Foo@149105b)
[EL Fine]: 2010-08-05 11:35:45.642--ClientSession(30463067)--Connection(29876954)--Thread(Thread[ConcurrentTestThread-2,5,main])--INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)
	bind => [3, -905850127]
[EL Finer]: 2010-08-05 11:35:45.642--ClientSession(30463067)--Connection(29876954)--Thread(Thread[ConcurrentTestThread-2,5,main])--commit transaction
[EL Finer]: 2010-08-05 11:35:45.642--UnitOfWork(33459432)--Thread(Thread[ConcurrentTestThread-2,5,main])--end unit of work commit
[EL Finer]: 2010-08-05 11:35:45.643--UnitOfWork(33459432)--Thread(Thread[ConcurrentTestThread-2,5,main])--resume unit of work
[EL Finer]: 2010-08-05 11:35:45.643--UnitOfWork(33459432)--Thread(Thread[ConcurrentTestThread-2,5,main])--release unit of work
[EL Finer]: 2010-08-05 11:35:45.643--ClientSession(30463067)--Thread(Thread[ConcurrentTestThread-2,5,main])--client released
[EL Finer]: 2010-08-05 11:35:45.643--ServerSession(20358204)--Thread(Thread[ConcurrentTestThread-2,5,main])--client acquired
[EL Finest]: 2010-08-05 11:35:45.643--UnitOfWork(33445663)--Thread(Thread[ConcurrentTestThread-2,5,main])--Merge clone with references com.example.Foo@109ea96
[EL Finer]: 2010-08-05 11:35:45.643--ClientSession(6326112)--Connection(22538826)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin transaction
[EL Finest]: 2010-08-05 11:35:45.643--ClientSession(6326112)--Thread(Thread[ConcurrentTestThread-2,5,main])--reconnecting to external connection pool
[EL Finest]: 2010-08-05 11:35:45.644--ClientSession(6326112)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query DataModifyQuery(sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
[EL Fine]: 2010-08-05 11:35:45.644--ClientSession(6326112)--Connection(14069849)--Thread(Thread[ConcurrentTestThread-2,5,main])--UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
	bind => [1, Foo]
[EL Finest]: 2010-08-05 11:35:45.644--ClientSession(6326112)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query ValueReadQuery(sql="SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?")
[EL Fine]: 2010-08-05 11:35:45.644--ClientSession(6326112)--Connection(14069849)--Thread(Thread[ConcurrentTestThread-2,5,main])--SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?
	bind => [Foo]
[EL Finer]: 2010-08-05 11:35:45.645--ClientSession(6326112)--Connection(14069849)--Thread(Thread[ConcurrentTestThread-2,5,main])--commit transaction
[EL Finest]: 2010-08-05 11:35:45.645--UnitOfWork(33445663)--Thread(Thread[ConcurrentTestThread-2,5,main])--assign sequence to the object (4 -> com.example.Foo@83b1b)
[EL Finer]: 2010-08-05 11:35:45.645--UnitOfWork(33445663)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin unit of work commit
[EL Finer]: 2010-08-05 11:35:45.645--ClientSession(6326112)--Connection(1708953)--Thread(Thread[ConcurrentTestThread-2,5,main])--begin transaction
[EL Finest]: 2010-08-05 11:35:45.645--ClientSession(6326112)--Thread(Thread[ConcurrentTestThread-2,5,main])--reconnecting to external connection pool
[EL Finest]: 2010-08-05 11:35:45.645--UnitOfWork(33445663)--Thread(Thread[ConcurrentTestThread-2,5,main])--Execute query InsertObjectQuery(com.example.Foo@83b1b)
[EL Fine]: 2010-08-05 11:35:45.646--ClientSession(6326112)--Connection(33341602)--Thread(Thread[ConcurrentTestThread-2,5,main])--INSERT IGNORE INTO FOO (ID, RANDOMINT) VALUES (?, ?)
	bind => [4, 1888662615]
[EL Finer]: 2010-08-05 11:35:45.646--ClientSession(6326112)--Connection(33341602)--Thread(Thread[ConcurrentTestThread-2,5,main])--commit transaction
[EL Finer]: 2010-08-05 11:35:45.646--UnitOfWork(33445663)--Thread(Thread[ConcurrentTestThread-2,5,main])--end unit of work commit
[EL Finer]: 2010-08-05 11:35:45.646--UnitOfWork(33445663)--Thread(Thread[ConcurrentTestThread-2,5,main])--resume unit of work
[EL Finer]: 2010-08-05 11:35:45.646--UnitOfWork(33445663)--Thread(Thread[ConcurrentTestThread-2,5,main])--release unit of work
[EL Finer]: 2010-08-05 11:35:45.647--ClientSession(6326112)--Thread(Thread[ConcurrentTestThread-2,5,main])--client released
2010-08-05 11:35:45.647 - ConcurrentTest - Concurrent test finished
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #551786 is a reply to message #550485] Mon, 09 August 2010 14:36 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

In the log you see that the sequence is first updated in two different transactions on two different connections. The update should cause a database lock for the duration of the transaction, but you log does not seem to indicate this. My guess is your database does not support multiple connections/transaction correctly.

You seem to be using HSQL, which is an in memory database and may not support transactions.

Can you try the issue on a real database?


James : Wiki : Book : Blog : Twitter
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #551810 is a reply to message #551786] Mon, 09 August 2010 16:00 Go to previous messageGo to next message
Christoph Grothaus is currently offline Christoph GrothausFriend
Messages: 12
Registered: July 2010
Location: Germany
Junior Member
Dear James,

thank you for pointing me to the difference of HSQL vs. real database. I know that the error occured on MS SQL and Oracle, too. But that was surely related to another mistake (setting "javax.persistence.transactionType" to "JTA" although being in a Java SE environment) that I corrected later. Obviousliy, I didn't check it again then.

So, to put it short, you were right and I was wrong: table generator does not generate duplicate ids.

Thank you for your help.

Regards,
Christoph
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #551815 is a reply to message #551786] Mon, 09 August 2010 16:13 Go to previous messageGo to next message
Christoph Grothaus is currently offline Christoph GrothausFriend
Messages: 12
Registered: July 2010
Location: Germany
Junior Member
PS: according to their website, HSQL does support transactions. However, on HSQL the error occurs with an allocationSize of 1. But isn't it odd that, once I change the allocationSize to a value of 2, the error goes away on HSQL (even with 500 or 1000 concurrent threads)?
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #552551 is a reply to message #550485] Thu, 12 August 2010 15:28 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I am pretty sure it is an HSQL transactional issue. Please try it again on Oracle or MS SQL to verify the issue does not occur.

The reason it may not occur with an allocation size is that EclipseLink will then be managing a pool of sequence numbers and is doing its own locking of this pool that may be avoiding any database issues.

It could be an issue with your transaction setting still, ensure you are not configuring JTA if not using a JTA DataSource.



James : Wiki : Book : Blog : Twitter
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #552664 is a reply to message #552551] Fri, 13 August 2010 06:06 Go to previous messageGo to next message
Christoph Grothaus is currently offline Christoph GrothausFriend
Messages: 12
Registered: July 2010
Location: Germany
Junior Member
Dear James,

I did already check against Oracle and MS SQL, and there the error does not occur (while re-reading my previous post from monday, I realize that i didn't state that clearly). So, everything is fine on real databases.

Regards,
Christoph
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #552821 is a reply to message #550485] Fri, 13 August 2010 16:10 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Hello Christoph ,

This points to it being a transaction problem with HSQL as James suggested.
When the sequence size is 1, EclipseLink will allow every thread to go to the database to get the sequence value directly. Because HSQL isn't handling transactions correctly, this is a problem, as multiple threads can query concurrently and get the same number.

When the sequence size is >1, EclipseLink has to lock its sequence object when it needs to go to the database for additional values, preventing other threads from obtaining numbers all at once. Other threads requesting a sequence number will then wait until the first thread returns a value. They will then use up the preallocated values until they run out and the process repeats. This preventing threads from concurrently accessing the database sequence value. Multiple sessions using the same sequence will still see this problem though.

Best Regards,
Chris
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #553021 is a reply to message #552821] Mon, 16 August 2010 06:24 Go to previous messageGo to next message
Christoph Grothaus is currently offline Christoph GrothausFriend
Messages: 12
Registered: July 2010
Location: Germany
Junior Member
Quote:
Multiple sessions using the same sequence will still see this problem though.

And multiple deployments on different JVMs accessing the same sequence will see the problem more than ever.

I knew before that the only safeguard is proper transactions on the database. It's a pity that HSQL is failing in this area, at least the in memory variant.

However, I am more and more coming to the conclusion that database sequences are inferior for generating synthetical (technical) primary keys. You have to access the sequence table first, increment it properly, and not before then, you can insert your real data. Not to speak of the problem that sequence table value and primary key value could run out of sync if not used correctly. So, I switched to using VARCHAR(40) primary key columns, populated with UUIDs.

Thanks to everybody for your help, and regards,
Christoph
Re: @TableGenerator generates duplicate primary keys when used concurrently [message #666526 is a reply to message #553021] Wed, 20 April 2011 17:19 Go to previous message
Olivier Chorier is currently offline Olivier ChorierFriend
Messages: 4
Registered: July 2009
Junior Member
Hi,

I'm using glassfish 3.0.1 and I've duplicate entry probles too, but qith mySQL this time. Here is a log.

Let's consider a table generator with a sequence number set to 1628, and the max(ID) for my table is : 1629.

When I try to create a new object, the entityManager.persist gives me 1629, and then when I flush, I have a PersistenceException with the duplicate Entry.

I don't really understand how I have corrputed my table generator, but I'd like to correct this behaviour when I detect it, and update the sequence number of my table generator. But when I try to execute a native query which increments the sequence number by 1, nothing is done on the database (I think it is a Lock problem).

So I'd like to handle those types of errors, but I don't have any idea on the best way to correct this.

Well, here is the log :

Quote:

ATTENTION: Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViol ationException: Duplicate entry '1629' for key 'PRIMARY'
Error Code: 1062
Call: INSERT IGNORE INTO db.employee (ID, NAME) VALUES (?, ?)
bind => [1629, "JOHN DOE"]
Query: InsertObjectQuery(path.entities.employee.MEmployee@b8d)
at org.eclipse.persistence.exceptions.DatabaseException.sqlExce ption(DatabaseException.java:324)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAcce ssor.executeDirectNoSelect(DatabaseAccessor.java:801)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAcce ssor.executeNoSelect(DatabaseAccessor.java:867)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAcce ssor.basicExecuteCall(DatabaseAccessor.java:587)
....
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush (EntityManagerImpl.java:696)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.performPre QueryFlush(EJBQueryImpl.java:1194)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeUpd ate(EJBQueryImpl.java:506)
at path.crud.QueryManager.executeUpdate(QueryManager.java:80)
at path.crud.CrudEntities.create(CrudEntities.java:52)
....
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViol ationException: Duplicate entry '1629' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Nativ e Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Native ConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(De legatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:5 13)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at ....

Previous Topic:Persisting new object with references to detached entities - fails
Next Topic:MySQL character encoding
Goto Forum:
  


Current Time: Mon Nov 24 16:36:06 GMT 2014

Powered by FUDForum. Page generated in 0.08288 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software