Using eclipselink in extended persistent context mode, and using single entity manager for memory reason.

The entity manager operations are synchronized to make sure that there is only one thread using the entity manager at a time.

Two threads running in parallel, one writing the records and the other executing sql query to get the data from the database using same entity manager.

The two threads are synchronized so once the writing operation is completed the query gets the executed. This could see from the eclipselink detailed log info that there is no overlapping in sql instructions.

The problem is when executing the sql query, the inerst sql instructions from previously executed statements get cached and executed for the second time and resulting in

Internal Exception: java.sql.BatchUpdateException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL100629211102660' defined on 'TEST'.
Error Code: 20000
Query: ReportQuery(referenceClass=Entry sql="SELECT COUNT(t0.ENTRY_ID) FROM ENTRY t0, PRTY t2, TEST t1 WHERE (((LCASE(t1.PATH) LIKE ?) AND (UCASE(t2.PRTY) LIKE ?)) AND ((t1.L_ID = t0.L_ID) AND (t2.PRTY = t0.TEST_ID)))")
Caused by: org.eclipse.persistence.exceptions.DatabaseException:
Internal Exception: java.sql.BatchUpdateException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL100629211102660' defined on

Can you post the full stack trace? Also, try turning on EclipseLink logging and post the log from the writing thread perform the same statement upto the read query error on the other thread. This might indicate a reason why it thinks it should reissue the statement. Are there any errors in the writing thread that might have been silently ignored?

If the write failed an exception is thrown and the transaction should be marked for rollback. I have seen problems when JTA and datasources are not setup correctly where previous successful statements would be commited anyway, due to autocommit not being set to false. Cannot rule anything out without additional information.