Как сделать сериализуемым MERGE

При выполнении параллельного MERGE, в то время как каждый сеанс использует другое значение (показано ниже в сноске ниже) для столбца первичного ключа id, все нормально, если я делаю это вручную в двух сеансах терминала.

MERGE
 INTO x
   USING (SELECT *** as id FROM DUAL) MERGE_SRC
     ON (x.id = MERGE_SRC.id)
 WHEN MATCHED THEN UPDATE SET val = val + 1 WHERE id = ***
 WHEN NOT MATCHED THEN INSERT VALUES (***, 99);
COMMIT;

Однако при запуске многопоточного теста нагрузки с тремя или более потоками я буду относительно быстро работать в ORA-08177 with locked table. Почему это? (И почему он не детерминирован в том смысле, что это не всегда происходит, когда транзакции перекрываются?)

Таблица была создана с помощью

create table x (id int primary key, val int);

SQL Server btw никогда не выдает исключения с эквивалентным оператором MERGE, выполняющим тот же эксперимент. Это даже верно при одновременной работе с одной строкой.

Это потому, что, возможно, MERGE не является атомарным, а сериализуемый режим работает оптимистично, так что гонка может отображаться только с достаточным количеством споров? Тем не менее, почему это происходит , даже если одновременно не работает одна и та же строка?

Btw, мои попытки исправить это, используя самый строгий доступный замок, не увенчались успехом. Поэтому любые идеи о том, как сделать этот атом, очень ценятся. Похоже, что ослабление уровня изоляции избавит меня от исключения, но риск несоответствий в случае, если в одной строке окажется 2 обновления (в противном случае, почему бы это не произошло в сериализованном режиме в первую очередь).

Ответы

Ответ 1

Исключение, которое вы видите, является прямым следствием использования строгой сериализации. Если одновременно активировано несколько транзакций, каждый из них начинается с SET SERACIZABLE LEVEL SERIALIZABLE, когда кто-либо из них совершает другие, получит ORA-08177. Это то, как строгая сериализация выполняется - база данных выдает ORA-08177 в любом сеансе, запущенном с LEOL SERIALIZABLE, если другая транзакция фиксируется в таблице, которую требует сериализуемый сеанс. Итак, в принципе, если вам действительно нужна строгая сериализация, вы должны обрабатывать ORA-08177 разумно, как в следующем:

DECLARE
  bSerializable_trans_complete  BOOLEAN := FALSE;
  excpSerializable              EXCEPTION;
  PRAGMA EXCEPTION_INIT(excpSerializable, -08177);
BEGIN
  <<SERIALIZABLE_LOOP>>
  WHILE NOT bSerializable_trans_complete
  LOOP
    BEGIN
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

      MERGE ...; -- or whatever

      COMMIT;

      bSerializable_trans_complete := TRUE;  -- allow SERIALIZABLE_LOOP to exit
    EXCEPTION
      WHEN excpSerializable THEN
        ROLLBACK;
        CONTINUE SERIALIZABLE_LOOP;
    END;
  END LOOP;  -- SERIALIZABLE_LOOP
END;

Сериализация - это не волшебство, и это не "бесплатно" (где "свободный" означает "Я, как разработчик, не должен ничего делать, чтобы заставить его работать правильно" ). Это требует большего планирования и работы со стороны разработчика, чтобы он функционировал должным образом, а не меньше. Поделитесь и наслаждайтесь.

Ответ 2

В Oracle режим SERIALIZABLE работает оптимистично, в отличие от, например, SQL Server, который делает пессимистическую блокировку в этом режиме. Это означает, что в последнем случае вы даже можете изменить одну и ту же строку без использования исключений.

Несмотря на документы:

База данных Oracle разрешает сериализуемую транзакцию изменять строку только в том случае, если изменения в строке, сделанные другими транзакциями, уже были совершены, когда началась сериализуемая транзакция. База данных генерирует ошибку, когда сериализуемая транзакция пытается обновить или удалить данные, измененные другой транзакцией, совершенной после сериализуемой транзакции:

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