Язык PL/SQL

отсутствует активная транзакция, изменившая строку (то есть когда-то ранее строка была добавлена в базу данных какой-то давнишней активной транзакцией, потом строка, возможно, изменялась предложениями UPDATE других активных транзакций, но все эти транзакции в свое время были зафиксированы или отменены);

строка изменена активной транзакцией (неважно, выполняющейся или выполненной – главное, что строка изменена, и эти изменения не зафиксированы и не отменены).

В литературе на русском языке часто встречаются фразы вроде «Запрос видит только зафиксированные данные на SCN 10023», представляющие собой кальковый перевод фраз вида «The query only sees committed data with respect to SCN 10023». Дело в том, что более правильные с точки зрения терминологии выражения выглядят довольно громоздко. Мы тоже будем дальше использовать эти ставшие общепринятыми понятия и выражения, просто приведем их определения:

зафиксированными данными называются данные, измененные зафиксированной транзакцией (изменения данных стали постоянными);

предложение SQL «видит» строки, если они используются (не игнорируются) им в ходе своего выполнения – строки участвуют в определении истинности критерия отбора из фразы WHERE и могут попадать в результирующую выборку.

Обращение к данным сервер Oracle осуществляет в одном из двух режимов:

обращение в текущем режиме (current mode, db block gets), при котором происходит обращение к строкам в их текущем состоянии, то есть в состоянии, в котором они находятся прямо сейчас (right now);

обращение в режиме согласованного чтения (consistent read mode, consistent gets), при котором происходит обращение к строкам, находящимся по состоянию на некоторый момент времени, то есть к предыдущим версиям строк.

В Oracle любой SQL-запрос SELECT видит только зафиксированные данные по состоянию на начало своего выполнения. Если другие транзакции уже после начала выполнения SQL-запроса сделают изменения в данных, и даже если эти изменения будут зафиксированы, то все равно SQL-запрос увидит версию данных по состоянию на момент начала его выполнения. Это и называется «согласованным чтением» (consistency read) на уровне отдельных предложений SQL. Часто версии данных по состоянию на некоторый момент времени в прошлом называют старыми данными.

Все сказанное верно и для критерия отбора из конструкции WHERE предложений DELETE, UPDATE и INSERT SELECT. Для удаления и изменения отбираются строки по состоянию на момент начала выполнения этих предложений.

«Под капотом» сервера Oracle для обеспечения корректного выполнения транзакций, согласованного чтения и вот этого всего имеются следующие специальные механизмы:

сегменты отката, обеспечивающие версионность данных;

система блокировок.

Блокировки

Наличие системы блокировок позволяет обеспечить корректное изменение одних и тех же данных.

Блокировкой (lock) называется средство организации доступа к совместно используемому ресурсу. Наложить на ресурс блокировку – это ограничить возможности других по работе с этим ресурсом. В Oracle есть несколько видов блокировок, мы рассмотрим, как работают самые распространенные блокировки строк таблиц транзакциями (блокировки TX).

Общие правила работы блокировок TX в Oracle выглядят так:

блокировки накладывают и снимают транзакции;

наложенную транзакцией блокировку может снять только она сама в ходе завершения транзакции командами COMMIT или ROLLBACK;

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

чтобы изменить или удалить строку, ее сначала надо заблокировать;

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

существует специальная форма SQL-запроса SELECT FOR UPDATE, которая накладывает блокировки на отбираемые по критерию отбора строки;

блокировки являются атрибутами самих строк – чтобы определить, есть ли на строке блокировка, к ней необходимо обратиться.

Для новых строк, добавленных в таблицу предложением INSERT, блокировка TX накладывается транзакцией, выполнившей это предложение.

При изменении строк предложением UPDATE к ним обращаются дважды:

поиск строк-кандидатов для изменений по условию в конструкции WHERE, который осуществляется в режиме согласованного чтения;

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

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

Когда наш процесс приступает к собственно изменению или удалению строки, найденной по критерию отбора в конструкции WHERE, сначала он попытается наложить на эту строку блокировку TX. При этом возможны два исхода в зависимости от того, есть ли на этой строке блокировка, установленная другой (чужой) активной транзакцией (наличие или отсутствие такой блокировки определяется в ходе обращения к строке):

если на строке нет чужой блокировки, то наша транзакция накладывает на нее свою блокировку;

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

В Oracle вся схема обработки изменений построена исходя из оптимистичного предположения, что в большинстве случаев чужих блокировок на строках не будет, то есть сам процесс изменения, как правило, происходит по схеме «нашел строку – заблокировал ее – изменил – стал искать следующую строку».

После успешного наложения блокировки на строку, наша транзакция изменяет значения ее столбцов в соответствии с тем, что написано в конструкции SET предложения UPDATE. После изменения строки наша блокировка на ней остается. Процесс пойдет по таблице дальше, отбирая строки по критерию из WHERE в режиме согласованного чтения, блокируя и меняя их в текущем режиме. В конце концов, таблица кончится, и процесс оставит за собой ее измененные строки с блокировкой на каждой. Так будет для каждого входящего в нашу транзакцию предложения SQL INSERT, UPDATE, DELETE. Никакие другие транзакции изменять заблокированные нами строки не смогут – при своих попытках заблокировать строки пере изменением все они будут переходить в режим ожидания с подпиской на оповещения о снятии наших блокировок. Эти оповещения придут к ним, как только наша транзакция будет зафиксирована или отменена и блокировки снимутся.

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

Уровни изоляции транзакции

Каждая транзакция в Oracle выполняется на заданном уровне изоляции, который не меняется на всем протяжении жизни транзакции. Уровень изоляции (isolation level) определяется как степень исключения взаимного влияния транзакций друг на друга.

Всего в Oracle поддерживается три уровня изоляции транзакций:

чтение зафиксированных данных (read committed) – любое выполняемое в транзакции предложение SQL видит только зафиксированные данные;

только чтение (read only) – в транзакции допускаются только SQL-запросы SELECT, которые видят только зафиксированные данные по состоянию на начало транзакции (все предложения INSERT, UPDATE и DELETE на этом уровне изоляции завершаются с ошибкой);

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

Уровень изоляции read only используется, как правило, в отчетных системах для того, чтобы результаты нескольких подряд выполняющихся SQL-запросов соответствовали одному и тому же состоянию базы данных, которое было на начало транзакции. Этот сценарий для программ PL/SQL используется не часто. Использование в программах PL/SQL уровня изоляции serializable на практике встречается тоже очень редко. По этим причинам мы не будем рассматривать уровни изоляции read only и serializable.

Уровень чтения зафиксированных данных (read committed) используется в Oracle по умолчанию. То есть, если не начать транзакцию командой SET TRANSACTION с указанием одного из двух других уровней изоляции, то будет использоваться именно read commited. Этот уровень изоляции транзакций вполне соответствует представлениям на бытовом уровне о параллельной работе нескольких людей с одними и теми же данными:

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

внес изменения и зафиксировал их – пусть их увидят другие люди;

внес изменения и потом их отменил – другим людям такие изменения видеть абсолютно незачем.

естественно, что сам свои текущие изменения видишь всегда и без их подтверждения.

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

Отдельный SQL-запрос в ходе своего выполнения не испытывает воздействия со стороны других транзакций (не видит внесенные ими изменения), а вот состоящая из таких операторов вся транзакция с уровнем изоляции read commited согласно определению уровня изоляции такому воздействию подвержена. Если в ходе транзакции один SQL-запрос выполнялся и проверял свой критерий отбора на одном «срезе» базы данных из зафиксированных данных, то через некоторое время в этой же транзакции другой SQL-запрос может увидеть отличающийся «срез» с изменениями, сделанными за прошедшее время другими зафиксированными транзакциями. Логично – были сделаны подтвержденные изменения, их всем следует увидеть. Результирующая выборка будет при втором выполнении другой. Для иллюстрации всего вышесказанного рассмотрим поведение параллельно выполняющихся транзакций в режиме изоляции read commited, изменяя и читая строки в таблице test:

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

t

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

Начальное заполнение:

SQL> SELECT * FROM test;

AT1 A

– -

1 a

Начальное заполнение:

SQL> SELECT * FROM test;

AT1 A

– -

1 a

Пример 1:

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

SQL> UPDATE test set at1=2;

1 row updated.

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

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

SQL> SELECT * FROM test;

AT1 A

– -

2 a

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

SQL> COMMIT;

Commit complete.

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

SQL> SELECT * FROM test;

AT1 A

– -

2 a

t0

t1

t2

t3

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

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

SQL> SELECT * FROM test;

AT1 A

– -

1 a

– изменения зафиксированной

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

SQL> SELECT * FROM test;

AT1 A

– -

2 a

Пример 2:

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

SQL> UPDATE test set at1=3;

1 row updated.

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

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

SQL> SELECT * FROM test;

AT1 A

– -

3 a

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

SQL> ROLLBACK;

Rollback complete.

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

SQL> SELECT * FROM test;

AT1 A

– -

2 a

t0

t1

t2

t3

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

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

SQL> SELECT * FROM test;

AT1 A

– -

2 a

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

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

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

SQL> SELECT * FROM test;

AT1 A

– -

2 a

Пример 3:

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

SQL> UPDATE test set at2=4;

1 row updated.

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

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

SQL> COMMIT;

Commit complete.

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

SQL> SELECT * FROM test;

AT1 A

– -

4 a

t0

t1

t2

t3

t4

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

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

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

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

SQL> UPDATE test set at2=5;

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

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

1 row updated.

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

SQL> SELECT * FROM test;

AT1 A

– -

5 a

Транзакции и средства работы с ними являются довольно сложно понимаемым учебным материалом. В том числе, это вызвано и тем, что в книгах по теории баз данных по этой теме написано одно, в разных имеющихся на рынке СУБД (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

–

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 после неуспешных программных вызовов. До явно устанавливаемых программистом именованным точкам сохранения отмена осуществляется согласно логике обработки ошибок и нестандартных ситуаций. Поэтому отмена до таких точек сохранения тоже должна явно инициироваться программистом. Можно сказать, что точки сохранения своими именами «размечают» активную транзакцию на участки, изменения на которых есть возможность отменить.

Приведем правила работы с именованными точками сохранения:

область видимости точки сохранения не ограничивается блоком PL/SQL, в котором она установлена (точки сохранения «живут» на уровне всей транзакции, в частности, вообще можно установить точку сохранения в PL/SQL, а выполнить отмену до нее в SQL и наоборот);