READ COMMITTED уровень изоляции базы данных в Oracle

Question

Я работаю над веб-приложением, связанным с Oracle. У нас есть таблица в оракуле с колонкой "активировано". Только одна строка может иметь этот столбец равным 1 в любой момент времени. Чтобы обеспечить это, мы использовали уровень изоляции SERIALIZED в Java, однако мы сталкиваемся с ошибкой "невозможно сериализовать транзакцию" и не можем понять, почему.

Нам было интересно, справится ли с этим уровень изоляции READ COMMITTED. Итак, мой вопрос заключается в следующем:

Если у нас есть транзакция, которая включает в себя следующий SQL:

SELECT *
FROM MODEL;

UPDATE MODEL
SET ACTIVATED = 0;

UPDATE MODEL
SET ACTIVATED = 1
WHERE RISK_MODEL_ID = ?;

COMMIT;

Учитывая, что возможно одновременное выполнение более чем одной из этих транзакций, можно ли для более чем одной строки MODEL установить флаг активации в 1?

Любая помощь будет оценена.

Answer 1

Ваше решение должно работать: ваше первое обновление заблокирует всю таблицу. Если другая транзакция не завершена, обновление будет ждать. Ваше второе обновление гарантирует, что только одна строка будет иметь значение 1, потому что вы блокируете таблицу (однако это не мешает операторам INSERT).

Вы также должны убедиться, что строка с RISK_MODEL_ID существует (или у вас будет нулевая строка со значением '1' в конце вашей транзакции).

Чтобы предотвратить одновременные операторы INSERT, вы должны заблокировать таблицу (в режиме EXCLUSIVE).

Answer 2

Вы можете рассмотреть возможность использования уникального индекса, основанного на функции, чтобы позволить Oracle обрабатывать ограничение, состоящее в том, чтобы иметь только одну строку с активированным флагом, установленным в 1.

CREATE UNIQUE INDEX MODEL_IX ON MODEL ( DECODE(ACTIVATED, 1, 1, NULL));

Это остановит более одной строки с установленным флагом 1, но это не означает, что всегда есть одна строка с установленным флагом 1.

Answer 3

Если вам нужно убедиться, что одновременно может выполняться только одна транзакция, вы можете использовать FOR UPDATE синтаксис. Поскольку у вас есть одна строка, которая требует блокировки, это очень эффективный подход.

declare
    cursor c is 
        select activated
        from model
        where activated = 1
        for update of activated;
    r c%rowtype;
begin
    open c;
    --  this statement will fail if another transaction is running
    fetch c in r;
    ....
    update model
    set activated = 0
    where current of c;

    update model
    set activated = 1
    where risk_model_id = ?;

    close c;

    commit;
end;
/

commit освобождает замок.

Поведение по умолчанию - ожидание освобождения строки. В противном случае мы можем указать NOWAIT, в этом случае любой другой сеанс, пытающийся обновить текущую активную строку, немедленно завершится неудачей, или мы можем добавить WAIT вариант с временем опроса. NOWAIT Это опция, позволяющая полностью избежать риска зависания, а также дает нам возможность сообщить пользователю, что кто-то еще обновляет таблицу, и он может захотеть это узнать.

Этот подход гораздо более масштабируем, чем обновление всех строк в таблице. Используйте основанный на функции индекс, как показал WW, для обеспечения соблюдения правила, согласно которому только одна строка может иметь ACTIVATED=1.