MERGE是SQL2008中的原子语句吗？ [英] Is MERGE an atomic statement in SQL2008?

问题描述

我使用 MERGE 语句作为 UPSERT 添加新记录或更新当前记录。我有多个线程通过多个连接和多个语句驱动数据库（每个线程一个连接和语句）。我一次批量处理50个语句。

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.

我很惊讶在我的期间收到重复密钥违规试验。我希望这是不可能的，因为 MERGE 将作为单个交易执行，或者是它？

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?

我的Java代码如下所示：

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

查询如下所示：

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

错误如下：

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)
...

表上的键是 PRIMARY 键入 ID 字段。

推荐答案

MERGE 是原子意味着所有更改都已提交或所有更改都已回滚。

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

在高位时不会阻止重复键并发性。添加 holdlock 提示将解决此问题。

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

这篇关于MERGE是SQL2008中的原子语句吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！