Иван Задворьев - Язык PL/SQL

Рассмотрим следующий пример.

Пусть в базе данных есть таблица балансов клиентов:

CREATE TABLE balances (client_id INTEGER, balance NUMBER);

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

DECLARE

CURSOR c_balances IS SELECT * FROM balances;

l_client_type VARCHAR2(100);

l_new_year_coeff NUMBER;

BEGIN

FOR rec_balance IN c_balances LOOP

– определяем тип клиента (активный / не активный)

l_client_type := getClientType(rec_balance); – вызов длится 1 минуту

CASE l_client_type

WHEN 'active' THEN l_new_year_coeff := 1.05;

WHEN 'non-actve' THEN l_new_year_coeff := 1.01;

END CASE;

UPDATE balances SET balance = rec_balance.balance*l_new_year_coeff

WHERE balances.client_id = rec_balance.client_id;

END LOOP;

COMMIT;

END;

Если в базе данных 100 клиентов, то анонимный блок будет выполняться 100 минут. Пусть на 30-й минуте с момента начала выполнения блока клиент одной из еще не считанной из курсора строк (для определенности, пусть 70-й по счету) вносит платеж и его баланс увеличивается на сумму платежа. Транзакция увеличения баланса этого клиента фиксируется (изменить 70-ю строку этой транзакции можно, строка не блокирована, так как ней пока еще не обращались в ходе «подарочного» расчета).

Когда на 70-й минуте курсор c_balances дочитает до этой строки, то обнаружится, что строка с момента начала выполнения SQL-запроса курсора была изменена зафиксированной транзакцией и для обеспечения согласованности чтения восстановит ее предыдущую версию (с балансом без нового платежа). Соответственно, после выполнения команды UPDATE для этого клиента будет проставлена увеличенная на один или пять процентов сумма старого (без учета нового платежа) баланса.

В теории баз данных это называется явлением пропавшего обновления (lost update phenomena) – пропало отражение на балансе нового платежа, оно было перезаписано ранее считанными данными. В данном случае более правильная формулировка – перезаписано данными, восстановленными по состоянию, которое было ранее. Ведь собственно считывалась-то 70-я строка из курсора уже после поступления нового платежа, просто в ходе ее считывания для обеспечения согласованности произошло восстановление старой версии строки.

Чтобы не сталкиваться с проблемой пропавшего обновления, необходимо на все время «подарочной» транзакции заблокировать данные балансов от изменений. Для этого в курсоре c_balances следует записать не просто SQL-запрос, а SQL-запрос с блокировкой отбираемых строк:

CURSOR c_balances IS SELECT * FROM balances FOR UPDATE;

Посмотрим, что изменится в этом случае. Для запросов с опцией FOR UPDATE блокировка на отбираемые строки накладывается в ходе открытия курсора командой OPEN, еще до считывания первой строки командой FETCH. Поэтому если курсор с FOR UPDATE успешно открылся, то это значит, что все отбираемые SQL-запросом курсора строки уже заблокированы. Если какие-то строки с балансами заблокированы другими активными транзакциями, то наш процесс во время открытия курсора с SELECT FOR UPDATE будет ждать снятия этих блокировок.

После успешного открытия курсора c_balances с FOR UPDATE строки всех балансов будут заблокированы нашей транзакцией и до снятия этой блокировки только она может их изменять. Все другие транзакции, которые будут вносить платежи и изменять (увеличивать) балансы, сами будут переходить в режим ожидания снятия блокировки с балансов, установленной нашей «подарочной» транзакцией.

Как только на всех балансах будут отражены подарочные суммы и цикл считывания и обработки строк курсора c_balances завершится, в конце анонимного блока есть команда COMMIT, фиксирующая транзакцию. После этого все транзакции новых платежей, поступавших во время выполнения «подарочной» транзакции и ожидавшие ее завершения, выйдут из режима ожидания и увеличат уже увеличенные «по-новогоднему» балансы на суммы своих платежей. Проблема пропавшего обновления не возникнет.

Конструкция WHERE CURRENT OF в DML-командах

Конструкция WHERE CURRENT OF предназначена для удаления или изменения той строки таблицы, которая является текущей в курсоре. Преимущество использования этой конструкции заключается в однократном задании критерия отбора данных в SQL.

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

Первый вариант программы (без использования WHERE CURRENT OF):

DECLARE

l_client_id INTEGER := 122329;

l_balance balances%ROWTYPE;

l_new_year_coeff NUMBER;

BEGIN

SELECT * INTO l_balance FROM balances

WHERE balances.client_id=l_client_id FOR UPDATE;

CASE getClientType(l_balance);

WHEN 'active' THEN l_new_year_coeff := 1.05;

WHEN 'non-actve' THEN l_new_year_coeff := 1.01;

END CASE;

UPDATE balances SET balance = l_balance.balance*l_new_year_coeff

WHERE balances.client_id=l_client_id;

END;

Второй вариант программы (c использованием WHERE CURRENT OF):

DECLARE

CURSOR c_balances IS SELECT * FROM balances

WHERE balances.client_id=122329 FOR UPDATE;

l_client_type VARCHAR2(100);

l_new_year_coeff NUMBER;

BEGIN

FOR rec_balance IN c_balances LOOP

CASE getClientType(rec_balance)

WHEN 'active' THEN l_new_year_coeff := 1.05;

WHEN 'non-actve' THEN l_new_year_coeff := 1.01;

END CASE;

UPDATE balances SET balance = rec_balance.balance *l_new_year_coeff

WHERE CURRENT OF c_balances;

END LOOP;

END;

В первом варианте видно, что один и тот же критерий отбора данных (WHERE balances.client_id=l_client_id) используется дважды – и в SELECT и в UPDATE. Ничего особо страшного в этом нет, но нарушается принцип DRY: одна и та же логика программируется в двух местах, и при внесении каких-либо изменений придется следить за синхронизацией этих участков кода.

При использовании конструкции WHERE CURRENT OF во втором варианте программы PL/SQL компилятор неявно добавил столбец ROWID в список столбцов, возвращаемых запросом курсора. Напомним, что ROWID (row identifier) является физическим указателем на место размещения строки таблицы в файле данных и переход по ROWID – самый быстрый способ обращения к строке.

Для команды PL/SQL UPDATE с конструкцией WHERE CURRENT OF компилятором PL/SQL условие во фразе WHERE соответствующего SQL-предложения UPDATE будет сформировано как ROWID=:B1. На каждой итерации цикла перед выполнением UPDATE с переменной :B1 связывается значение ROWID строки таблицы balances, считанной из курсора на этой итерации.

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

Время работы программ PL/SQL, как правило, определяется суммарным временем выполнения предложений SQL и обработки их результатов. В языке PL/SQL имеются средства, позволяющие оптимизировать выполнение предложений SQL. Применение этих средств по некоторым оценкам позволяет повысить общую производительность программ PL/SQL на порядок.