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
1
2
4
В SQL*Plus возникновение ошибок не влияет на статус транзакции, она остается активной и ее можно будет зафиксировать или отменить. Это же верно и для Quest SQL Navigator. Для других программ, выполняющих транзакции в Oracle, действия при возникновении ошибок могут отличаться. Например, для прикладного программного обеспечения (каких-нибудь бухгалтерских программ с GUI на C#, скриптов обсчета данных в базе на Python) программисты часто согласно требованиям бизнес-логики предусматривают отмену транзакции при возникновении первой же ошибки выполнения предложения SQL в ходе транзакции.
Точки сохранения для вызовов PL/SQL
Для вызовов программ PL/SQL внутри транзакции неявная точка сохранения также устанавливается перед передаваемым на выполнение блоком PL/SQL и в случае завершения выполнения блока с ошибкой, все внесенные им изменения данных будут автоматически отменены. То есть в этой части вызываемые блоки PL/SQL обрабатываются аналогично предложениям SQL.
Рассмотрим две ситуации
CREATE TABLE tab4 (a INTEGER);
Ситуация 1: необработанное исключение
Ситуация 2: обработанное исключение
все в рамках одной транзакции
первый блок (успешно)
SQL> BEGIN
2 INSERT INTO tab4 VALUES(1);
3 INSERT INTO tab4 VALUES(2);
4 END;
5 /
PL/SQL procedure successfully completed.
второй блок (с ошибкой)
SQL> BEGIN
2 INSERT INTO tab4 VALUES(3);
3 INSERT INTO tab4 VALUES('abc');
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 3
SQL> SELECT * FROM tab4;
A
1
2
все в рамках одной транзакции
первый блок (успешно)
SQL> BEGIN
2 INSERT INTO tab4 VALUES(1);
3 INSERT INTO tab4 VALUES(2);
4 END;
5 /
PL/SQL procedure successfully completed.
второй блок (успешно)
SQL> BEGIN
2 INSERT INTO tab4 VALUES(3);
3 INSERT INTO tab4 VALUES('abc');
4 EXCEPTION
5 WHEN OTHERS THEN NULL;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM tab4;
A
1
2
3
В первой ситуации (есть блок с необработанным исключением):
для второго блока PL/SQL при ошибке выполнения команды INSERT со значением abc произошла отмена до неявной точки сохранения перед INSERT и изменения, внесенные этим предложением, были отменены;
так как во втором блоке PL/SQL нет раздела обработки исключений, то с ошибкой завершился весь блок и произошла отмена к неявно установленной точке сохранения перед ним (в результате была отменено успешное добавление тройки на второй строке кода этого блока);
первый блок PL/SQL выполнился без ошибок; последовавшие потом ошибки второго блока, как и должно быть, никак на внесенные его командами изменения данных не повлияли и обе добавленные в ходе обработки первого блока строки есть в таблице (единица и двойка).
Во второй ситуации (исключение обработано):
точно так же для второго блока PL/SQL при ошибке выполнения предложения INSERT со значением abc произошла отмена к неявной точке сохранения перед INSERT и изменения, внесенные этим предложением, были отменены;
так как во втором блоке есть раздел обработки исключений с OTHERS-обработчиком, то вызов второго блока завершился успешно, без передачи ошибки вызывающей среде, поэтому добавление тройки не отменялось;
после выполнения обоих блоков в таблице tab4 будет три строкидве от первого блока и одна от второго.
Использование именованных точек сохранения
До неявно устанавливаемых внутри активных транзакций точкам сохранения отмена автоматически осуществляется ядром сервера Oracle после неуспешных программных вызовов. До явно устанавливаемых программистом именованным точкам сохранения отмена осуществляется согласно логике обработки ошибок и нестандартных ситуаций. Поэтому отмена до таких точек сохранения тоже должна явно инициироваться программистом. Можно сказать, что точки сохранения своими именами «размечают» активную транзакцию на участки, изменения на которых есть возможность отменить.