Блог

Добро пожаловать в Oracle pro

В базе данных Oracle 12 (обновление уже запланировано ;-)) у нас есть настройка различных таблиц, обновляющих общую базовую таблицу с помощью триггеров «после обновления«, таких как:

Теперь таблица 1 содержит n столбцов, где, скажем, 2 из n относятся к таблице Search_Flat. Поскольку обновление таблицы 1 может повлиять только на столбцы, не относящиеся к Seach_Flat, мы хотим добавить проверки в триггер. Итак, наш первый подход заключается в следующем:

 CREATE OR REPLACE TRIGGER tr_tbl_1_au_search
    AFTER UPDATE OF
        field_a,
        field_b
    ON schemauser.search_flat
FOR EACH ROW
    BEGIN
        IF :new.field_a <> :old.field_a THEN 
            UPDATE schemauser.search_flat SET field_a = :new.field_a WHERE id = :new.ID;
        END IF;
        IF :new.field_b <> :old.field_b THEN 
            UPDATE schemauser.search_flat SET field_b = :new.field_b WHERE id = :new.ID;
        END IF;
    END;
 

В качестве альтернативы мы также могли бы настроить триггер следующим образом:

 CREATE OR REPLACE TRIGGER tr_tbl_1_au_search
    AFTER UPDATE OF
        field_a,
        field_b
    ON schemauser.search_flat
FOR EACH ROW
    BEGIN
        IF :new.field_a <> :old.field_a OR :new.field_b <> :old.field_b THEN 
            UPDATE schemauser.search_flat 
            SET field_a = :new.field_a, 
                field_b = :new.field_b 
            WHERE id = :new.ID;
        END IF;
    END;
 

Теперь вопрос заключается в настройке самих триггеров. Какой подход лучше в отношении:

• время блокировки строк search_flat
• общая производительность затронутых компонентов (т. е. table_1, триггер и search_flat)

В производстве мы говорим о 4 таблицах с 10 полями, каждое из которых учитывается в триггерах. И у нас есть независимые серверы приложений, которые получают доступ к общей базе данных, обновляя 4 таблицы одновременно. Время от времени мы обнаруживаем следующую ошибку, из-за которой мы не хотим оптимизировать триггеры:

ORA-02049: время ожидания: распределенная транзакция ожидает блокировки

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

Я с нетерпением жду обсуждения и ваших мыслей.

Как я понимаю, в вашем посте у вас есть 4 живые таблицы (называемые «таблица1», «таблица2» и т. Д.), По которым вы хотите выполнять поиск, Но запросы от них слишком медленные, поэтому вы хотите вместо этого поддерживать единую плоскую таблицу для поиска и иметь триггеры, чтобы эта плоская таблица всегда обновлялась. Вы хотите знать, какой из двух подходов к запуску лучше.

Я думаю, что ответ «ни то, ни другое», так как оба склонны к тупикам. Представьте себе такой сценарий

Пользователь 1 —

 UPDATE table1 
SET field_a = 500 
WHERE <condition effecting 200 distinct IDs>
 

Пользователь 2 примерно в то же время —

 UPDATE table1 
SET field_b = 700 
WHERE <condition effecting 200 distinct IDs>
 

Триггеры начинают обработку. Вы не можете контролировать порядок, в котором обновляются строки. Может быть, дело обстоит так:

Триггер пользователя 1, индекс времени 100 ->

 UPDATE search_flat SET field_a = 500 WHERE id = 90;
 

Триггер пользователя 2, индекс времени 101 ->

 UPDATE search_flat SET field_b = 700 WHERE id = 91;
 

Триггер пользователя 1, индекс времени 102 ->

 UPDATE search_flat SET field_a = 500 WHERE id = 91;  (waits on user 2's session)
 

Триггер пользователя 2, индекс времени 103 ->

 UPDATE search_flat SET field_b = 700 WHERE id = 90;  (deadlock error)
 

Первоначальное обновление пользователя 2 завершается ошибкой и откатывается.

У вас есть несколько параллельных процессов, все они обновляют один и тот же набор строк search_flat , не контролируя порядок обработки. Это рецепт для тупиковых ситуаций.

Если вы хотите сделать это безопасно, вам не следует рассматривать ни один из FOR EACH ROW подходов к триггеру, которые вы описали. Скорее, сделайте составной триггер для этого.

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

 -- Aside: consider setting this at the system level if on 12.2 or later
--   alter system set temp_undo_enabled=false;

CREATE GLOBAL TEMPORARY TABLE table1_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80),
  field_b     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE table2_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

-- .. so on for table3 and 4.

CREATE OR REPLACE TRIGGER table1_search_maint_trg
  FOR INSERT OR UPDATE OR DELETE ON table1  -- with similar compound triggers for table2, 3, 4.
    COMPOUND TRIGGER

  AFTER EACH ROW IS
  BEGIN
    -- Update the table-1 specific GTT with the changes.
    CASE WHEN INSERTING OR UPDATING THEN
      -- Assumes ID is immutable primary key
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:new.id, :new.field_a);
         WHEN DELETING THEN
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:old.id, null);  -- or figure out what you want to do about deletes.
    END CASE;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- Write the data from the GTT to the search_flat table.
    -- NOTE: The ORDER BY in the next line is what saves us from deadlocks.
    FOR r IN ( SELECT id, field_a, field_b FROM table1_updates_gtt ORDER BY id ) LOOP
      -- TODO: replace with BULK processing for better performance, if DMLs can affect a lot of rows
      UPDATE search_flat sf
      SET    sf.field_a = r.field_a,
             sf.field_b = r.field_b
      WHERE  sf.id = r.id
      AND    ( sf.field_a <> r.field_a 
               OR (sf.field_a IS NULL AND r.field_a IS NOT NULL) 
               OR (sf.field_a IS NOT NULL AND r.field_a IS NULL)
               OR sf.field_b <> r.field_b 
               OR (sf.field_b IS NULL AND r.field_b IS NOT NULL) 
               OR (sf.field_b IS NOT NULL AND r.field_b IS NULL)
             );
    END LOOP;             
          
  END AFTER STATEMENT;

END table1_search_maint_trg;
 

Кроме того, как отмечали многочисленные комментаторы, для этого, вероятно, лучше использовать материализованный вид. Если вы используете 12.2 или более позднюю версию, материализованные представления в реальном времени (они же «ВКЛЮЧИТЬ ВЫЧИСЛЕНИЕ ЗАПРОСОВ») предлагают многообещающие возможности для такого рода вещей. Никаких COMMIT накладных расходов для вашего приложения и результатов поиска в режиме реального времени. Просто время поиска немного сокращается, если в базовых таблицах много последних обновлений.