Транзакции

1. Подготовка таблицы (в конце – явное указание окончания транзакции с фиксацией изменений в файле):

CREATETABLE Tickets (

ticket_id NUMBERNOTNULLCONSTRAINT ticket_pk PRIMARYKEY,

ticket_name VARCHAR2(25) NOTNULLCONSTRAINT ticket_name_uq UNIQUE,

ticket_count NUMBER

);

INSERTINTO Tickets VALUES (1, 'Mariupol-Donetsk', 100);

INSERTINTO Tickets VALUES (2, 'Mariupol-Kharkov', 90);

COMMIT;

2. Блокирующая транзакция (подходит для небольшого числа (2-3) касс: кассир открывает форму подготовки билета и за короткое время вводит необходимые данные, в это время соответствующая строка таблицы заблокирована для просмотра и изменения из других сессий):

--Запрашиваем данные из некоторой строки,

--одновременно блокируя эту строку

SELECTticket_count

FROM Tickets

WHERE ticket_name = 'Mariupol-Donetsk'

FORUPDATENOWAIT;

--На основании полученной и дополнительно введенной информации

--вносим изменения в блокированную строку и разблокируем ее

UPDATE Tickets

SET ticket_count = 99

WHERE ticket_name = 'Mariupol-Donetsk';

COMMIT;

3. Неблокирующая транзакция (подходит для интернет-магазинов с большим числом клиентов: предварительно пользователь регистрируется, вводит свои личные данные, затем выбирает нужный билет, и тут же оформляется продажа билета – одной командой на основании выбора и уже имеющейся необходимой информации о клиенте, на имя которого оформляется билет):

DECLARE

--Курсор, по которому будут получены данные из нужной записи

CURSOR c IS

SELECT *

FROM Tickets

WHERE ticket_name = 'Mariupol-Donetsk'

FORUPDATENOWAIT;

--Переменная типа, способного принять соответствующую запись

tmp_rowsc%ROWTYPE;

BEGIN

--Открываем курсор

OPENc;

--Записываем данные из курсора в техническую переменную,

--чтобы " перемотать" курсор на первую строку, если он указывает

--на выборку из нескольких строк

FETCHcINTOtmp_rows;

--Выполняем обновление для записи, на которую указывает курсор

UPDATE Tickets

SET ticket_count = 99

WHERECURRENTOFc;

--Возвращаем клиенту число обработанных строк

Dbms_Output. put_line(SQL%ROWCOUNT);

--Фиксируем изменения в файл, транзакция успешно завершается

COMMIT;

--Если транзакция не удалась - отправляем клиенту сообщение об ошибке

EXCEPTIONWHENOTHERSTHEN

Dbms_Output. put_line(SQLERRM);

END;

4. Повышение транзакций производится только в явном виде (некоторые СУБД выполняют это автоматически, например, SQLServerлокирует всю таблицу для других сеансов, если текущий сеанс в одной транзакции обновляет более 20% строк). Если предполагаемое время построения отчета невелико, и в это время допускается приостановка оперативных работ с БД, можно залокировать нужные таблицы:

LOCK TABLE TestDB IN EXCLUSIVE MODE;

Теперь из данного сеанса можно сделать обновление данных в строке:

UPDATE Emp SET ename = 'SMITH-2' WHERE empno = 7369;

Не делайте коммит. Откройте окно еще одного сеанса и попробуйте выполнить:

UPDATE Emp SET ename = ' SMITH' WHERE empno = 7369;

В доступе к таблице будет отказано, команда повиснет. Нажмите пиктограмму «красная молния» (break)и попробуйте получить доступ к строке с немедленным ответом о результате попытки:

UPDATE Emp SET ename = 'SMITH' WHERE empno = 7369;

Теперь закоммитим транзакцию в первом сеансе и вернемся во второй сеанс. Таблица доступна для изменений.

5. Если предполагается длительное построение отчета (минуты, часы, сутки), то нужно предоставить остальным сеансам возможность работать в оперативном режиме. Для этого стороне сеанса построения отчета нужно предоставить моментальный снимок БД, а остальные будут при необходимости менять строки в undo-сегментах:

- в первом сеансе откройте транзакцию «только для чтения»:

SET TRANSACTION READ ONLY;

- попробуйте выполнить обновление строки;

- перейдите в другой сеанс, попробуйте выполнить обновление строки и просмотрите содержимое таблицы;

- перейдите в первый сеанс, просмотрите содержимое таблицы;

- в первом сеансе выполните откат, просмотрите содержимое таблицы;

6. Если во время построения отчета некоторые данные нужно изменить, но так, чтобы в исходных таблицах изменения не фиксировались, то нужно предоставить undo-сегменты и для стороны основного сеанса:

- в первом сеансе открываем транзакцию с уровнем изоляции сериализации:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

- выполняем обновление, но не коммитим:

UPDATE Emp SET ename = 'Vasia-main' WHERE empno = 7369;

UPDATE Emp SET ename = 'Petia-main' WHERE empno = 7499;

- пытаемся выполнить те же обновления в другом сеансе;

- пытаемся обновить другую строку во втором сеансе, коммитим:

UPDATE Emp SET ename = 'Masha-sec' WHERE empno = 7521;

- читаем изменившиеся строки в обоих сеансах, коммитим транзакцию в первом сеансе, повторяем чтение в обоих сеансах;

7. В предыдущем примере если второй сеанс закоммитит свои изменения, а первый сеанс попытается внести изменения в ту же строку, то получит ошибку запрета изменений. Чтобы избежать этой проблемы, воспользуемся временной таблицей, в которую будем помещать данные, измененные по требованиям построения отчета:

CREATE GLOBAL TEMPORARY TABLE MyReport

(

id NUMBER,

content VARCHAR2(2000),

sal NUMBER

) ON COMMIT DELETE ROWS;

Другие сеансы не будут иметь доступа к данной таблице. После коммита строки таблицы будут удаляться. Заполним таблицу отчета:

create or replace function fn_build_report(id number) return varchar2 is

Result varchar2(2000);

begin

FOR r IN (SELECT ename FROM Emp WHERE deptno = id)

LOOP

Result: = Result || '; ' || r. ename;

END LOOP;

return(Result);

end fn_build_report;

INSERT INTO MyReport (id, content, sal)

(

SELECT deptno, fn_build_report(deptno), SUM(sal)

FROM Emp

GROUP BY deptno

);

-------------------------------------------------------------------

--высокопроизводительный вариант, работающий на версиях СУБД до 11-й

MERGE INTO MyReort AS mr USING Emp ON (mr. id = Emp. Deptno)

WHEN NOT MATCHED THEN

INSERT (id, content, sal)

VALUES (Emp. Deptno, Emp. Ename || '; ', Emp. sal)

WHEN MATCHED THEN

UPDATE SET mr. content = mr. content || Emp. Ename || '; '

, mr. sal = mr. sal + Emp. sal;

8. Оператор commit можно дополнять уточняющим выражением: writewait (клиент получит ответ только после окончания сохранения данных в файл) writenowait (клиент получит ответ сразу, хотя данные к этому моменту могут быть не сохранены). Иначе СУБД сама принимает решение, каким из двух способов завершить транзакцию. Применим безопасный вариант завершения с откатом к точке сохранения в случае удачи операции обновления и неудачи операции вставки:

/*Неименованный блок*/

DECLARE

--объявляем переменную того типа,

--который задан у поля emp. empno

emp_id emp. empno%TYPE;

BEGIN

--начинаем транзакцию

--здесь можно добавить указание на тип транзакции,

--отличный от типа по умолчанию: только чтение или

--разрешение изменений undo-сегменте;

--инициализируем переменную

emp_id: = 7369;

--пытаемсяобновитьстроку

UPDATE emp SET ename = 'SMITH-5' WHERE empno = emp_id;

--если удалось - создаем точку сохранения

SAVEPOINT do_insert;

--пытаемся выполнить вставку строки

INSERT INTO emp VALUES (7934, 'vasia2', 'CLERK', 7782, '23-JAN-82', 1300, NULL, 10);

--INSERT INTO emp VALUES (7934, 'vasia2', 'CLERK', 911, '23-JAN-82', 1300, NULL, 10);

--выводимчислообработаныхстрок

Dbms_Output. put_line(SQL%ROWCOUNT);

--Фиксируем изменения в файл, транзакция успешно завершается,

--клиент получит ответ только после окончания сохранения

--данныхвфайлБД

COMMIT WRITE WAIT;

EXCEPTION

--в случае ошибки вставки строки отменяем только это действие

--WHEN DUP_VAL_ON_INDEX THEN

WHEN OTHERS THEN

Dbms_Output. put_line(SQLERRM);

ROLLBACK TO do_insert;

END;

9. Задание: создать две таблицы – журнал попыток перевода денежных сумм и журнал успешных переводов. В первом журнале одной строкой фиксируется факт попытки транзакции перевода суммы с одного счета на другой, а во втором – строка перечисления и строка получения средств. Настроить правильное управление процессом сохранения информации в эти таблицы.