Перейти к содержимому

01 Занятие 01: Многоверсионность / Изоляция

Исходная страница в нативном Markdown: Многоверсионность / Изоляция
Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: dba2_01_mvcc_isolation.html Archive: DBA2-handouts-16.zip Страниц PDF: 22

Page 1: Многоверсионность / Изоляция

Авторские права

© Postgres Professional, 2017–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Темы

  • Транзакции и их свойства
  • Уровни изоляции в стандарте SQL
  • Особенности реализации PostgreSQL

Page 3: Транзакции и их свойства

Транзакция — последовательность операций,

  • которые переводят базу данных из одного корректного состояния в другое корректное
    • Согласованность
  • при условии, что транзакция выполнена полностью
    • Атомарность
  • и без помех со стороны других транзакций
    • Изоляция
    • атомарность: транзакция либо выполняется полностью,
    • либо не оставляет никаких следов
    • согласованность определяется ограничениями целостности
    • и семантикой приложения
    • неполная изоляция приводит к нарушению корректности (аномалиям)
    • при конкурентном выполнении транзакций

Комментарии преподавателя

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

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

Идеальная изоляция транзакций гарантирует, что на работу приложения не окажут влияния никакие другие конкурентно выполняющиеся транзакции. Однако если ослабить требования к изоляции, то транзакция, которая корректно выполнялась в базе данных в одиночестве, может выдавать некорректные результаты при наличии других транзакций — из-за того, что при выполнении операторы разных транзакций могут чередоваться. Такие некорректные ситуации называются аномалиями.

Реализация полной изоляции — сложная задача, сопряженная с уменьшением пропускной способности системы. На практике как правило применяется именно ослабленная изоляция, и поэтому важно понимать, какие это влечет последствия.

Четвертое свойство ACID — долговечность — мы рассмотрим позже, в теме «Журналирование».

Page 4: Read Uncommitted

Должен предотвращать аномалии

  • потерянное изменение:
  • транзакция может перезаписать изменения других транзакций,
  • зафиксированных после ее начала
  • Не реализован в PostgreSQL
    • работает как Read Committed

Комментарии преподавателя

Стандарт SQL допускает четыре уровня изоляции, определяемые в терминах аномалий, которые допускаются при конкурентном выполнении транзакций на этом уровне.

Самый слабый уровень — Read Uncommitted, который (как следует из названия) позволяет увидеть даже незафиксированные данные.

Согласно стандарту на этом уровне должна предотвращаться единственная аномалия:

● Потерянное изменение (lost update). Если после начала транзакции T1 другая транзакция T2 изменила и зафиксировала строки, транзакция T1 может перезаписать эти изменения.

Этот уровень вводился как требующий наименьших накладных расходов, но PostgreSQL может без потери эффективности работать на более строгом уровне. Поэтому в PostgreSQL такой уровень не реализован и говорить о нем мы больше не будем.

Page 5: Read Committed

Должен предотвращать аномалии

  • потерянное изменение
  • грязное чтение:
  • запрос видит незафиксированные изменения других транзакций
  • Реализация в PostgreSQL
    • используется по умолчанию

Комментарии преподавателя

Каждый следующий уровень изоляции строже, чем предыдущий. Поэтому уровень Read Committed должен предотвращать не только потерянные изменения, но и еще одну аномалию:

● «Грязное» чтение (dirty read). Транзакция T1 может читать строки, измененные, но еще не зафиксированные, транзакцией T2. Отмена изменений (ROLLBACK) в T2 приведет к тому, что T1 прочитает данные, которых никогда не существовало.

Существует много других аномалий, которые допускаются на уровне Read Committed. Разработчик должен всегда помнить о возможных проблемах и при необходимости вручную использовать блокировки.

В PostgreSQL (как и во многих других СУБД) именно этот уровень изоляции используется по умолчанию — как компромисс между строгостью изоляции и эффективностью.

Заметим, что в PostgreSQL на уровне Read Committed можно потерять изменения — такой пример будет показан в демонстрации.

Page 6: Управление уровнем изоляции

Для демонстрации мы будем использовать отдельную базу данных для каждой темы.

=> CREATE DATABASE arch_isolation;
CREATE DATABASE
=> \c arch_isolation
You are now connected to database "arch_isolation" as user "student".

Уровни изоляции посмотрим на примере с таблицей, представляющей состояние светофора:

=> CREATE TABLE lights(
  id integer GENERATED ALWAYS AS IDENTITY,
  lamp text,
  state text
);
CREATE TABLE

Это будет пешеходный светофор с двумя лампочками:

=> INSERT INTO lights(lamp,state) VALUES
    ('red', 'on'), ('green', 'off');
INSERT 0 2
=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)

Один способ установить уровень изоляции — команда SET TRANSACTION, выполненная в начале транзакции:

=> BEGIN;
BEGIN
=> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET

Проверить текущий уровень можно, посмотрев значение параметра:

=> SHOW transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)
=> COMMIT;
COMMIT

А можно указать уровень прямо в команде BEGIN:

=> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
=> COMMIT;
COMMIT

По умолчанию используется уровень Read Committed:

=> SHOW default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1 row)

Page 7: Page 7

Так что если этот параметр не менялся, можно не указывать уровень явно.

=> BEGIN;
BEGIN
=> SHOW transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)
=> COMMIT;
COMMIT

Read Committed и грязное чтение

Попробуем прочитать «грязные» данные. В первой транзакции гасим красный свет:

=> BEGIN;
BEGIN
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
UPDATE 1

Начнем второй сеанс.

student$ psql arch_isolation

В нем откроем еще одну транзакцию с тем же уровнем Read Committed.

=> BEGIN;
BEGIN
=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)

Вторая транзакция не видит незафиксированных изменений. Отменим изменение.

=> ROLLBACK;
ROLLBACK
=> ROLLBACK;
ROLLBACK

Read Committed и чтение зафиксированных изменений

Мы проверили, что транзакция не видит незафиксированных изменений. Посмотрим, что будет при фиксации.

=> BEGIN;
BEGIN
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
UPDATE 1
=> BEGIN;
BEGIN
=> SELECT * FROM lights ORDER BY id;

Page 8: Page 8

 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)

Пока не видно.

=> COMMIT;
COMMIT

А теперь?

=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)
=> COMMIT;
COMMIT

Итак, в режиме Read Committed операторы одной транзакции видят зафиксированные изменения других транзакций. Заметьте, что при этом запрос, выполненный повторно в той же транзакции, может выдать другой результат. Можно ли увидеть изменения, зафиксированные в процессе выполнения одного оператора? Проверим. Сейчас обе лампочки погашены. Запустим долгий запрос и, пока он работает, включим свет во втором сеансе:

=> SELECT *, pg_sleep(2) FROM lights ORDER BY id;
=> UPDATE lights SET state = 'on';
UPDATE 2
 id | lamp  | state | pg_sleep
----+-------+-------+----------
  1 | red   | off   |
  2 | green | off   |
(2 rows)

Итак, если во время выполнения оператора другая транзакция успела зафиксировать изменения, то они не будут видны. Оператор видит данные в таком состоянии, в котором они находились на момент начала его выполнения. Однако если в запросе вызывается функция с категорией изменчивости volatile, выполняющая собственный запрос, то такой запрос внутри функции будет возвращать данные, не согласованные с данными основного запроса.

=> CREATE FUNCTION get_state(lamp text) RETURNS text
LANGUAGE sql VOLATILE
RETURN (SELECT l.state FROM lights l WHERE l.lamp = get_state.lamp);
CREATE FUNCTION

Повторим эксперимент, но теперь запрос будет использовать функцию:

=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;
=> UPDATE lights SET state = 'off';
UPDATE 2
 id | lamp  | state | get_state | pg_sleep
----+-------+-------+-----------+----------
  1 | red   | on    | on        |
  2 | green | on    | off       |
(2 rows)

Правильный вариант здесь — объявить функцию с категорией изменчивости stable.

=> ALTER FUNCTION get_state STABLE;
ALTER FUNCTION

Page 9: Page 9

=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;
=> UPDATE lights SET state = 'on';
UPDATE 2
 id | lamp  | state | get_state | pg_sleep
----+-------+-------+-----------+----------
  1 | red   | off   | off       |
  2 | green | off   | off       |
(2 rows)

Вывод: внимательно следите за категорией изменчивости функций на уровне изоляции Read Committed. Со значениями «по умолчанию» легко получить несогласованные данные.

Read Committed и потерянные изменения

Что происходит при попытке изменения одной и той же строки двумя транзакциями? Сейчас обе лампочки включены. Первая транзакция выключает красную лампочку, но пока не завершается.

=> BEGIN;
BEGIN
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
UPDATE 1

Вторая транзакция видит, что обе лампочки горят…

=> BEGIN;
BEGIN
=> SELECT * FROM lights;
 id | lamp  | state
----+-------+-------
  2 | green | on
  1 | red   | on
(2 rows)

… меняет состояние каждой лампочки…

=> UPDATE lights
SET state = CASE WHEN state = 'on' THEN 'off' ELSE 'on' END;

… но зависает, ожидая завершения первой.

=> COMMIT;
COMMIT
UPDATE 2

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

=> COMMIT;
COMMIT
=> SELECT * FROM lights;
 id | lamp  | state
----+-------+-------
  2 | green | off
  1 | red   | on
(2 rows)

Почему так получилось? Команда UPDATE во второй транзакции блокирует строки таблицы по очереди. Сначала блокируется зеленая лампочка и изменяется ее состояние, а затем команда ждет снятия блокировки красной лампочки первой транзакцией. При этом команда во второй транзакции не должна видеть изменений, сделанных после начала ее выполнения. С другой стороны, она не должна потерять изменения, зафиксированные другими транзакциями. Поэтому после снятия блокировки она перечитывает строку, которую пытается обновить.

Page 10: Page 10

В итоге, первая транзакция выключает красную лампочку, а вторая снова включает ее.

  • В результате вторая транзакция смогла отработать успешно за счет того, что она видела несогласованные данные: часть — на один момент времени, часть — на другой. Но если изменение выполняется не в одной команде SQL, то обновление будет потеряно.
=> BEGIN;
BEGIN

Первая транзакция переводит красную лампочку в мигающий режим. Строка при этом блокируется:

=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';
UPDATE 1

Во второй транзакции клиент видит, что красная лампочка горит…

=> BEGIN;
BEGIN
=> SELECT state AS old_state FROM lights WHERE lamp = 'red';
 old_state
-----------
 on
(1 row)

… и выключает ее:

=> -- привязка значения параметра
\bind 'off'
=> UPDATE lights SET state = $1 WHERE lamp = 'red';

Вторая транзакция ждет завершения первой.

=> COMMIT;
COMMIT
UPDATE 1
=> COMMIT;
COMMIT

Первый сеанс ожидает увидеть, что красная лампочка мигает. Проверим:

=> SELECT * FROM lights WHERE lamp = 'red';
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)

В этом случае вторая транзакция записала свои изменения «поверх» изменений первой транзакции. На уровне Read Committed сервер не может это предотвратить, поскольку команда UPDATE фактически содержит предопределенную константу.

Page 11: Repeatable Read

Должен предотвращать аномалии

  • потерянные изменения
  • грязное чтение
  • неповторяющееся чтение:
  • повторное чтение строки вернет другие данные,
  • если строка была изменена и зафиксирована другой транзакцией
  • Реализация в PostgreSQL
    • также не допускает фантомное чтение:
    • повторный запрос по одному и тому же условию вернет иную выборку,
    • если другая транзакция добавила и зафиксировала новые строки,
    • удовлетворяющие этому условию
    • для предотвращения аномалий транзакции могут обрываться

Комментарии преподавателя

Согласно стандарту, в дополнение к потерянным изменениям и грязным чтениям уровень изоляции Repeatable Read должен предотвращать аномалию, которую мы видели, когда рассматривали уровень Read Committed:

● Неповторяющееся чтение (non-repeatable read). После того как транзакция T1 прочитала строку, транзакция T2 изменила или удалила эту строку и зафиксировала изменения (COMMIT). При повторном чтении этой же строки транзакция T1 видит, что строка изменена или удалена.

Реализация PostgreSQL более строгая, чем того требует стандарт, и предотвращает аномалию

● Фантомное чтение (phantom read). Транзакция T1 прочитала набор строк по некоторому условию. Затем транзакция T2 добавила строки, также удовлетворяющие этому условию. Если транзакция T1 повторит запрос, она получит другую выборку строк.

На самом деле существуют и другие аномалии (не отраженные в стандарте), которые допускаются в PostgreSQL на уровне Repeatable Read.

От уровня Read Committed этот уровень изоляции отличается и тем, что на нем транзакция может быть оборвана, чтобы не допустить аномалию (такую транзакцию надо повторять). На уровне Read Committed этого не происходит никогда — если стоит выбор между корректностью и эффективностью, предпочтение всегда отдается эффективности.

Page 12: Repeatable Read и неповторяющееся чтение

Убедимся в отсутствии аномалии неповторяющегося чтения.

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT * FROM lights WHERE lamp = 'red';
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)
=> UPDATE lights SET state = 'on' WHERE lamp = 'red' RETURNING *;
 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)
UPDATE 1

Какое значение получит теперь первая транзакция?

=> SELECT * FROM lights WHERE lamp = 'red';
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)

Повторное чтение измененной строки возвращает первоначальное значение.

=> COMMIT;
COMMIT

Repeatable Read и фантомное чтение

Фантомные записи также не должны быть видны. Проверим это.

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT * FROM lights WHERE state = 'off';
 id | lamp  | state
----+-------+-------
  2 | green | off
(1 row)
=> INSERT INTO lights(lamp,state) VALUES ('yellow', 'off')
    RETURNING *;
 id |  lamp  | state
----+--------+-------
  3 | yellow | off
(1 row)
INSERT 0 1
=> SELECT * FROM lights WHERE state = 'off';
 id | lamp  | state
----+-------+-------
  2 | green | off
(1 row)

Действительно, транзакция не видит добавленной записи, удовлетворяющей первоначальному условию. Уровень изоляции Repeatable Read в PostgreSQL более строгий, чем того требует стандарт SQL.

Page 13: Repeatable Read и потерянные изменения

=> COMMIT;
COMMIT

Уберем желтую лампочку.

=> DELETE FROM lights WHERE lamp = 'yellow';
DELETE 1

Необходимость все время видеть ровно те же данные, что и в самом начале, не позволяет перечитывать измененную строку в случае обновления. Воспроизведем пример, который мы видели на уровне изоляции Read Committed. Сейчас красная лампочка включена.

=> SELECT * FROM lights WHERE lamp = 'red';
 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)
=> BEGIN;
BEGIN
=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';
UPDATE 1
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

Клиент запрашивает состояние красной лампочки…

=> SELECT state AS current_state FROM lights WHERE lamp = 'red';
 current_state
---------------
 on
(1 row)

…а затем меняет его на сервере:

=> -- привязка значения параметра
\bind 'off'
=> UPDATE lights SET state = $1 WHERE lamp = 'red';

Вторая транзакция ждет завершения первой.

=> COMMIT;
COMMIT
ERROR:  could not serialize access due to concurrent update

Во второй транзакции получаем ошибку. Строка была изменена; обновить неактуальную версию невозможно (это будет потерянным изменением, что недопустимо), а увидеть актуальную версию тоже невозможно (это нарушило бы уровень изоляции).

=> ROLLBACK;
ROLLBACK

Таким образом, потерянное обновление на уровне Repeatable Read не допускается.

Repeatable Read и другие аномалии

Можно ли быть уверенным в том, что следующая команда включит все лампочки?

UPDATE lights SET state = 'on' WHERE state != 'on';

Можно ли быть уверенным в том, что следующая команда выключит все лампочки?

UPDATE lights SET state = 'off' WHERE state != 'off';

Page 14: Page 14

Если одна транзакция включает лампочки, а другая — выключает, в каком состоянии останется таблица после одновременного выполнения двух этих транзакций? Включим красную лампочку и проверим.

=> UPDATE lights SET state = 'on' WHERE lamp = 'red';
UPDATE 1

Начальное состояние:

=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> UPDATE lights SET state = 'on' WHERE state != 'on';
UPDATE 1

Первая транзакция включила зеленую лампочку, и теперь для нее все огни горят.

=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | on
(2 rows)
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

Вторая транзакция не видит этих, еще не зафиксированных, изменений и считает, что зеленый свет выключен. Поэтому она выключает красный и для нее все огни погашены.

=> UPDATE lights SET state = 'off' WHERE state != 'off';
UPDATE 1
=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)

Теперь обе транзакции фиксируют свои изменения…

=> COMMIT;
COMMIT
=> COMMIT;
COMMIT

И оказывается, что…

=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | on
(2 rows)

…одна лампочка включена, а другая — выключена. Это пример аномалии конкурентного доступа — несогласованная запись (write skew), — которая возможна, даже если нет грязного, неповторяющегося и фантомного чтений.

Page 15: Page 15

Page 16: Serializable

Должен предотвращать любые аномалии

  • результат конкурентного выполнения совпадает с результатом
  • выполнения транзакций в какой-либо последовательности
  • Реализация в PostgreSQL
    • для предотвращения аномалий транзакции могут обрываться
    • для правильной работы уровень Serializable должен использоваться
    • всеми транзакциями
    • уровень не работает на физических репликах

Комментарии преподавателя

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

В PostgreSQL этот уровень реализован и работает, если все транзакции в системе используют уровень Serializable.

Следует учитывать, что реализация имеет определенные ограничения:

● запросы не могут выполняться на физических репликах;

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

Page 17: Serializable

На этом уровне транзакции могут положиться на то, что на их работу никто не повлияет.

=> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
=> UPDATE lights SET state = 'on' WHERE state != 'on';
UPDATE 1

Первая транзакция включила красную лампочку, для нее все огни горят.

=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | on
(2 rows)

Вторая транзакция не видит незафиксированных изменений: для нее красный свет еще выключен, и она выключает зеленый.

=> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
=> UPDATE lights SET state = 'off' WHERE state != 'off';
UPDATE 1

Для второй транзакции обе лампочки выключены:

=> SELECT * FROM lights ORDER BY id;
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)

Теперь обе транзакции фиксируют свои изменения…

=> COMMIT;
COMMIT
=> COMMIT;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

И вторая транзакция получает ошибку. Действительно, при последовательном выполнении транзакций допустимо два результата: если сначала выполняется первая транзакция, а потом вторая, то все лампочки будут погашены; если сначала выполняется вторая транзакция, а потом первая, то все лампочки будут включены. «Промежуточный» вариант получить невозможно, поэтому выполнение одной из транзакций завершается ошибкой. Важный момент: чтобы уровень Serializable работал корректно, на этом уровне должны работать все транзакции. Если смешивать транзакции разных уровней, фактически Serializable будет вести себя, как Repeatable Read.

Page 18: Итоги: стандарт SQL

  • неповторя-
  • потерянные
  • обновления
  • грязное
  • фантомное
  • другие
  • аномалии
  • ющееся
  • чтение
  • чтение
  • чтение
  • Read Uncommitted ― да да да да
  • Read Committed ― ― да да да
  • Repeatable Read ― ― ― да да
  • Serializable ― ― ― ― ―

Комментарии преподавателя

Подытожим информацию.

В стандарте SQL определены четыре уровня изоляции транзакций: Read Uncommitted, Read Committed, Repeatable Read, Serializable (по умолчанию).

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

Отметим, что для обеспечения уровня изоляции Serializable недостаточно исключить три аномалии, определенные в стандарте.

Page 19: Итоги: PostgreSQL

  • неповторя-
  • потерянные
  • обновления
  • грязное
  • фантомное
  • другие
  • аномалии
  • ющееся
  • чтение
  • чтение
  • чтение
  • Read Uncommitted
  • Read Committed * ― да да да
  • Repeatable Read ― ― ― ― да
  • Serializable ― ― ― ― ―
  • * предотвращается не всегда

Комментарии преподавателя

Реальные СУБД, и PostgreSQL в том числе, не реализуют требования стандарта буквально (которые были сформулированы в те времена, когда вопросы изоляции еще не были изучены теоретиками). Названия уровней изоляции сохраняются, но реализация их строже, чем это требует стандарт.

В PostgreSQL используется изоляция на основе снимков данных (Snapshot Isolation) в сочетании с многоверсионностью. Это определяет следующие свойства:

● Грязное чтение не допускается вообще, причем без ущерба для производительности.

● Уровень Read Committed используется по умолчанию и применяется в большинстве систем. Зачастую он требует ручной установки блокировок, чтобы обеспечить корректность.

● Уровень Repeatable Read не допускает не только неповторяемое чтение, но и фантомное чтение (хотя и не обеспечивает полную изоляцию). Этот уровень удобен для отчетов, поскольку позволяет нескольким запросам видеть согласованные данные, а только читающие транзакции никогда не обрываются.

● Уровень Serializable также полностью реализован, но имеет определенные ограничения.

https://postgrespro.ru/docs/postgresql/16/transaction-iso

Page 20: Практика

  1. Создайте пустую таблицу. Проверьте, что на уровне изоляции Read Committed не предотвращается аномалия фантомного чтения.
    1. Откройте транзакцию с уровнем изоляции Repeatable Read (и пока не выполняйте в ней никаких команд). В другом сеансе удалите строку из таблицы. Видна ли удаленная строка в открытой транзакции? Будет ли видна удаленная строка, если после открытия транзакции выполнить запрос к базе данных, не обращаясь к таблицам?
    1. Убедитесь, что команда DROP TABLE транзакционна.

Page 21: 1. Фантомное чтение на уровне Read Committed

Source slide page 21: 1. Фантомное чтение на уровне Read Committed{#source-figure-021 caption=“Source slide page 21: 1. Фантомное чтение на уровне Read Committed”}

=> CREATE DATABASE arch_isolation;
CREATE DATABASE
=> \c arch_isolation
You are now connected to database "arch_isolation" as user "student".

Пустая таблица:

=> CREATE TABLE t(id integer);
CREATE TABLE

Во втором сеансе начнем транзакцию Read Committed и выполним запрос.

=> \c arch_isolation
You are now connected to database "arch_isolation" as user "student".
=> BEGIN;
BEGIN
=> SELECT * FROM t;
 id
----
(0 rows)

Вставим новую строку в таблицу (изменения фиксируются).

=> INSERT INTO t VALUES (1);
INSERT 0 1
=> SELECT * FROM t;
 id
----
  1
(1 row)

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

=> COMMIT;
COMMIT

2. На какой момент времени видны данные?

Открываем транзакцию Repeatable Read:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

Добавляем строку в таблицу (изменения фиксируются):

=> INSERT INTO t VALUES (2);
INSERT 0 1

Проверяем содержимое таблицы в открытой транзакции:

=> SELECT * FROM t;
 id
----
  1
  2
(2 rows)

Page 22: Page 22

Почему видны обе строки? Дело в том, что транзакции Repeatable Read (и Serializable) видят данные на момент начала первой команды, а не на момент выполнения BEGIN. Если сейчас добавить в таблицу еще строк, результат повторной выборки уже не изменится.

=> INSERT INTO t VALUES (3);
INSERT 0 1
=> SELECT * FROM t;
 id
----
  1
  2
(2 rows)
=> COMMIT;
COMMIT

3. Транзакционность DDL

Откроем транзакцию и удалим таблицу t.

=> BEGIN;
BEGIN
=> DROP TABLE t;
DROP TABLE
=> SELECT * FROM t;
ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
                      ^

Таблица удалена. Теперь откатим транзакцию:

=> ROLLBACK;
ROLLBACK
=> SELECT * FROM t;
 id
----
  1
  2
  3
(3 rows)

Удаление отменено, таблица опять существует.