Using ORACLE merge with jdbc template batchupdate inserting duplicates

Question

I'm using Oracle merge with jdbc template's batchupdate and it is inserting duplicates. However, the problem is it is not happening every time. It happened only for 150 items in a table of more than 2,00,000 items.

The query works properly when run in sqldeveloper, i'm suspecting the problem is in the way batch update is being performed.

String sql = "MERGE INTO XXX USING dual ON  (column_one = ? ) " +
                    "WHEN NOT MATCHED THEN INSERT " +
                    "(column_one, column_two, column_three) " +
                    "VALUES (?,?,?)";
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                    AuditData data = requestData.get(i);

                    preparedStatement.setString(1, columnOne);
                    preparedStatement.setString(2, columnOne);
                    preparedStatement.setString(3, columnTwo);
                    preparedStatement.setString(4, columnThree);
                }

                @Override
                public int getBatchSize() {
                    return requestData.size();
                }
            });

Are you the only person running this code ? If there are more than one, you need to make sure you have the relevant unique/primary key constraints in place, otherwise multiple sessions can yield duplicates because they cannot see each others uncommitted changes — Connor McDonald
– Connor McDonald, Commented Apr 19, 2018 at 8:59
Yes, only one instance of this code runs.But i'm using database pool and client can send the same column_one multiple times until they get acknowledgement. Can this result in such scenario? — Chakradhar Vyza
– Chakradhar Vyza, Commented Apr 19, 2018 at 11:45

Connor McDonald · Accepted Answer · 2018-04-19 23:52:52Z

Multiple sessions will do this, that is, if you do not have the required constraints in place. Example

Session 1

SQL> create table t ( x int );

Table created.

SQL>
SQL> declare
  2    incoming_value int := 1;
  3  begin
  4    MERGE INTO t USING dual ON  (x = incoming_value )
  5    WHEN NOT MATCHED THEN INSERT (x)
  6    VALUES (incoming_value);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Session 2

SQL> declare
  2    incoming_value int := 1;
  3  begin
  4    MERGE INTO t USING dual ON  (x = incoming_value )
  5    WHEN NOT MATCHED THEN INSERT (x)
  6    VALUES (incoming_value);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Session 1

SQL> commit;

Commit complete.

Session 2

SQL> commit;

Commit complete.

SQL> select * from t;

         X
----------
         1
         1

and voila...duplicate values become possible. If we repeat the experiment, but this time let the database know to enforce the uniqueness of the column in question

Session 1

SQL> create table t ( x int PRIMARY KEY);

Table created.

SQL>
SQL> declare
  2    incoming_value int := 1;
  3  begin
  4    MERGE INTO t USING dual ON  (x = incoming_value )
  5    WHEN NOT MATCHED THEN INSERT (x)
  6    VALUES (incoming_value);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Session 2

SQL> declare
  2    incoming_value int := 1;
  3  begin
  4    MERGE INTO t USING dual ON  (x = incoming_value )
  5    WHEN NOT MATCHED THEN INSERT (x)
  6    VALUES (incoming_value);
  7  end;
  8  /

[is blocked - it cannot proceed until we know the outcome of session 1]

Session 1

SQL> commit;

Commit complete.


Session 2

ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.SYS_C0068793) violated
ORA-06512: at line 4

If Session 1 had encountered an error (eg validation etc) and had rolled back the transaction, then Session 2 would have been successful.

Collectives™ on Stack Overflow

Using ORACLE merge with jdbc template batchupdate inserting duplicates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related