Что такое Repeatable Read в SQL?

Уровень изоляции Repeatable Read в SQL

Repeatable Read — это один из четырех стандартных уровней изоляции транзакций в SQL, определённых стандартом ANSI/ISO SQL-92. Основная цель этого уровня — гарантировать, что данные, прочитанные в рамках одной транзакции, останутся неизменными на протяжении всей её выполнения, даже если другие транзакции пытаются их изменить.

Ключевые характеристики Repeatable Read

Основные гарантии, которые предоставляет этот уровень изоляции:

1. Предотвращение "грязного" чтения (Dirty Read): Транзакция не может видеть незафиксированные изменения других транзакций.
2. Гарантия неповторяющегося чтения (Non-Repeatable Read): Если транзакция повторно читает те же строки, она получит идентичные данные. Другие транзакции не могут изменить или обновить эти строки до завершения текущей транзакции.
3. Отсутствие гарантии от фантомного чтения (Phantom Read): Другие транзакции могут добавлять новые строки, соответствующие условиям запроса. Таким образом, повторное выполнение того же запроса может вернуть дополнительные ("фантомные") строки.

Как это работает на практике

База данных обычно реализует Repeatable Read с помощью механизмов блокировок (locks) или систем управления версиями данных (MVCC).

Пример с блокировками (часто используется в Microsoft SQL Server): Когда транзакция читает строку, она удерживает разделяемую блокировку (shared lock) на ней до своего завершения (COMMIT или ROLLBACK). Это предотвращает изменение строки другими транзакциями, но позволяет им её читать.

-- Транзакция 1
BEGIN TRANSACTION;
-- На строки с ProductID = 1 накладывается разделяемая блокировка
SELECT Quantity FROM Products WHERE ProductID = 1;

-- Транзакция 2 попытается обновить ту же строку
-- Этот запрос будет ЖДАТЬ, пока не завершится Транзакция 1
UPDATE Products SET Quantity = 10 WHERE ProductID = 1;
-- Запрос заблокирован...

-- Транзакция 1 снова читает ту же строку и получает ТЕ ЖЕ данные
SELECT Quantity FROM Products WHERE ProductID = 1;
COMMIT;
-- Только после COMMIT в Транзакции 1, блокировка снимается,
-- и UPDATE в Транзакции 2 может выполниться.

Пример с MVCC (реализация в PostgreSQL и MySQL InnoDB): Система хранит несколько версий строки. Каждая транзакция видит "снимок" данных (snapshot) на момент своего начала. Другие транзакции могут создавать новые версии строк, но для текущей транзакции данные остаются неизменными.

-- Транзакция 1
START TRANSACTION;
-- Видит snapshot данных
SELECT COUNT(*) FROM Orders; -- Допустим, вернуло 100

-- Транзакция 2
START TRANSACTION;
INSERT INTO Orders (CustomerID) VALUES (5);
COMMIT; -- Теперь в таблице 101 строка

-- Транзакция 1 повторяет запрос
-- При Repeatable Read в PostgreSQL она УВИДИТ ТЕ ЖЕ 100 строк,
-- так как работает со snapshot'ом на момент своего начала.
SELECT COUNT(*) FROM Orders; -- Всё ещё 100
COMMIT;

Различия между Repeatable Read и Serializable

Это частый источник путаницы. Serializable — более строгий уровень. В дополнение к гарантиям Repeatable Read, он также предотвращает фантомное чтение. В примере выше, при уровне SERIALIZABLE, вторая вставка INSERT в Транзакции 2 могла бы быть заблокирована или отменена, чтобы сохранить целостность условий чтения первой транзакции.

Проблемы и преимущества уровня Repeatable Read

Преимущества:

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

Проблемы и риски:

1. Фантомное чтение: Остаётся возможным, что может привести к логическим ошибкам (например, при подсчёте суммы по набору строк, который может увеличиться).
2. Снижение производительности и блокировки: В реализациях на основе блокировок удерживаемые до конца транзакции разделяемые блокировки уменьшают параллелизм, могут приводить к взаимным блокировкам (deadlocks) и ожиданиям.
3. Риск взаимных блокировок: Две транзакции, читающие, а затем пытающиеся обновить одни и те же строки в разном порядке, легко приходят к deadlock.

Когда использовать Repeatable Read?

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

• Финансовые операции (переводы между счетами).
• Создание сложных отчётов на основе последовательных согласованных данных.
• Системы бронирования, где наличие места проверяется дважды перед подтверждением.

Важно: Выбор уровня изоляции — это всегда компромисс между консистентностью данных, производительностью и параллелизмом. Repeatable Read занимает срединное положение между Read Committed (более быстрое, меньше блокировок, но есть non-repeatable reads) и Serializable (максимальная консистентность, но высокая цена в производительности и риске блокировок).