Могу ли я сделать атомный MERGE в Oracle?

У меня есть несколько экземпляров приложения J2EE, работающего в одном кластере WebLogic.

В какой-то момент эти приложения делают MERGE для вставки или обновления записи во внутреннюю базу данных Oracle. MERGE проверяет, существует ли строка с указанным первичным ключом. Если он там, обновите. Если нет, вставьте.

Теперь предположим, что два экземпляра приложения хотят вставить или обновить строку с помощью первичного ключа = 100. Предположим, что строка не существует. Во время стадии "проверки" слияния они оба видят, что строк нет, поэтому оба они пытаются вставить. Затем я получаю уникальное нарушение ограничения ключа.

Мой вопрос заключается в следующем: есть ли атомный MERGE в Oracle? Я ищу что-то похожее на INSERT ... FOR UPDATE в PL/SQL, за исключением того, что я могу выполнять SQL только из своих приложений.

РЕДАКТ: Я был неясен. Я использую оператор MERGE, пока эта ошибка все еще встречается. Дело в том, что только "модифицирующая" часть является атомарной, а не всего слияния.

Ответ 1

Оператор MERGE во втором сеансе не может "видеть" вставку, которую сделал первый сеанс до тех пор, пока этот сеанс не завершится. Если вы уменьшите размер транзакций, вероятность того, что это произойдет, будет уменьшена.

Или вы можете сортировать или разбивать свои данные так, чтобы все записи данного первичного ключа были предоставлены одному сеансу. Простая функция, такая как "первичный ключ mod N", должна равномерно распределяться на N сеансов.

btw, если две записи имеют один и тот же первичный ключ, второй будет перезаписывать первый. Звучит немного странно.

Ответ 2

Это не проблема с MERGE как таковой. Скорее проблема заключается в вашей заявке. Рассмотрим эту хранимую процедуру:

create or replace procedure upsert_t23 
    ( p_id in t23.id%type
      , p_name in t23.name%type )
is
    cursor c is
        select null 
        from t23
        where id = p_id;
    dummy varchar2(1);
begin
    open c;
    fetch c into dummy;
    if c%notfound then
        insert into t23 
            values (p_id, p_name);
    else
        update t23
             set name = p_name
             where id = p_id;
    end if;
 end;

Итак, это эквивалент PL/SQL для MERGE на T23. Что произойдет, если две сессии вызовут его одновременно?

SSN1>  exec upsert_t23(100, 'FOX IN SOCKS')

SSN2>  exec upsert_t23(100, 'MR KNOX')

SSN1 попадает туда первым, не находит соответствующей записи и вставляет запись. SSN2 получает второе место, но до того, как SSN1 совершит ошибку, не находит записи, вставляет запись и зависает, потому что SSN1 имеет блокировку уникального индекса node для 100. Когда SSN1 совершает SSN2, он будет нарушать нарушение DUP_VAL_ON_INDEX.

Операция MERGE работает точно так же. Обе сессии проведут проверку on (t23.id = 100), не найдут ее и не спустит ветвь INSERT. Первый сеанс будет успешным, а второй вызовет ORA-00001.

Один из способов справиться с этим - ввести пессимистическую блокировку. В начале процедуры UPSERT_T23 мы блокируем таблицу:

...
lock table t23 in row shared mode nowait;
open c;
...

Теперь SSN1 прибывает, захватывает блокировку и продолжает, как раньше. Когда SSN2 приходит, он не может получить блокировку, поэтому он немедленно сработает. Это разочаровывает второго пользователя, но, по крайней мере, они не висят, а также знают, что кто-то другой работает над той же записью.

Нет синтаксиса для INSERT, который эквивалентен SELECT... FOR UPDATE, потому что выбирать нечего. И поэтому нет такого синтаксиса для MERGE. Что вам нужно сделать, это включить оператор LOCK TABLE в программный блок, который выдает MERGE. Возможно ли это для вас, зависит от используемой структуры.

Ответ 3

Да, и он называется.... MERGE

РЕДАКТИРОВАТЬ. Единственный способ получить эту воду - вставить, поймать исключение dup_val_on_index и обработать его соответствующим образом (возможно, обновить или вставить другую запись). Это легко сделать с помощью PL/SQL, но вы не можете использовать это.

Вы также ищете обходные пути. Можете ли вы поймать dup_val_on_index в Java и снова добавить дополнительное ОБНОВЛЕНИЕ?

В псевдокоде:

try {
  // MERGE
}
catch (dup_val_on_index) {
  // UPDATE
}

Ответ 4

Я удивлен, что MERGE будет вести себя так, как вы описываете, но я не использовал его достаточно, чтобы сказать, нужно ли это или нет.

В любом случае у вас могут быть транзакции, которые хотят выполнить слияние, установите их уровень изоляции в SERIALIZABLE. Я думаю, что это может решить вашу проблему.