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

Когда наш процесс приступает к собственно изменению или удалению строки, найденной по критерию отбора в конструкции 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

– -

2a

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

SQL> COMMIT;

Commit complete.

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

SQL> SELECT * FROM test;

AT1 A

– -

2a

t0

t1

t2

t3

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

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

SQL> SELECT * FROM test;

AT1 A

– -

1a

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

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

SQL> SELECT * FROM test;

AT1 A