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

– -

2a

Пример 2:

– начало транзакции

SQL> UPDATE test set at1=3;

1 row updated.

– свои изменения видны

– (в т.ч.) незафиксированные

SQL> SELECT * FROM test;

AT1 A

– -

3a

– транзакция отменяется

SQL> ROLLBACK;

Rollback complete.

– отмененные изменения не видны

SQL> SELECT * FROM test;

AT1 A

– -

2a

t0

t1

t2

t3

– изменения чужой активной

– транзакции не видны

SQL> SELECT * FROM test;

AT1 A

– -

2a

– о том, что были какие-то

– отмененные изменения,

– никто и не узнает никогда

SQL> SELECT * FROM test;

AT1 A

– -

2a

Пример 3:

– начало транзакции в сессии 1

SQL> UPDATE test set at2=4;

1 row updated.

– транзакция фиксируется

– блокировка со строки снимается

SQL> COMMIT;

Commit complete.

– видна зафиксированная строка

SQL> SELECT * FROM test;

AT1 A

– -

4a

t0

t1

t2

t3

t4

– начало транзакции в сессии 2

– серверный процесс переходит

– в режим ожидания снятия блокировки

– SQL*Plus «повисает»

SQL> UPDATE test set at2=5;

– получаем сообщение, «отвисаем»

– блокируем строку и меняем ее

1 row updated.

– видны свои изменения

SQL> SELECT * FROM test;

AT1 A

– -

5a

Транзакции и средства работы с ними являются довольно сложно понимаемым учебным материалом. В том числе, это вызвано и тем, что в книгах по теории баз данных по этой теме написано одно, в разных имеющихся на рынке СУБД (Oracle, Microsoft SQL Server, IBM DB2) реализовано другое, причем в книгах про эти СУБД описано далеко не все. Для понимания того, как же все это устроено и работает в Oracle, авторы настоятельно рекомендуют прочитать подряд идущие главы «Блокировка и защелкивание данных», «Параллелизм и многоверсионность», «Транзакции», «Повтор и отмена» книги Томаса Кайта «Oracle для профессионалов. Архитектура, методики программирования и основные особенностей версий 9i, 10g, 11g и 12c» – всего около двухсот страниц мелким шрифтом. Текст Кайта предельно понятен и реально просветляет.

Команды управления транзакциями

В языке PL/SQL имеются следующие команды для управления транзакциями, соответствующие аналогичным предложениям SQL (напомним, что по этим командам компилятор PL/SQL размещает в байт-коде предложения SQL):

SET TRANSACTION – устанавливает уровень изоляции транзакции;

COMMIT – фиксирует транзакцию (сохраняет все внесенные транзакцией изменения данных и снимает все наложенные транзакцией блокировки);

ROLLBACK – отменяет транзакцию (отменяет все внесенные транзакцией изменения данных и снимает все наложенные транзакцией блокировки);

SAVEPOINT – устанавливает точку сохранения (точкой сохранения называется именованный номер изменения в транзакции, до которого может быть выполнена отмена изменений);

ROLLBACK TO SAVEPOINT – отменяет все изменения, внесенные транзакцией после установки указанной точки сохранения и снимает блокировки (сама транзакция при этом остается активной, то есть является транзакцией, которая начата, но и не зафиксирована и для нее не выполнена отмена);

LOCK TABLE – блокирует указанную таблицу в заданном режиме.

Приведем пример использования команд для управлениями транзакциями в PL/SQL.

CREATE TABLE tab3 (at1 INTEGER);

INSERT INTO tab3 VALUES(7);

Сначала запускаем анонимный блок в SQL*Plus первой сессии, она «засыпает» на десять секунд («засыпание» обеспечивает процедура SLEEP встроенного пакета DBMS_LOCK). Пока это время не прошло, переключаемся в SQL*Plus второй сессии и запускаем другой анонимный блок, который отрабатывает мгновенно. Через десять секунд «проснется» первая сессия.

SQL*Plus первой сессии

SQL*Plus второй сессии

SQL> DECLARE

2 l_at1 tab3.at1%TYPE;

3 BEGIN

4 SET TRANSACTION READ ONLY;

5 DBMS_LOCK.sleep(10);

6 SELECT at1 INTO l_at1 FROM tab3;

7 DBMS_OUTPUT.PUT_LINE(l_at1);

8 COMMIT;

9 END;

10 /

7

PL/SQL procedure successfully completed

SQL> DECLARE

2 l_at1 tab3.at1%TYPE;

3 BEGIN

4 UPDATE tab3 SET at1=8;

5 COMMIT;

6 SELECT at1 INTO l_at1 FROM tab3;

7 DBMS_OUTPUT.PUT_LINE(l_at1);

8 END;

9 /

8

PL/SQL procedure successfully completed

Видно, что выставленный для первой транзакции уровень изоляции READ ONLY обеспечил чтение старой версии данных (прочитана семерка), несмотря на то, что данные до их чтения изменены (на восьмерку) зафиксированной транзакцией второй сессии.

Следует помнить, что транзакция состоит из предложений SQL. Вызовы программ на PL/SQL внутри транзакции следует рассматривать как промежуточные. Можно в SQL*Plus начать транзакцию выполнением SQL-предложения UPDATE, после него вызвать программу на PL/SQL, которая выполнит 5 предложений SQL из своего байт-кода, потом подождать полчаса, потом выполнить еще пару SQL-предложений INSERT, потом опять вызвать программу PL/SQL. Все это время транзакция будет являться активной и с точки зрения сервера выполнит 1+5+2+5 предложений SQL. То, что часть из них была выполнена из программ PL/SQL, значения не имеет. Зафиксировать или отменить транзакцию также можно как в программе на PL/SQL, так и в «чистом» SQL.

Точки сохранения для предложений SQL

Выполнение предложений SQL сопровождается установкой ядром Oracle неявных точек сохранения (savepoints) перед каждым предложением по следующей трехэтапной схеме:

неявно SET SAVEPOINT implicit_savepoint;

выполняется предложение SQL, например, UPDATE;

IF SQLerror THEN отмена до implicit_savepoint;

При отмене до неявно установленной точки сохранения отменяются изменения, которые сделаны в ходе обработки предложения SQL до возникновения ошибки. Например, пусть предложение UPDATE изменило две строки, а на третьей произошла ошибка – нарушено ограничение целостности.

CREATE TABLE tab7 (at1 INTEGER CHECK ( at1<=4));

INSERT INTO tab7 VALUES (2);

INSERT INTO tab7 VALUES (3);

INSERT INTO tab7 VALUES (4);

– установка неявной точки сохранения

SQL> UPDATE tab7 SET at1=at1+1;

UPDATE tab7 SET at1=at1+1

*

ERROR at line 1:

ORA-02290: check constraint (U1.SYS_C0012475) violated

AT1 пояснение

–

2 +1 = 3 ( 3<=4, OK)

3 +1 = 4 ( 4<=4, OK)

4 +1 = 5 ( 5>4, Error, отмена до точки сохранения)

Ошибки предложений SQL в транзакции

В языке SQL в рамках одной транзакции изменения, внесенные одними предложениями SQL, не отменяются из-за ошибок других предложений SQL. Если из SQL*Plus в рамках одной транзакции выполнить пять предложений INSERT, из которых два завершатся ошибкой, то в таблице все равно будет три новые строки. Успешное добавление этих трех строк не отменится, они не пропадут, а останутся «изменениями, внесенными активной транзакцией».

SQL> CREATE TABLE transaction_test (a INTEGER);

Table created.

SQL> INSERT INTO transaction_test VALUES(1);

1 row created.

SQL> INSERT INTO transaction_test VALUES(2);

1 row created.

SQL> INSERT INTO transaction_test VALUES(3/0);

INSERT INTO transaction_test VALUES(3/0)

*

ERROR at line 1:

ORA-01476: divisor is equal to zero

SQL> INSERT INTO transaction_test VALUES(4);

1 row created.

SQL> INSERT INTO transaction_test VALUES(5/0);

INSERT INTO transaction_test VALUES(5/0)

ERROR at line 1:

ORA-01476: divisor is equal to zero

SQL> SELECT * FROM transaction_test;

A

–