Upsert on oracle



Redis has incr function, which can easily increment atoms. At the same time, for non-existent key, the default initial value is 0, after incr, it is 1. This is convenient, which not only solves the problem of upsert, but also solves the problem of atomic concurrency.

Pg has the function of upsert, but oracle does not. Some people have given the following scheme

merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;

However, this is not atomic, and there are still multiple insert cases in the case of concurrency.


Is there any other plan? The traditional plan is to use optimistic locking or pessimistic lock, but this performance is too poor. Another solution is to log the operation of incr, i.e. instead of actually operating incr, record the operation and then count all the accumulated values in one go.