DB2中merge语句上的重复键异常 [英] Duplicate key exception on merge statement in DB2

问题描述

问题：每天我们都有很多要添加到库存中的零件。我们通过读取的队列（使用4个不同的服务器）获取消息。队列始终包含元素，因此服务器读取速度尽可能快。我们希望服务器在文章退出时仅对其进行更新，否则将其插入。

The problem: Everyday we get lots of parts that we want to add to our stock. We get messages over a queue that we read from (using 4 different servers). The queue always contains elements so the servers read as fast as they can. We want the servers to simply update the article if the article exits, and insert it if it doesn't.

我们的第一个幼稚解决方案是选择是否查看该文章存在，如果没有，我们想插入。但是，由于没有行可供我们锁定，所以我们遇到了两个服务器同时执行选择，什么也找不到，然后尝试插入的问题。当然，其中一个给了我们一个重复的键例外。

Our first, naive solution was simply to select to see if the article existed, and if it didn't we wanted to insert. However since there was no row for us to lock we got problems with two servers doing the select at the same time, finding nothing, and then trying to insert. Of course one of them gave us a duplicate key exception.

因此，我们转而考虑了merge语句。我们做了一个看起来像这样的合并语句（为简化起见简化了）：

So instead we looked to the merge statement. We made a merge statement that looked like this (simplified for clarity):

    MERGE INTO articles sr
    USING ( 
        VALUES (:PARAM_ARTICLE_NUMBER))
        AS v(ARTICLE_NUMBER)
    ON sr.ARTICLE_NUMBER = v.ARTICLE_NUMBER
    WHEN MATCHED THEN 
        UPDATE SET 
        QUANTITY = QUANTITY + :PARAM_QUANTITY
                ARRIVED_DATE = CASE WHEN ARRIVED_DATE IS NULL
                THEN :PARAM_ARRIVED_DATE
                ELSE ARRIVED_DATE END
    WHEN NOT MATCHED THEN
        INSERT (QUANTITY, ARRIVED_DATE)
        VALUES (:PARAM_QUANTITY, CURRENT_TIMESTAMP);

但是，由于某些原因，我们仍然遇到重复的关键问题。我相信即使合并语句是原子的，两个合并语句也可以同时运行并同时选择。

However, for some reason we are still getting duplicate key problems. My believe is that even if the merge statement is atomic two merge statements can run concurrently and select at the same time.

除了锁定整个表之外，还有什么方法可以确保只插入一个表吗？

Is there any way, short of locking the whole table, to make sure we only get one insert?

推荐答案

在类似的情况下，运行具有重复读隔离级别的 MERGE 可以解决我们的问题。 RS不足，因为它仍然允许幻像行，而这正是您遇到的问题。您只需在语句末尾添加 WITH RR 并尝试一下即可。

In a similar situation running the MERGE with the Repeatable Read isolation level solved our problem. RS was insufficient, because it still allowed phantom rows, which is exactly the issue you are experiencing. You can simply add WITH RR at the end of the statement and try it out.

我们的测试套件运行最多1000个并发连接，并且我们并没有看到并发仅受该特定语句使用的RR隔离的影响很大。

Our test suite runs with up to 1000 simultaneous connections and we don't see concurrency much affected by the RR isolation used for that particular statement only.

这篇关于DB2中merge语句上的重复键异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！