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

03 Занятие 03: Многоверсионность / Снимки данных

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

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

Page 1: Многоверсионность / Снимки данных

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

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

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

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

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

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

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

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

Page 2: Темы

  • Видимость версий строк
  • Снимок данных
  • Виртуальный номер транзакции
  • Горизонт транзакции и базы данных
  • Экспорт снимка

Page 3: Снимок

Дает согласованную картину данных на момент времени

  • видны только зафиксированные на этот момент данные
  • Создается:

снимок

  • Read Committed —
  • в начале каждого оператора
  • Repeatable Read, Serializable —
  • в начале первого оператора транзакции
  • xid

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

В теме «Страницы и версии строк» мы видели, что в страницах данных физически может находиться несколько версий одной и той же строки.

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

Согласованность здесь понимается в обычном ACID-смысле: данные должны быть корректны, что, по сути, означает, что видеть нужно только те данные, которые были зафиксированы на момент создания снимка. Это полностью исключает аномалию грязного чтения на любом уровне изоляции.

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

На уровне Repeatable Read (и Serializable) снимок создается один раз в начале первого оператора транзакции. Такой снимок остается активным до самого конца транзакции. В этом случае неповторяемое и фантомное чтения исключаются.

Page 4: Видимость версий строк

Видимость версии строки ограничена xmin и xmax

  • Версия попадает в снимок, когда
    • изменения транзакции xmin видны для снимка,
    • изменения транзакции xmax не видны для снимка
  • Изменения транзакции видны, когда
    • либо это та же самая транзакция, что создала снимок,
    • либо она завершилась фиксацией до момента создания снимка
  • Отдельные правила для видимости собственных изменений
    • учитывается порядковый номер операции в транзакции (cmin/cmax)

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

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

Будет или нет данная версия строки видна в снимке, определяется двумя полями ее заголовка — xmin и xmax, — то есть номерами создавшей и удалившей транзакций. Интервалы для разных версий одной строки не пересекаются, поэтому строка таблицы представлена в любом снимке максимум одной своей версией.

Точные правила видимости довольно сложны и учитывают различные «крайние» случаи. Чуть упрощая, можно сказать, что версия строки видна, когда в снимке видны изменения, сделанные транзакцией xmin, и не видны изменения, сделанные транзакцией xmax. Иными словами, создание версии строки должно быть видно, а удаление (если оно было) — нет.

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

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

Page 5: Видимость версий строк

Source slide page 5: Видимость версий строк{#source-figure-005 caption=“Source slide page 5: Видимость версий строк”}

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

Рассмотрим, как это работает, на простом примере.

На рисунке синим цветом отмечен момент создания снимка. Черные линии показывают транзакции от момента начала до момента фиксации (оборванные транзакции никогда не учитываются снимком).

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

Изменения транзакции T1 не должны быть видны, поскольку на момент создания снимка она еще не была зафиксирована. А изменения транзакции T3 не должны быть видны, поскольку она еще не началась в момент создания снимка.

Page 6: Видимость версий строк

Source slide page 6: Видимость версий строк{#source-figure-006 caption=“Source slide page 6: Видимость версий строк”}

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

=> CREATE DATABASE mvcc_snapshots;
CREATE DATABASE
=> \c mvcc_snapshots
You are now connected to database "mvcc_snapshots" as user "student".
=> CREATE TABLE t(s text);
CREATE TABLE

Первая транзакция (не завершится до создания снимка):

=> \c mvcc_snapshots
You are now connected to database "mvcc_snapshots" as user "student".
T1=> BEGIN;
BEGIN
T1=> INSERT INTO t VALUES ('first');
INSERT 0 1
T1=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                745
(1 row)

Вторая транзакция (завершается сразу):

=> \c mvcc_snapshots
You are now connected to database "mvcc_snapshots" as user "student".
T2=> BEGIN;
BEGIN
T2=> INSERT INTO t VALUES ('second');
INSERT 0 1
T2=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                746
(1 row)
T2=> COMMIT;
COMMIT

Теперь создаем снимок в транзакции в отдельном сеансе.

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT 1;
 ?column?
----------
        1
(1 row)

И завершаем первую транзакцию после того, как создан снимок:

T1=> COMMIT;
COMMIT

Page 7: Page 7

Source slide page 7: Page 7{#source-figure-007 caption=“Source slide page 7: Page 7”}

Третья транзакция начинается после создания снимка:

=> \c mvcc_snapshots
You are now connected to database "mvcc_snapshots" as user "student".
T3=> BEGIN;
BEGIN
T3=> INSERT INTO t VALUES ('third');
INSERT 0 1
T3=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                747
(1 row)
T3=> COMMIT;
COMMIT

Очевидно, что в нашем снимке видна одна строка:

=> SELECT *, xmin, xmax FROM t;
   s    | xmin | xmax
--------+------+------
 second |  746 |    0
(1 row)

Вопрос в том, как это понимает PostgreSQL.

Page 8: Видимость версий строк

Source slide page 8: Видимость версий строк{#source-figure-008 caption=“Source slide page 8: Видимость версий строк”}

как отличить T1 от T2 после того, как снимок создан?

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

Сложность реализации состоит в том, что нам известен только момент начала транзакции. Он определяется номером транзакции (xid), с которым можно сравнить номер xmin или xmax версии строки. Но момент завершения транзакции нигде не записывается. Все, что мы можем узнать — это текущий статус транзакций.

То есть после того, как транзакция T1 завершится фиксацией, она ничем не будет отличаться от транзакции T2: нет способа выяснить, что одна из них была зафиксирована до создания снимка, а другая — после.

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

Page 9: Снимок данных

Source slide page 9: Снимок данных{#source-figure-009 caption=“Source slide page 9: Снимок данных”}

  • xmin — номер самой ранней активной транзакции xmax — номер, следующий за последней зафиксированной транзакцией xip_list — список активных транзакций

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

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

● xmin — нижняя граница снимка, в качестве которой выступает номер самой ранней активной транзакции.

Все транзакции с меньшими номерами либо зафиксированы, и тогда их изменения безусловно видны в снимке, либо отменены, и тогда изменения игнорируются.

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

Все транзакции с номерами, большими или равными xmax, в момент создания снимка заведомо не были зафиксированы — они либо были активны, либо отменены, либо еще не начались.

● xip_list (xid-in-progress list) — список активных транзакций.

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

Информацию о снимке можно получить с помощью функции pg_current_snapshot() и нескольких других, см. https://postgrespro.ru/docs/postgresql/16/functions-info#FUNCTIONS-INFO-SNAPSHOT

Page 10: Снимок данных

Source slide page 10: Снимок данных{#source-figure-010 caption=“Source slide page 10: Снимок данных”}

Видимость версий строк определяется снимком. Посмотрим на него:

=> SELECT pg_current_snapshot(); -- txid_current_snapshot() до v.13
 pg_current_snapshot
---------------------
 745:747:745
(1 row)

Снимок определяется в момент создания тремя основными значениями, которые функция выводит через двоеточия: xmin — номер самой ранней активной транзакции; xmax — номер, следующий за последней зафиксированной транзакцией (определяет момент создания снимка); xip_list — список номеров активных транзакций (в данном случае она одна). В снимке должны быть видны изменения транзакций с номерами: xid < xmin; xmin <= xid < xmax, за исключением транзакций из xip_list. В нашем случае: xid < 745; 745 <= xid < 747, за исключением 745.

T1=> SELECT *, xmin, xmax FROM t;
   s    | xmin | xmax
--------+------+------
 first  |  745 |    0
 second |  746 |    0
 third  |  747 |    0
(3 rows)

Первая строка не видна — она создана транзакцией 745, которая входит в список активных. Вторая строка видна — она создана транзакцией 746, которая попадает в диапазон снимка. Третья строка не видна — она создана транзакцией 747, которая не входит в диапазон снимка.

Page 11: Виртуальные транзакции

Только читающая транзакция никак не влияет на видимость

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

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

На практике PostgreSQL использует оптимизацию, позволяющую «экономить» номера транзакций.

Если транзакция только читает данные, то она никак не влияет на видимость версий строк и ее номер не надо учитывать в снимках данных.

Поэтому вначале обслуживающий процесс выдает транзакции виртуальный номер (virtual xid). Номер состоит из внутреннего идентификатора процесса и последовательного числа.

Выдача этого номера не требует синхронизации между всеми процессами и поэтому выполняется очень быстро. С другой причиной использования виртуальных номеров мы познакомимся в теме «Заморозка» этого модуля.

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

Как только транзакция начинает изменять данные, ей сразу же выдается постоянный номер (xid), который может появляться в страницах данных — в полях xmin и xmax версий строк.

Page 12: Виртуальные транзакции

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

=> SELECT pg_current_xact_id_if_assigned(); -- txid_current_if_assigned() до v.13
 pg_current_xact_id_if_assigned
--------------------------------
(1 row)

Постоянный номер присваивается при первом изменении, выполненном транзакцией, а также при вызове функции pg_current_xact_id().

Page 13: Горизонт снимка

Source slide page 13: Горизонт снимка{#source-figure-013 caption=“Source slide page 13: Горизонт снимка”}

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

Номер самой ранней из активных транзакций (xmin снимка) имеет важный смысл — он определяет горизонт снимка.

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

Действительно, при построении снимка ему требуется видеть неактуальную версию только если она удалена (заменена) еще не завершившейся транзакцией, поэтому снимок этого изменения не видит. Но за горизонтом все транзакции уже гарантированно завершились.

Page 14: Горизонт транзакции

Source slide page 14: Горизонт транзакции{#source-figure-014 caption=“Source slide page 14: Горизонт транзакции”}

Repeatable Read, Serializable

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

На разных уровнях изоляции транзакции по-разному используют снимки данных.

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

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

Page 15: Горизонт транзакции

Source slide page 15: Горизонт транзакции{#source-figure-015 caption=“Source slide page 15: Горизонт транзакции”}

Read Committed

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

В случае уровня изоляции Read Committed снимков может быть несколько.

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

Page 16: Горизонт транзакции

Source slide page 16: Горизонт транзакции{#source-figure-016 caption=“Source slide page 16: Горизонт транзакции”}

Read Committed

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

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

Page 17: Горизонт транзакции

Source slide page 17: Горизонт транзакции{#source-figure-017 caption=“Source slide page 17: Горизонт транзакции”}

Read Committed

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

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

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

Page 18: Горизонт транзакции

Source slide page 18: Горизонт транзакции{#source-figure-018 caption=“Source slide page 18: Горизонт транзакции”}

Read Committed

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

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

Если транзакция будет долго находиться в таком состоянии (оно называется idle in transaction), не выполняя никаких команд, горизонт будет оставаться на месте.

Page 19: Горизонт транзакции

Source slide page 19: Горизонт транзакции{#source-figure-019 caption=“Source slide page 19: Горизонт транзакции”}

Read Committed

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

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

Page 20: Горизонт транзакции

Source slide page 20: Горизонт транзакции{#source-figure-020 caption=“Source slide page 20: Горизонт транзакции”}

Read Committed

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

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

Page 21: Горизонт базы данных

Source slide page 21: Горизонт базы данных{#source-figure-021 caption=“Source slide page 21: Горизонт базы данных”}

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

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

Неактуальные версии строк за горизонтом базы данных уже никогда не будут нужны ни одной транзакции. Такие версии строк могут быть безопасно вычищены.

На слайде показан пример трех транзакций с уровнем изоляции Read Committed. Для оператора UPDATE транзакции T1 был построен снимок. Когда оператор отрабатывает, снимок удаляется и перестает удерживать горизонт. Однако транзакция T1 по-прежнему активна, и поэтому горизонты снимков транзакций T2 и T3 не сдвинутся правее номера транзакции T1. Тем самым активная транзакция, если у нее есть постоянный номер, удерживает горизонт самим фактом своего существования.

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

Это одна из причин, по которым транзакции не следует делать длиннее, чем абсолютно необходимо.

Page 22: Горизонт базы данных

Source slide page 22: Горизонт базы данных{#source-figure-022 caption=“Source slide page 22: Горизонт базы данных”}

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

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

Page 23: Горизонт транзакции и базы данных

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

=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          745
(1 row)

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

=> COMMIT;
COMMIT
=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          748
(1 row)

Page 24: Экспорт снимка

Source slide page 24: Экспорт снимка{#source-figure-024 caption=“Source slide page 24: Экспорт снимка”}

Задача

  • распределить работу между несколькими одновременно работающими
  • транзакциями так, чтобы они видели одни и те же данные
  • пример: pg_dump –jobs=N
BEGIN ISOLATION LEVEL REPEATABLE READ;
...
SELECT pg_export_snapshot();
идентификатор
снимка
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT идентификатор;
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT идентификатор;

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

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

Функция pg_export_snapshot сохраняет информацию о снимке в виде файла в каталоге PGDATA/pg_snapshots и возвращает идентификатор снимка, который может быть передан (внешними по отношению к СУБД средствами) в другую транзакцию.

https://postgrespro.ru/docs/postgresql/16/functions-admin#FUNCTIONS-SN APSHOT-SYNCHRONIZATION

Экспортированный снимок существует до окончания экспортировавшей его транзакции. В это время другая транзакция может импортировать снимок с помощью команды SET TRANSACTION SNAPSHOT до выполнения первого запроса в ней. Предварительно надо установить и уровень изоляции Repeatable Read или Serializable, потому что на уровне Read Committed операторы будут использовать собственные снимки.

https://postgrespro.ru/docs/postgresql/16/sql-set-transaction

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

Page 25: Итоги

Снимок содержит информацию, необходимую для определения видимости версий строк

  • Время создания снимка определяется уровнем изоляции
  • Снимок определяет горизонт транзакции, влияющий на возможность удаления неактуальных версий

Page 26: Практика

  1. Воспроизведите ситуацию, при которой одна транзакция еще видит удаленную строку, а другая — уже нет. Посмотрите снимки данных этих транзакций и значения полей xmin и xmax удаленной строки. Объясните видимость на основании этих данных.
    1. Если в запросе вызывается функция, содержащая другой запрос, какой снимок данных будет использоваться для «вложенного» запроса? Проверьте уровни изоляции Read Committed и Repeatable Read и категории изменчивости функций volatile и stable.
    1. В одной транзакции экспортируйте снимок, затем в другой транзакции измените данные. Импортируйте снимок и проверьте, что в нем видны еще не измененные данные.

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

  1. Можно воспользоваться следующим шаблоном функции, в предположении, что создана таблица t:
CREATE FUNCTION test() RETURNS bigint
VOLATILE -- или STABLE
LANGUAGE sql
BEGIN ATOMIC
  SELECT count(*) FROM t;
END;

Также может пригодиться функция pg_sleep(n), вызывающая задержку на n секунд.

Page 27: 1. Снимки данных двух транзакций

Таблица с одной строкой:

=> CREATE DATABASE mvcc_snapshots;
CREATE DATABASE
=> \c mvcc_snapshots
You are now connected to database "mvcc_snapshots" as user "student".
=> CREATE TABLE t(n integer);
CREATE TABLE
=> BEGIN;
BEGIN
=> INSERT INTO t(n) VALUES (1);
INSERT 0 1
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                745
(1 row)
=> COMMIT;
COMMIT

Первая транзакция видит строку:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT * FROM t;
 n
---
 1
(1 row)
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                746
(1 row)
=> SELECT pg_current_snapshot();
 pg_current_snapshot
---------------------
 746:746:
(1 row)

Вторая транзакция в другом сеансе удаляет строку:

=> \c mvcc_snapshots
You are now connected to database "mvcc_snapshots" as user "student".
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> DELETE FROM t;
DELETE 1
=> SELECT * FROM t;

Page 28: Page 28

 n
---
(0 rows)
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                747
(1 row)
=> SELECT pg_current_snapshot();
 pg_current_snapshot
---------------------
 746:746:
(1 row)

Первая транзакция продолжает видеть строку:

=> SELECT xmin, xmax, * FROM t;
 xmin | xmax | n
------+------+---
  745 |  747 | 1
(1 row)

Снимки обеих транзакций одинаковы, и по правилам видимости они обе должны были бы видеть строку, так как изменения транзакции xmin = 745 видны в снимке (745 < 746), изменения транзакции xmax = 746 не видны в снимке (746 >= 746). Однако вторая транзакция все-таки не видит строку, поскольку она сама ее удалила, а это — исключение из обычных правил видимости.

=> COMMIT;
COMMIT
=> COMMIT;
COMMIT

2. Снимок вложенного запроса

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

=> CREATE FUNCTION test() RETURNS bigint
VOLATILE LANGUAGE sql
BEGIN ATOMIC
  SELECT pg_sleep(1);
  SELECT count(*) FROM t;
END;
CREATE FUNCTION

Теперь начинаем транзакцию с уровнем изоляции Read Committed и в запросе несколько раз вызываем функцию, одновременно подсчитывая количество строк подзапросом.

=> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);

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

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

Page 29: Page 29

 cnt | test
-----+------
   0 |    0
   0 |    0
   0 |    1
   0 |    1
   0 |    1
(5 rows)
=> END;
COMMIT

Значение первого столбца не изменяется — запрос использует снимок, созданный в начале выполнения. Однако значение во втором столбце изменяется — запросы внутри volatile-функции используют отдельные снимки. Повторим эксперимент для уровня изоляции Repeatable Read:

=> TRUNCATE t;
TRUNCATE TABLE
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);
=> INSERT INTO t VALUES (1);
INSERT 0 1
 cnt | test
-----+------
   0 |    0
   0 |    0
   0 |    0
   0 |    0
   0 |    0
(5 rows)
=> END;
COMMIT

Теперь еще раз то же самое для функции с категорией изменчивости stable.

=> ALTER FUNCTION test STABLE;
ALTER FUNCTION

Уровень Read Committed:

=> TRUNCATE t;
TRUNCATE TABLE
=> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);
=> INSERT INTO t VALUES (1);
INSERT 0 1
 cnt | test
-----+------
   0 |    0
   0 |    0
   0 |    0
   0 |    0
   0 |    0
(5 rows)
=> END;
COMMIT

Page 30: Page 30

Уровень Repeatable Read:

=> TRUNCATE t;
TRUNCATE TABLE
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT (SELECT count(*) FROM t) AS cnt, test()
FROM generate_series(1,5);
=> INSERT INTO t VALUES (1);
INSERT 0 1
 cnt | test
-----+------
   0 |    0
   0 |    0
   0 |    0
   0 |    0
   0 |    0
(5 rows)
=> END;
COMMIT

Выводы: Запросы в volatile-функциях на уровне изоляции Read Committed используют собственные снимки и могут видеть изменения в процессе работы оператора. Такая возможность скорее опасна, чем полезна. При любых других комбинациях категории изменчивости и уровня изоляции вложенные запросы используют снимок основного запроса.

3. Экспорт снимка

Функция pg_export_snapshot возвращает идентификатор снимка, который можно передать в другую транзакцию (внешними по отношению к СУБД средствами):

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT count(*) FROM t;
 count
-------
     1
(1 row)
=> SELECT pg_export_snapshot();
 pg_export_snapshot
---------------------
 00000005-00000010-1
(1 row)

В другом сеансе удаляем все строки из таблицы:

=> DELETE FROM t;
DELETE 1

Затем транзакция импортирует снимок и видит в нем те же данные, что и экспортировавшая этот снимок транзакция:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SET TRANSACTION SNAPSHOT '00000005-00000010-1';
SET
=> SELECT count(*) FROM t;

Page 31: Page 31

 count
-------
     1
(1 row)
=> COMMIT;
COMMIT
=> COMMIT;
COMMIT