Is MERGE an atomic statement in SQL2008?

Is MERGE an atomic statement in SQL2008?

up vote 5 down vote favoriteshare [g+] share [fb] share [tw]

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.

link|improve this question

edited Mar 29 at 10:59

asked Mar 26 at 11:58

OldCurmudgeon
1,9251111

92% accept rate

How are you generating the primary key (V.ID)? – Paolo Mar 26 at 12:02

@Paolo ALTER TABLE CustomerSpend ADD CONSTRAINT [PK_CustomerSpend] PRIMARY KEY CLUSTERED (ID). Is there a better way? – OldCurmudgeon Mar 26 at 12:37

Sorry, I meant the actual value of the ID you were passing in the query. Mikael's got it below – the transaction is atomic, but there's nothing to stop multiple threads trying to insert the same key – Paolo Mar 26 at 21:31

feedback


1 Answer

active oldest votes


up vote 5 down vote accepted

MERGE is atomic meaning that either all changes are committed or all changes are rolled back.

It does not prevent duplicate keys in case of high concurrency. Adding holdlock hint will take care of that.

MERGE INTO CustomerSpend WITH (HOLDLOCK) 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 );

link|improve this answer

answered Mar 26 at 12:17

Mikael Eriksson
40.2k41537

Thanks! That makes sense. – OldCurmudgeon Mar 26 at 12:34

此条目发表在Uncategorized分类目录,贴了标签。将固定链接加入收藏夹。