В этой статье я на простом примере покажу, как пронаблюдать аномалию несогласованное чтение (read skew) в postgresql, и как уровень изоляции транзакции Repeatable Read позволяет устранить эту аномалию.

Наблюдаем аномалию

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

id	name	active
1	Alex	true
2	Sam	true
3	Felix	false

Запустим консольную утилиту printer, которая будет печатать список активных (active=true) пользователей в таблице users и их количество.

~$ printer
1 Alex
2 Sam
total:  2

Чтобы напечатать такой список, printer делает следующие запросы в postgresql.

begin;

select id, name from users
where active = true
order by id;
 id | name  
----+-------
  1 | Alex
  2 | Sam

select count(*) from users
where active = true;
 count 
-------
     2

commit;

Теперь запустим консольную утилиту virus, которая нарушит работу утилиты printer. Все, что будет делать virus, - это с высокой частотой отправлять в postgresql следующий запрос.

update users set active = not active
where id = 3;

Если запустить printer, пока работает virus, то увидим несогласованные данные.

~$ printer
1 Alex
2 Sam
3 Felix
total:  2

Действительно, результаты чтения одной и той же таблицы в рамках одной транзакции противоречат друг другу. Это и есть несогласованное чтение. Причиной тому стал sql-запрос от virus, который "вклинился" между двумя sql-запросами printer и исключил из списка активных пользователей одну строку.

printer                           virus

begin;

select id, name from users
where active = true
order by id;
 id | name  
----+-------
  1 | Alex
  2 | Sam
  3 | Felix

                                  update users set active = not active
                                  where id = 3;

select count(*) from users
where active = true;
 count 
-------
     2

commit;

Утилита printer использует уровень изоляции транзакции Read Committed (читать зафиксированное) - уровень изоляции по умолчанию в postgresql, который активируется, если пишем просто begin. Как видно из названия, этот уровень изоляции позволяет читать зафиксированные изменения, что и сделала транзакция printer. Утилита printer прочитала зафиксированные изменения транзакции virus. Да, именно транзакции: если явно не написать begin/commit, то postgresql помещает команду в рамки индивидуальной транзакции. Иными словами, команду от virus postgresql воспринимает следующим образом.

begin;

update users set active = not active where id = 3;

commit;

Устраняем аномалию с помощью Repeatable Read

Воспользуемся уровнем изоляции Repeatable Read. Повторим эксперимент и убедимся, что чтение теперь всегда согласованно, и утилита virus больше не в силах помешать корректной работе printer.

begin isolation level repeatable read;

Каким образом чтение стало согласованным? Проведем еще один эксперимент, в котором разберемся, как видят таблицу users транзакция printer и транзакция virus. Для анализа результатов этого эксперимента, нужно упомянуть еще два понятия postgresql:

номер транзакции;
версия строки.

Каждой транзакции postgresql выдает порядковый номер. Этот номер можно увидеть, вызвав стандартную функцию pg_current_xact_id.

begin;

select pg_current_xact_id();
 pg_current_xact_id 
--------------------
                1043

commit;

Строка в postgresql - это логическое понятие. Физически postgresql хранит так называемые версии строк. Каждой строке может соответствовать сразу несколько версий. Причем транзакция в один момент времени видит только одну версию строки - логическую строку. Определить, какая версия видна транзакции, помогают два поля: xmin и xmax. Эти поля являются частью метаинформации версии строки и не выводятся в результате обычного select *. Чтобы вывести эти поля, их потребуется явно указать в запросе.

select *, xmin, xmax from users;

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

Вернемся к эксперименту. Будем использовать уровень Repeatable Read для изоляции транзакции printer, а также выведем номера транзакций и информацию о версиях строк:

xmin (для удобства будем использовать псевдоним tx_created);
xmax (для удобства будем использовать псевдоним tx_deleted).

Создаем строки.

begin;

select pg_current_xact_id();
 pg_current_xact_id 
--------------------
                742

insert into users (id, name, active) values 
(1, 'Alex', true), 
(2, 'Sam', true), 
(3, 'Felix', true);

commit;

Запускаем транзакции printer и virus.

printer                                             virus

begin isolation level repeatable read;

select pg_current_xact_id();
 pg_current_xact_id 
--------------------
                743

select id, name, active,
  xmin as tx_created, 
  xmax as tx_deleted 
from users
order by id;
 id | name  | active | tx_created | tx_deleted 
----+-------+--------+------------+------------
  1 | Alex  | t      |        742 |          0
  2 | Sam   | t      |        742 |          0
  3 | Felix | t      |        742 |          0

                                                     begin;

                                                     select pg_current_xact_id();
                                                      pg_current_xact_id 
                                                     --------------------
                                                                     744
                                                     
                                                     update users set active = not active
                                                     where id = 3;
                                                     
                                                     select id, name, active,
                                                       xmin as tx_created, 
                                                       xmax as tx_deleted 
                                                     from users
                                                     order by id;
                                                      id | name  | active | tx_created | tx_deleted 
                                                     ----+-------+--------+------------+------------
                                                       1 | Alex  | t      |        742 |          0
                                                       2 | Sam   | t      |        742 |          0
                                                       3 | Felix | f      |        744 |          0
                                                     
                                                     commit;

select id, name, active,
  xmin as tx_created, 
  xmax as tx_deleted 
from users
order by id;
 id | name  | active | tx_created | tx_deleted 
----+-------+--------+------------+------------
  1 | Alex  | t      |        742 |          0
  2 | Sam   | t      |        742 |          0
  3 | Felix | t      |        742 |        744

commit;

Команда update отметила версию строки с пользователем Felix и полем active=true как удаленную своей транзакцией и создала новую версию этой строки с active=false. Теперь у строки с пользователем Felix целых две версии.

id	name	active	tx_created	tx_deleted
1	Alex	true	742	0
2	Sam	true	742	0
3	Felix	true	742	744
3	Felix	false	744	0

Транзакция printer с уровнем изоляции Repeatable Read вплоть до момента фиксации (commit) видит только актуальные версии строк, созданные до вызова первого select. Совокупность версий строк, которые видит транзакция, называется снимком данных (snapshot). Транзакция с уровнем Repeatable Read используют только один снимок данных. Соответственно, если транзакция использует один снимок данных, то она может сколько угодно раз читать из users и получать одни и те же данные. Об этом свойстве напоминает название Repeatable Read (читать повторно).

Напротив, транзакция printer с уровнем изоляции Read Committed в процессе работы создает новый снимок данных перед вызовом каждого select. Именно поэтому транзакция printer после фиксации транзакции virus видит новую версию строки с пользователем Felix - эта новая версия строки из нового снимка.

Упрощенно визуализировать все вышеописанное можно следующим образом.

Транзакция 742 создает три первых версии строки в таблице users, затем запускается транзакция 743 - читающая транзакция printer. В транзакции 744 команда update помечает одну версию строки с пользо��ателем Felix как удаленную и создает новую версию.

Заключение

Использование Repeatable Read устранит аномалию несогласованное чтение, если вы столкнулись с ее проявлениями в процессе эксплуатации Read Committed, либо вообще не помещаете запросы на чтение в рамки транзакций. Repeatable Read - это решение, которое предоставляется средствами postgresql.

В зависимости от конкретной ситуации могут быть и другие решения. Например, в рассмотренном случае можно было бы сделать снимок данных средствами приложения: утилита printer могла бы не делать второй запрос select count(*), и в качестве общего количества активных пользователей рассматривать количество строк, которое вернул первый select. Этот набор строк - тоже снимок данных, но сделанный приложением.

У задачи может быть несколько решений - выбирайте оптимальное.

Список источников

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

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

Глава 2. Изоляция
Глава 4. Снимки данных

Все детали реализации postgresql можно найти в исходном коде. В частности функция GetTransactionSnapshot содержит реализацию алгоритма создания снимка данных для запроса в рамках транзакции.

Исходный код с приложениями для тестов и docker-compose.yml располагается здесь.

Repeatable Read или как согласовать чтение в Postgresql