Strange unique constraint error using Java and an oracle database

Question

I have a table named CUSTOMERS with the following columns :

CUSTOMER_ID (NUMBER), DAY(DATE), REGISTERED_TO(NUMBER)

There are more columns in the table but it is irrelevant to my question as only the above columns are defined together as the primary key

In our application we do a large amount of inserts into this table so we do not use MERGE but use the following statement :

INSERT INTO CUSTOMERS  (CUSTOMER_ID , DAY, REGISTERED_TO)                      
                   SELECT ?, ?, ? 
                   FROM DUAL WHERE NOT EXISTS 
                            (SELECT NULL 
                             FROM CUSTOMERS
                             WHERE CUSTOMER_ID = ? 
                               AND DAY = ? 
                               AND REGISTERED_TO = ?
                              )";

We use a PreparedStatement object using the batch feature to insert a large number of records collected through the flow of the application per customer.

Problem is that sometimes I get the following error :

ORA-00001: unique constraint (CUSTOMERS_PK) violated

Strange thing is that when I do NOT use batch inserts and insert each record one by one (by simply executing pstmt.execute()) there are no errors.

Is it something wrong with the insert statement ? the jdbc driver ? Am I not using the batch mechanism correctly ?

Here is a semi-pseudo-code of my insertion loop :

  pstmt = conn.prepareStatement(statement);
  pstmt.setQueryTimeout(90);

  for each customer :
     - pstmt.setObject(1, customer id);
     - pstmt.setObject(2, current day);
     - pstmt.setObject(3, registered to);
     - pstmt.addBatch();
  end for

  pstmt.executeBatch();

It is all enclosed in a try/catch/finally block making sure the statement and connection are closed at the end of this process.

How are you getting the input data? Is there a possibility of duplicate data in the input source? — muruga
No I checked that. I logged all statements going into the batch and there were no duplicates. (It does not happen every batch but seldomly) — giorashc
sounds like some parameters are still alive, when you call a second instance of your insert, you might use some static stuff? — SomeJavaGuy

Erich Kitzmueller Erich Kitzmueller · Accepted Answer · 2012-11-27T14:49:12

I guess you are using several threads or processes in parallel, each doing inserts. In this case, Oracle's transaction isolation feature defeats your attempt to do the merge, because sometimes the following is bound to happen:

session A runs your statement, inserts a row (x,y,z)
session B runs the same statement, tries to insert row (x,y,z), gets a lock and waits
session A commits
session B receives the "unique constraint violated" error

That's because until session A commits, session B doesn't see the new row, so it tries to insert the same.

Strange unique constraint error using Java and an oracle database

1 Answers