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

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