[jira] Created: (OPENJPA-466) Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app

Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
-------------------------------------------------------------------------------------------

                 Key: OPENJPA-466
                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
             Project: OpenJPA
          Issue Type: Bug
    Affects Versions: 1.0.0
         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
Oracle XE 10g (JDBC driver 10.2.0.3.0)
Windows XP Pro
            Reporter: Frank Le
            Priority: Blocker


Here's how I annotate the ID:
    @Id
    @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
    private Long id;

Here's how I create the (Oracle) sequence:
CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;

I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!

You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.


--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12572281#action_12572281 ]

Gil Markham commented on OPENJPA-466:
-------------------------------------

I've seen the same behavior using postgresql 8.2/8.3 and sequences and openjpa 1.0.1 and 1.0.2.

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12669924#action_12669924 ]

Joe Pullen commented on OPENJPA-466:
------------------------------------

is this issue resolved ? its pretty old and I am experiencing a similar error with version 1.2.0 and the priority is Blocker ?

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Work on OPENJPA-466 started by Tim McConnell.

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tim McConnell updated OPENJPA-466:
----------------------------------

    Assignee: Tim McConnell

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tim McConnell updated OPENJPA-466:
----------------------------------

    Affects Version/s: 1.0.1
                       1.1.0
                       1.2.0

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12670605#action_12670605 ]

Tim McConnell commented on OPENJPA-466:
---------------------------------------

Hi Joe, I'm trying to replicate the failure so that I can fix this problem. However, I'm still unable to reproduce it. Are you only seeing it fail in a multi-threaded application ?? And if so, are both threads connected to the same database and/or using the same sequence ?? Thanks for any additional information.

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12671815#action_12671815 ]

Joe Pullen commented on OPENJPA-466:
------------------------------------

Hi Tim,

Definately get the issue in a WebLogic multithreaded enviroment with 11g (RAC). The version of  OpenJPA used is 1.2.0.
The issues comes with more load (25-100 TPS). The application has about 25 entities all using seqs on the primary key.
The error is always on the flush. The seq cache is set to 100.

The test cases are not mutilthreaded in our app, but maybe I try to get a working, or is that non working ;-) test.
Do you have ideas where the problem lies ?

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12672404#action_12672404 ]

Tim McConnell commented on OPENJPA-466:
---------------------------------------

Hi Joe, I think I'm finally able to replicate this error !! I haven't run it yet on Oracle, but I'm consistently able to get this failure using postgresql. Does this look similar to the error you're getting in Oracle ??

Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:742775 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: ERROR: duplicate key value violates unique constraint "entity_employee_pkey" {prepstmnt 17832855 INSERT INTO ENTITY_EMPLOYEE (id, firstName, lastName, salary) VALUES (?, ?, ?, ?) [params=(int) 273350, (String) First_name_1628, (String) Last_name_1628, (float) 1628.0]} [code=0, state=23505]

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12672482#action_12672482 ]

Pinaki Poddar commented on OPENJPA-466:
---------------------------------------

Is openjpa.Multithreaded configuration property is set to 'true'?

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12672505#action_12672505 ]

Tim McConnell commented on OPENJPA-466:
---------------------------------------

Hi Pinaki, in my postgresql testcase I'm setting the openjpa.Multithreaded property in the setup() mehtod as below. Does this look correct ??

    public void setUp() {
        setUp(EntityPerson.class,
                EntityEmployee.class,
        CLEAR_TABLES, "openjpa.Multithreaded", "true");
    }

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12672606#action_12672606 ]

Joe Pullen commented on OPENJPA-466:
------------------------------------

Hi Tim,

Looks similar to mine from oracle.

]]></stack><cause><stack><![CDATA[      <openjpa-1.2.0-r422266:683325 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: ORA-00001: unique constraint (P
OR.DSTA_PK) violated
 {prepstmnt 97 INSERT INTO DIA_STATUSES (DSTA_ID, APPLICATION_ID, COMPONENT_NAME, STATUS_LEVEL, REFERENCE_ID, SOFTWARE_VERSION, TIME_STAMP, VERSION, DATE_CREATED, CREATED_BY, DAT
E_MODIFIED, MODIFIED_BY, PPPC_ID, PPRC_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [params=(long) 230503, (String) JTX, (String) input, (short) 1, (null) null, (String)
 19-60-08, (Timestamp) 2009-02-10 17:15:05.49, (int) 1, (Timestamp) 2009-02-10 17:15:05.493, (String) weblogic, (Timestamp) 2009-02-10 17:15:05.493, (String) weblogic, (null) nul
l, (null) null]} [code=1, state=23000]
FailedObject: com.csg.cs.pay.process.diag.domain.Status@17b05c9
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4238)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4203)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:131)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:89)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:72)
        at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flushPrimaryRow(OperationOrderUpdateManager.java:203)
        at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flush(OperationOrderUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:655)
   

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12672606#action_12672606 ]

joepullen edited comment on OPENJPA-466 at 2/11/09 3:46 AM:
-------------------------------------------------------------

Hi Tim,

Looks similar to mine from oracle.

]]></stack><cause><stack><![CDATA[      <openjpa-1.2.0-r422266:683325 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: ORA-00001: unique constraint (P
OR.DSTA_PK) violated
 {prepstmnt 97 INSERT INTO DIA_STATUSES (DSTA_ID, APPLICATION_ID, COMPONENT_NAME, STATUS_LEVEL, REFERENCE_ID, SOFTWARE_VERSION, TIME_STAMP, VERSION, DATE_CREATED, CREATED_BY, DAT
E_MODIFIED, MODIFIED_BY, PPPC_ID, PPRC_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [params=(long) 230503, (String) JTX, (String) input, (short) 1, (null) null, (String)
 19-60-08, (Timestamp) 2009-02-10 17:15:05.49, (int) 1, (Timestamp) 2009-02-10 17:15:05.493, (String) weblogic, (Timestamp) 2009-02-10 17:15:05.493, (String) weblogic, (null) nul
l, (null) null]} [code=1, state=23000]
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4238)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4203)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:131)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:89)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:72)
        at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flushPrimaryRow(OperationOrderUpdateManager.java:203)
        at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flush(OperationOrderUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:655)
   

      was (Author: joepullen):
    Hi Tim,

Looks similar to mine from oracle.

]]></stack><cause><stack><![CDATA[      <openjpa-1.2.0-r422266:683325 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: ORA-00001: unique constraint (P
OR.DSTA_PK) violated
 {prepstmnt 97 INSERT INTO DIA_STATUSES (DSTA_ID, APPLICATION_ID, COMPONENT_NAME, STATUS_LEVEL, REFERENCE_ID, SOFTWARE_VERSION, TIME_STAMP, VERSION, DATE_CREATED, CREATED_BY, DAT
E_MODIFIED, MODIFIED_BY, PPPC_ID, PPRC_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [params=(long) 230503, (String) JTX, (String) input, (short) 1, (null) null, (String)
 19-60-08, (Timestamp) 2009-02-10 17:15:05.49, (int) 1, (Timestamp) 2009-02-10 17:15:05.493, (String) weblogic, (Timestamp) 2009-02-10 17:15:05.493, (String) weblogic, (null) nul
l, (null) null]} [code=1, state=23000]
FailedObject: com.csg.cs.pay.process.diag.domain.Status@17b05c9
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4238)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4203)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:131)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:89)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:72)
        at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flushPrimaryRow(OperationOrderUpdateManager.java:203)
        at org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.flush(OperationOrderUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:655)
   
 

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12672608#action_12672608 ]

Joe Pullen commented on OPENJPA-466:
------------------------------------

tried with the openjpa.Multithreaded  as true for oracle and didnt help

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12672842#action_12672842 ]

Tim McConnell commented on OPENJPA-466:
---------------------------------------

Hi again Joe, I can now replicate the problem on both postgresql and oracle databases. Here is the error I get on oracle. Now, I will determine the cause for each. Thanks for your help.....

Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743437M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: ORA-00001: unique constraint (SYSTEM.SYS_C004001) violated
 {prepstmnt 30673895 INSERT INTO ENTITY_PERSON (id, firstName, lastName) VALUES (?, ?, ?) [params=(int) 15662, (String) First_name_1011, (String) Last_name_1011]} [code=1, state=23000]
FailedObject: org.apache.openjpa.persistence.hightps.sequence.EntityPerson@114c8b6
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4244)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4209)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:127)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.batchOrExecuteRow(BatchingPreparedStatementManagerImpl.java:100)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:84)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:93)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:81)
        at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:549)
        at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:106)
        at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:630)
        at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
        ... 15 more

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12673900#action_12673900 ]

Tim McConnell commented on OPENJPA-466:
---------------------------------------

Failure for DB2 below. This was to demonstrate that it is not just a Oracle and PostgreSQL problem. There seems to be a thread-safe problem in OpenJPA for all databases that support native Sequences.

testMultiThreadedLoad(org.apache.openjpa.persistence.hightps.sequence.TestSequence)  Time elapsed: 178.797 sec  <<< ERROR!
org.apache.openjpa.persistence.hightps.sequence.TestSequence$ThreadingException: The 3 embedded errors occured in the execution of 8 iterations of 6 threads: [reflection invocation: (testMultiThreadedLoad)]
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.mttest(TestSequence.java:466)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.mttest(TestSequence.java:360)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.mttest(TestSequence.java:310)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.mttest(TestSequence.java:304)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.testMultiThreadedLoad(TestSequence.java:255)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at junit.framework.TestCase.runTest(TestCase.java:154)
        at junit.framework.TestCase.runBare(TestCase.java:127)
        at org.apache.openjpa.persistence.test.PersistenceTestCase.runBare(PersistenceTestCase.java:443)
        at junit.framework.TestResult$1.protect(TestResult.java:106)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.framework.TestResult.run(TestResult.java:109)
        at junit.framework.TestCase.run(TestCase.java:118)
        at org.apache.openjpa.persistence.test.PersistenceTestCase.run(PersistenceTestCase.java:173)
        at junit.framework.TestSuite.runTest(TestSuite.java:208)
        at junit.framework.TestSuite.run(TestSuite.java:203)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.maven.surefire.junit.JUnitTestSet.execute(JUnitTestSet.java:213)
        at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:140)
        at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:127)
        at org.apache.maven.surefire.Surefire.run(Surefire.java:177)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:334)
        at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:980)
Nested Throwable #1: org.apache.openjpa.persistence.hightps.sequence.TestSequence$ThreadingException: thread=Thread[reflection invocation: (testMultiThreadedLoad) [5 of 6],5,main];threadNum=5;maxThreads=6;iteration=1;maxIterations=8
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$2.run(TestSequence.java:429)
Nested Throwable #1: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$1.run(TestSequence.java:363)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$2.run(TestSequence.java:422)
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M fatal store error> org.apache.openjpa.persistence.RollbackException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:530)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.createManyPersonsInSeparateTransactions(TestSequence.java:134)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.testMultiThreadedLoad(TestSequence.java:261)
        ... 6 more
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M fatal general error> org.apache.openjpa.persistence.PersistenceException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
        at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2264)
        at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2111)
        at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2009)
        at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1927)
        at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
        at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1451)
        at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:895)
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:519)
        ... 8 more
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.ENTITY_PERSON" from having duplicate rows for those columns.
FailedObject: prepstmnt 30057146 INSERT INTO ENTITY_PERSON (id, firstName, lastName) VALUES (?, ?, ?) [org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement]
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4244)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4209)
        at org.apache.openjpa.jdbc.sql.DB2Dictionary.newStoreException(DB2Dictionary.java:507)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushBatch(BatchingPreparedStatementManagerImpl.java:193)
        at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:63)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:630)
        at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
        ... 15 more
Caused by: com.ibm.db2.jcc.b.SQLException: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.ENTITY_PERSON" from having duplicate rows for those columns.
        at com.ibm.db2.jcc.b.ce.d(ce.java:975)
        at com.ibm.db2.jcc.a.bd.k(bd.java:312)
        at com.ibm.db2.jcc.a.bd.a(bd.java:61)
        at com.ibm.db2.jcc.a.r.a(r.java:64)
        at com.ibm.db2.jcc.a.bq.c(bq.java:217)
        at com.ibm.db2.jcc.b.cf.C(cf.java:1109)
        at com.ibm.db2.jcc.b.cf.a(cf.java:1505)
        at com.ibm.db2.jcc.b.cf.executeUpdate(cf.java:322)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:981)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1501)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:249)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushSingleRow(BatchingPreparedStatementManagerImpl.java:215)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushBatch(BatchingPreparedStatementManagerImpl.java:154)
        ... 20 more
Nested Throwable #2: org.apache.openjpa.persistence.hightps.sequence.TestSequence$ThreadingException: thread=Thread[reflection invocation: (testMultiThreadedLoad) [2 of 6],5,main];threadNum=2;maxThreads=6;iteration=1;maxIterations=8
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$2.run(TestSequence.java:429)
Nested Throwable #1: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$1.run(TestSequence.java:363)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$2.run(TestSequence.java:422)
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M fatal store error> org.apache.openjpa.persistence.RollbackException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:530)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.createManyPersonsAndEmployeesInSeparateTransactions(TestSequence.java:179)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.testMultiThreadedLoad(TestSequence.java:267)
        ... 6 more
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M fatal general error> org.apache.openjpa.persistence.PersistenceException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
        at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2264)
        at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2111)
        at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2009)
        at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1927)
        at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
        at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1451)
        at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:895)
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:519)
        ... 8 more
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".
FailedObject: prepstmnt 5148820 INSERT INTO ENTITY_EMPLOYEE (id, firstName, lastName, salary) VALUES (?, ?, ?, ?) [org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement]
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4244)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4209)
        at org.apache.openjpa.jdbc.sql.DB2Dictionary.newStoreException(DB2Dictionary.java:507)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushBatch(BatchingPreparedStatementManagerImpl.java:193)
        at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:63)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:630)
        at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
        ... 15 more
Caused by: com.ibm.db2.jcc.b.SQLException: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".
        at com.ibm.db2.jcc.b.ce.e(ce.java:1093)
        at com.ibm.db2.jcc.a.bd.s(bd.java:678)
        at com.ibm.db2.jcc.a.bd.k(bd.java:335)
        at com.ibm.db2.jcc.a.bd.a(bd.java:61)
        at com.ibm.db2.jcc.a.r.a(r.java:64)
        at com.ibm.db2.jcc.a.bq.c(bq.java:217)
        at com.ibm.db2.jcc.b.cf.C(cf.java:1109)
        at com.ibm.db2.jcc.b.cf.a(cf.java:1505)
        at com.ibm.db2.jcc.b.cf.executeUpdate(cf.java:322)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:981)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1501)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:249)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushSingleRow(BatchingPreparedStatementManagerImpl.java:215)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushBatch(BatchingPreparedStatementManagerImpl.java:154)
        ... 20 more
Nested Throwable #3: org.apache.openjpa.persistence.hightps.sequence.TestSequence$ThreadingException: thread=Thread[reflection invocation: (testMultiThreadedLoad) [4 of 6],5,main];threadNum=4;maxThreads=6;iteration=1;maxIterations=8
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$2.run(TestSequence.java:429)
Nested Throwable #1: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$1.run(TestSequence.java:363)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence$2.run(TestSequence.java:422)
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M fatal store error> org.apache.openjpa.persistence.RollbackException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:530)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.createManyPersonsAndEmployeesInSeparateTransactions(TestSequence.java:179)
        at org.apache.openjpa.persistence.hightps.sequence.TestSequence.testMultiThreadedLoad(TestSequence.java:267)
        ... 6 more
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M fatal general error> org.apache.openjpa.persistence.PersistenceException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
        at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2264)
        at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2111)
        at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2009)
        at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1927)
        at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
        at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1451)
        at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:895)
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:519)
        ... 8 more
Caused by: <openjpa-2.0.0-SNAPSHOT-r422266:743836M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".
FailedObject: prepstmnt 6639365 INSERT INTO ENTITY_EMPLOYEE (id, firstName, lastName, salary) VALUES (?, ?, ?, ?) [org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement]
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4244)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4209)
        at org.apache.openjpa.jdbc.sql.DB2Dictionary.newStoreException(DB2Dictionary.java:507)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushBatch(BatchingPreparedStatementManagerImpl.java:193)
        at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:63)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:630)
        at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
        ... 15 more
Caused by: com.ibm.db2.jcc.b.SQLException: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".
        at com.ibm.db2.jcc.b.ce.e(ce.java:1093)
        at com.ibm.db2.jcc.a.bd.s(bd.java:678)
        at com.ibm.db2.jcc.a.bd.k(bd.java:335)
        at com.ibm.db2.jcc.a.bd.a(bd.java:61)
        at com.ibm.db2.jcc.a.r.a(r.java:64)
        at com.ibm.db2.jcc.a.bq.c(bq.java:217)
        at com.ibm.db2.jcc.b.cf.C(cf.java:1109)
        at com.ibm.db2.jcc.b.cf.a(cf.java:1505)
        at com.ibm.db2.jcc.b.cf.executeUpdate(cf.java:322)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:981)
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1501)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:249)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushSingleRow(BatchingPreparedStatementManagerImpl.java:215)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushBatch(BatchingPreparedStatementManagerImpl.java:154)
        ... 20 more

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tim McConnell updated OPENJPA-466:
----------------------------------

    Attachment: OPENJPA-466.patch

The attached patch provides a testcase to demonstrate the failure on both PostgreSQL and Oracle, and likewise provides a fix for both PostgreSQL and Oracle. I would suggest that it be applied to both Trunk and the 1.2.x branch. Thanks

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>         Attachments: OPENJPA-466.patch
>
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Albert Lee updated OPENJPA-466:
-------------------------------

    Fix Version/s: 2.0.0

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>             Fix For: 2.0.0
>
>         Attachments: OPENJPA-466.patch
>
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tim McConnell resolved OPENJPA-466.
-----------------------------------

    Resolution: Fixed

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>             Fix For: 2.0.0
>
>         Attachments: OPENJPA-466.patch
>
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/OPENJPA-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12676381#action_12676381 ]

Joe Pullen commented on OPENJPA-466:
------------------------------------

Hi Tim, thanks for the patch I can guarantee to anyone who is using OpenJPA with a larger configuration (4 solaris machines with 24 core each) and oracle sequences that this patch is a MUST.
After testing with the patch our problems with duplicate pk from sequences have disappeared. Also the patch doesnt seem to have a major impact on performance.

> Primary key constraint violated using (Oracle) sequence to generate ID in multithreaded app
> -------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-466
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-466
>             Project: OpenJPA
>          Issue Type: Bug
>    Affects Versions: 1.0.0, 1.0.1, 1.1.0, 1.2.0
>         Environment: OpenJPA 1.0.0 (also tried 1.0.1 and 1.1.0-SNAPSHOT)
> Oracle XE 10g (JDBC driver 10.2.0.3.0)
> Windows XP Pro
>            Reporter: Frank Le
>            Assignee: Tim McConnell
>            Priority: Blocker
>             Fix For: 2.0.0
>
>         Attachments: OPENJPA-466.patch
>
>
> Here's how I annotate the ID:
>     @Id
>     @SequenceGenerator(name = "FooSeq", sequenceName = "seq_foo", allocationSize = 20)
>     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FooSeq")
>     private Long id;
> Here's how I create the (Oracle) sequence:
> CREATE SEQUENCE seq_foo START WITH 1 INCREMENT BY 1;
> I get a primary key unique constraint violated in a multithreaded app i.e. it doesn't happen in single-threaded!
> You can simply reproduce this error by either create blocking queue or blocking thread pool say size 5 to insert 10000+ object.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.