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

表上的键是 ID 字段上的 PRIMARY 键.

The key on the table is a PRIMARY key on the ID field.

推荐答案

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屋！