Уровень изоляции REPEATABLE READ: предотвращение Non-Repeatable Read

Представьте, что вы играете в онлайн-игру, а в это время какой-то читер вмешивается в её код и усиливает своего игрока читаете книгу в библиотеке, а кто-то в это время может менять страницы, вставлять новые главы или вообще подменять книгу. Довольно неприятная ситуация, правда? Вот именно от таких "сюрпризов" и защищает уровень изоляции REPEATABLE READ.

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

Ключевые особенности:

• Предотвращает Dirty Read (чтение данных, которые еще не подтверждены).
• И, самое главное, предотвращает Non-Repeatable Read. Это означает, что если вы прочли набор данных в начале транзакции, то при повторном чтении вы получите те же данные, даже если другой пользователь их изменил.

Однако REPEATABLE READ не защищает от Phantom Read. Если другая транзакция добавит новые строки, они могут появиться в вашем повторном запросе. Чтобы устранить и эту аномалию, понадобится уровень SERIALIZABLE, но об этом мы поговорим позднее.

Как установить уровень изоляции REPEATABLE READ

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

1. Установить уровень изоляции для конкретной транзакции:

   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN;
   -- Ваши запросы
   COMMIT;
   

2. Установить уровень изоляции для текущей сессии:

   SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   

Во втором случае все транзакции в текущей сессии будут использовать REPEATABLE READ.

Пример: предотвращение Non-Repeatable Read

Допустим, у нас есть таблица accounts со следующей структурой:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (customer_name, status)
VALUES ('Alice', 'pending'), ('Bob', 'pending');

Начнем с базового сценария, когда одна транзакция изменяет данные, а другая читает.

Сценарий без REPEATABLE READ (уровень READ COMMITTED)

Транзакция 1 начинает работу:

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Получаем: 100

В это время Транзакция 2 меняет данные:

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Транзакция 1 продолжает:

SELECT balance FROM accounts WHERE account_id = 1;
-- Получаем: 150 (данные изменились!)
COMMIT;

Как видите, при уровне READ COMMITTED данные могут измениться между двумя чтениями в рамках одной транзакции. Это и есть Non-Repeatable Read.

Сценарий с REPEATABLE READ

Теперь попробуем тот же пример, но с уровнем изоляции REPEATABLE READ.

Транзакция 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Получаем: 100

Транзакция 2:

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Транзакция 1 продолжают работу:

SELECT balance FROM accounts WHERE account_id = 1;
-- Все еще получаем: 100 (данные неизменны!)
COMMIT;

Независимо от изменений, сделанных другой транзакцией, транзакция 1 видит данные такими, какими они были на момент начала. Таким образом, Non-Repeatable Read предотвращается.

Как работает REPEATABLE READ

PostgreSQL использует механизм MVCC (Multi-Version Concurrency Control), чтобы реализовать уровень изоляции REPEATABLE READ. Основной принцип MVCC заключается в том, что каждая транзакция получает стабильный "снимок" базы данных, который не меняется до окончания транзакции. Это достигается за счет создания и управления несколькими версиями строк.

Когда транзакция начинает выполняться, она видит данные в том состоянии, в котором они находились на момент её старта. Если другая транзакция вносит изменения, PostgreSQL создает новую версию строки, но ранее существующая версия остается для всех транзакций, которые ее используют.

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

Ограничения REPEATABLE READ: Phantom Read

Как мы уже упоминали, REPEATABLE READ не защищает от Phantom Read. Чтобы понять, что это значит, рассмотрим пример с запросами, которые работают с диапазонами данных.

Предположим, у нас есть таблица orders:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    amount NUMERIC NOT NULL
);

INSERT INTO orders (amount)
VALUES (50), (100), (150);

Транзакция 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Получаем: 2

Транзакция 2:

BEGIN;
INSERT INTO orders (amount) VALUES (200);
COMMIT;

Транзакция 1 продолжает работу:

SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Получаем: 3 (новая строка появилась в результате запроса!)
COMMIT;

В данном случае новая строка (с amount = 200) была добавлена другой транзакцией, и она "призрачным образом" появилась в результате запроса транзакции 1, несмотря на уровень изоляции REPEATABLE READ.

Если вам нужно избежать Phantom Read, вам придется использовать уровень SERIALIZABLE, но это всегда требует компромисса с производительностью.

Преимущества и недостатки REPEATABLE READ

Уровень изоляции REPEATABLE READ — отличное решение, когда вам нужна уверенность, что данные не изменятся в процессе выполнения транзакции. Как только вы что-то прочитали, это значение останется таким до самого COMMIT, даже если кто-то в другой транзакции попытается внести изменения.

Такой подход предотвращает и грязные чтения (dirty read), и неповторяющееся чтение (non-repeatable read). Вы работаете с теми же самыми данными, что и в начале — никакого неожиданного обновления "на лету". Это особенно полезно, когда вы обрабатываете отчёты или принимаете решения, где важна консистентность.

С другой стороны, REPEATABLE READ не справляется с так называемыми "фантомами" (phantom read) — когда новые строки появляются в результате запроса, который вы уже выполняли в рамках той же транзакции. Кроме того, при высокой нагрузке такой уровень может вызывать конфликты между транзакциями, особенно если они часто обращаются к одним и тем же данным. Это может привести к блокировкам и откатам, даже если с запросами всё было правильно.

В общем, REPEATABLE READ — это хорошее соотношение надёжности и производительности, но в сценариях с высокой конкуренцией может потребовать дополнительных настроек и внимания.

Полезные советы и типичные ошибки

• Помните, что выбор уровня изоляции влияет на производительность. Используйте REPEATABLE READ только тогда, когда нужна уверенность в неизменности данных.
• Путаница между REPEATABLE READ и SERIALIZABLE — частая ошибка. Если вы видите новые строки в результате повторного запроса, это поведение ожидаемо для REPEATABLE READ.
• При работе с долгими транзакциями будьте осторожны с возможными конфликтами блокировок. Затяжные транзакции могут заблокировать другие операции.

PostgreSQL предоставляет различные инструменты для управления изоляцией транзакций. Уровень REPEATABLE READ идеально подходит для случаев, когда важна уверенность в неизменности уже прочитанных данных в рамках одной транзакции.