В 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):