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