I am using a MERGE
statement as an UPSERT
to either add a new record or update the current one. I have multiple threads driving the database through multiple connections and multiple statements (one connection and statement per thread). I am batching the statements 50 at a time.
I was very surprised to get a duplicate key
violation during my tests. I expected that to be impossible because the MERGE
will be performed as a single transaction, or is it?
My Java code looks like:
private void addBatch(Columns columns) throws SQLException {
try {
// Set parameters.
for (int i = 0; i < columns.size(); i++) {
Column c = columns.get(i);
// Column type is an `enum` with a `set` method appropriate to its type, e.g. setLong, setString etc.
c.getColumnType().set(statement, i + 1, c.getValue());
}
// Add the insert as a batch.
statement.addBatch();
// Ready to execute?
if (++batched >= MaxBatched) {
statement.executeBatch();
batched = 0;
}
} catch (SQLException e) {
log.warning("addBatch failed " + sql + " thread " + Thread.currentThread().getName(), e);
throw e;
}
}
The query looks like this:
MERGE INTO CustomerSpend AS T
USING ( SELECT ? AS ID, ? AS NetValue, ? AS VoidValue ) AS V
ON T.ID = V.ID
WHEN MATCHED THEN
UPDATE SET T.ID = V.ID, T.NetValue = T.NetValue + V.NetValue, T.VoidValue = T.VoidValue + V.VoidValue
WHEN NOT MATCHED THEN
INSERT ( ID,NetValue,VoidValue ) VALUES ( V.ID, V.NetValue, V.VoidValue );
The error reads:
java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'PK_CustomerSpend'. Cannot insert duplicate key in object 'dbo.CustomerSpend'. The duplicate key value is (498288 ).
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:944)
at x.db.Db$BatchedStatement.addBatch(Db.java:299)
...
The key on the table is a PRIMARY
key on the ID
field.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…