代码之家 › 专栏 › 技术社区 › Chakradhar Vyza

使用ORACLE merge和jdbc模板batchupdate插入重复项

jdbctemplate duplicates jdbc oracle spring

Chakradhar Vyza · 技术社区 · 7 年前

我正在使用Oracle merge和jdbc模板的batchupdate,它正在插入重复项。然而,问题是它并不是每次都发生。在一张20多万个项目的表格中,只有150个项目发生了这种情况。

该查询在sqldeveloper中运行时工作正常,我怀疑问题出在批量更新的执行方式上。

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();
                }
            });

1 回复 | 直到 7 年前

Connor McDonald 7 年前

如果没有所需的约束,多个会话将执行此操作。实例

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

瞧。。。重复值成为可能。如果我们重复这个实验,但这次让数据库知道如何强制所讨论的列的唯一性

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

如果会话1遇到错误(如验证等)并回滚了事务,则会话2将成功。