Уровни изоляции транзакций для собеседования и работы

Уровни изоляции транзакций – один из частых вопросов на собеседовании. Есть мнение, что один раз настроил и не вмешиваешься, но на практике не всегда так. Участвовал в нескольких проектах, где незнание уровней изоляции привело к трудноуловимым ошибкам и искажениям данных. В какой ситуации какой уровень изоляции лучше — разбираем ниже.

Рассмотрим:

1) Почему использование READ UNCOMMITTED ускоряет, но ведет к некорректным данным и дублированию строк.

2) Способ убрать длительный отклик БД при множестве операций чтения\запись.

3) В каких случаях какой уровень изоляции лучше.

Для начала вспомним основные понятия.

Уровни изоляции транзакций: суть и решаемые проблемы

Уровни изоляции — правила, определяющие, как транзакции видят изменения друг друга.

Какие проблемы решают?

Грязное чтение (dirty read),
Неповторяемое чтение (non repeatable read),
Фантомное чтение (phantom read),
Потерянное обновление (lost update).

Транзакции реализуются путём установки разного вида блокировок.

Shared, S-lock — При чтении накладываются разделяемые блокировки: разрешается читать, но не изменять.

Update, U-lock — Когда транзакция изменяет данные (например, выполняет UPDATE), ставится блокировка на изменение. Никто другой не может изменить эти данные, пока эта блокировка не снята.

Exclusive, X-lock — Когда транзакция захватывает данные, ставится эксклюзивная блокировка. Никто другой не может прочитать или изменить эти данные, пока эта блокировка не снята.

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

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

Уровни изолированности транзакций для самых маленьких

Транзакция, ACID, CAP теорема и уровни изоляций транзакций простыми словами

Уровни изоляции транзакций в БД

Уровни изоляции:

· Read uncommitted — чтение незафиксированных данных;

· Read committed — чтение зафиксированных данных;

· Snapshot – моментальный снимок;

· Repeatable read — повторяющееся чтение;

· Serializable – упорядоченный, как будто транзакции шли не параллельно, а одна за другой.

Проблемы по уровням изоляции

Уровень	Dirty Read	Non-Repeatable Read	Phantom Reads	Lost Update
READ UNCOMMITTED	Да	Да	Да	Да
READ COMMITTED	Нет ✅	Да	Да	Возможно
SNAPSHOT	Нет ✅	Нет ✅	Обычно Нет ✅	Нет (ошибка update)
REPEATABLE READ	Нет ✅	Нет ✅	Да	Нет ✅
SERIALIZABLE	Нет ✅	Нет ✅	Нет ✅	Нет ✅

Рассмотрим уровни подробнее.

Read uncommitted — Чтение незафиксированных данных

Частая ошибка: Когда БД перестает отвечать из-за нагрузки чтения\записи прописывают NOLOCK для ускорения запросов.

Причина

Для уровней старше Read uncommitted (кроме Snapshot) Select накладывает разделяемую блокировку (S-lock). В СУБД блокировки могут выставляться на таблицу, страницу, строку. При Read uncommitted СУБД читает данные с диска без учета блокировок. В результате запросы с хинтом NOLOCK или READUNCOMMITTED выполняются быстрее. Читающий запрос не ждет завершения блокировок обновлений (на строки, страницы, таблицу). В выборке может оказаться часть измененных данных, дубликаты (если строка была перезаписана в другое место на диске), а также отмененные изменения. Поэтому читать с диска без учета блокировок быстро, но будут некорректные данные.

Когда Read uncommitted подходит:

· Запросы, где погрешность не страшна, например, подсчет количества строк в больших таблицах,

· Выборка из константных таблиц.

Read committed — Чтение зафиксированных данных.

Уровень изоляции “по умолчанию” для СУБД: MS SQL, PostgreSQL, Oracle.

Реализация:

· Запросы на чтение ждут снятия блокировок изменения (U-lock),

· Запросы изменения ждут снятия блокировок чтения (S-lock) и изменения (U-lock).

При большой нагрузке на БД, длительных запросах блокировки выстраиваются в очередь, время ответа растет, запросы снимаются по таймауту. В таких случаях предлагают рассмотреть Snapshot, который делает копию данных и не блокирует запросы на чтение\изменение, а также выполнить оптимизацию запросов и транзакций. Как — будет приведено в конце статьи.

Риск при READ COMMITTED: Между SELECT и INSERT другая транзакция может купить последний товар и зафиксироваться. Ваш INSERT создаст заказ на несуществующий товар. Поэтому важно использовать блокировки.

BEGIN TRANSACTION;  -- 1. Выбираем строку товара и сразу берем UPDLOCK, чтобы "зарезервировать" ее. SELECT StockCount  FROM Products WITH (UPDLOCK, ROWLOCK) – блокируем строку для изменений  WHERE ProductId = 123;   -- 2. Проверяем остаток на прикладном уровне (в коде приложения) -- Если (StockCount < 1) -> ROLLBACK; и сообщаем об ошибке. -- 3. Если товар есть, уменьшаем количество.  UPDATE Products SET StockCount = StockCount - 1  WHERE ProductId = 123;   COMMIT TRANSACTION;

Что происходит:

1. Первая транзакция выполняет SELECT … WITH (UPDLOCK) и получает блокировку на изменение для строки товара №123.

2. Вторая транзакция пытается выполнить такой же SELECT … WITH (UPDLOCK) для этой же строки и не сможет наложить блокировку обновления. Запрос будет остановлен и будет ждать, пока первая транзакция не снимет блокировку (сделает COMMIT или ROLLBACK).

3. Первая транзакция проверяет остаток, обновляет его и фиксирует изменения.

4. Только после этого вторая транзакция продолжит работу, прочитает уже обновленное значение (StockCount = 0) и на этапе проверки в коде поймет, что товара уже нет.

Итог: Мы предотвратили race condition и потерю данных, оставаясь при этом в уровне изоляции READ COMMITTED.

Частые ошибки: обновление таблиц в разной последовательности, возникновение взаимоблокировок; Lost Update при обновлении данных на основе выбранных отдельным Select-ом без блокировки.

Когда Read committed подходит:

· Много операций записи одних и тех же строк

· Чистая OLTP-нагрузка с короткими транзакциями

· Важно потребление памяти/диска

Snapshot – моментальный снимок

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

Реализация: транзакция видит то состояние данных, которое было зафиксировано до её запуска, а также изменения, внесённые ею самой, то есть ведёт себя так, как будто получила при запуске моментальный снимок данных БД и работает с ним.

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

Типичные сценарии:

1. Длительные отчеты и аналитические запросы.

Контекст: Запрос, который агрегирует данные за год и строит сложные отчеты. Например, отчет на 30 секунд.

Проблема при READ COMMITTED (с блокировками): Длительный запрос будет блокироваться на каждой изменяемой строке или сам будет блокировать миллионы строк от изменения, парализуя работу OLTP-системы.

Решение SNAPSHOT: Запрос видит данные на момент своего начала и работает с ними, не устанавливая блокировок. Писатели могут свободно изменять актуальные данные, не мешая отчету. Это решение для SELECT-ов в системах с высокой нагрузкой.

2. Системы с высокой конкуренцией «читателей» и «писателей».

Контекст: Веб-сайт, где тысячи пользователей одновременно просматривают (SELECT) и обновляют (UPDATE) свои профили.

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

Решение SNAPSHOT: Чтение больше не блокируется и не блокирует. Резко повышается пропускная способность и отзывчивость приложения.

Snapshot оптимален по памяти относительно редко. Хранение множества версий строк — это плата за его преимущества в производительности. Однако есть нюанс:

Временные данные vs Постоянные данные: Версии хранятся не вечно. В SQL Server они помещаются в tempdb, в PostgreSQL — в специальные области в самих табличных файлах (с очисткой автовакуумом). Пока система успевает очищать устаревшие версии, потребление памяти и диска остается контролируемым.
Snapshot более эффективен по памяти, чем долгие блокировки: Держать тысячи разделяемых (S-lock) блокировок в памяти на протяжении длинной транзакции — тоже дорого. Snapshot заменяет эти затраты на хранение версий. В некоторых сценариях это может быть выгоднее.

Цена: возросшая нагрузка на tempdb (где хранятся версии строк), объем памяти, риск конфликтов обновления.

Частая ошибка: не включение Serializable там, где он нужен.

Пример, когда Snapshot уступает Serializable: Serializable vs. Snapshot Isolation Level

Когда Snapshot подходит:

· Много операций чтения\записи на одну таблицу

· Долгие операции чтения по изменяемым данным

· Ресурсы диска/памяти не критичны

Repeatable read – повторяющееся чтение;

Цель: Гарантировать, что данные, которые вы уже прочитали в рамках транзакции не изменятся и не исчезнут до ее завершения.

Типичные сценарии:

1. Проверка существования с последующим действием.

Контекст: Вы проверяете наличие достаточного количества товара на складе (SELECT …), и если товар есть, создаете заказ (INSERT …).

Риск при READ COMMITTED: Между SELECT и INSERT другая транзакция может купить последний товар и зафиксироваться. Ваш INSERT создаст заказ на несуществующий товар.

Решение REPEATABLE READ: После вашего первого SELECT строки с данными о товаре блокируются (S-lock) до конца транзакции. Другая транзакция не сможет изменить их количество или удалить их, пока вы не закончите. Это гарантирует, что ваше решение о создании заказа основано на актуальных и неизменных данных.

2. Согласованные расчеты на основе нескольких единиц данных.

Контекст: Вы читаете несколько связанных строк (например, общий баланс по нескольким счетам пользователя), чтобы на их основе выполнить расчет (например, начислить процент).

Риск при READ COMMITTED: После чтения первого счета другая транзакция может изменить второй счет. Ваш расчет будет основан на несогласованных данных.

Решение REPEATABLE READ: После чтения всех связанных счетов они остаются «замороженными» для изменений до конца вашей транзакции, обеспечивая согласованность данных для расчета.

Цена: Повышенный риск блокировок и взаимоблокировок (deadlocks), так как транзакция удерживает блокировки на всех прочитанных данных.

Когда Repeatable read быть не достаточен? Например при «Проверке уникальности с сложным условием»

Контекст: Вы должны быть уверены, что в системе нет другой заявки с таким же сочетанием полей (например, «Имя + Фамилия + Дата рождения»), прежде чем создать новую.

Риск на REPEATABLE READ: Этот уровень не защищает от фантомов. Другая транзакция может вставить новую строку, удовлетворяющую вашему условию, после вашего проверочного SELECT-а, но до вашего INSERT-а.

Решение SERIALIZABLE: На этом уровне блокируется не только существующие строки, но и диапазон индекса, куда могла бы вставиться такая строка. Это предотвращает вставку «фантома» и гарантирует уникальность.

Когда Repeatable read подходит:

· Важно, чтобы прочитанные данные не изменялись до конца транзакции

· Транзакции на изменения могут подождать

· Появление новых строк не критично.

Serializable – упорядоченный

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

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

Например, если транзакция включает инструкцию

SELECT * FROM Orders

диапазон представляет собой всю таблицу Orders. Транзакция считывает таблицу и не разрешает вставлять в нее новые строки.

Если транзакция включает инструкцию

DELETE FROM Orders WHERE Status = "CLOSED"

Диапазон состоит из всех строк с состоянием «CLOSED». Транзакция блокирует все строки в таблице «Заказы» с состоянием «CLOSED» и не позволяет вставлять или обновлять строки, чтобы результирующая строка имела состояние «CLOSED».

Типичные сценарии:

Критически важные финансовые операции.

Контекст: Перевод денег между счетами. Вы должны быть уверены, что:

— Сумма на исходном счете не изменилась после вашей проверки (защита от Non-Repeatable Read).

— Не появилось новых транзакций на целевом счете, которые могли бы изменить его итоговый баланс (защита от Phantom Read).

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

Пример:

-- Вся система работает на READ COMMITTED (или READ COMMITTED SNAPSHOT) -- Но эта конкретная транзакция должна быть максимально защищена  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION;      -- 1. Проверяем баланс на счете A. Блокируем строки от изменения до конца транзакции.     -- 2. Проверяем существование счета B. Блокируем строки от изменения до конца транзакции.     -- На уровне SERIALIZABLE мы гарантированно защищены от фантомов     -- и неповторяющегося чтения на этих шагах.      -- 3. Если все ок, списываем с A и зачисляем на B  COMMIT TRANSACTION; -- После коммита транзакция завершается, и соединение -- возвращается к уровню изоляции по умолчанию.

Частая ошибка: включение Serializable на уровне БД и в транзакциях где уровень избыточен.

Когда Serializable подходит:

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

· Добавление новых строк выполнялось после завершения транзакции.

Резюме

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

«Тщательно спроектировать приложение» помогает:

Выявление критических секций: Определение какие именно операции и над какими данными требуют абсолютной согласованности, какие запросы выполняются длительно, в каких местах возникают конфликты читателей\писателей.
Определение порядка доступа: Проектирование логики так, чтобы разные процессы всегда обращались к одним и тем же данным в одинаковом порядке. Это главный способ избежать взаимоблокировок (deadlocks).
- Плохо: Процесс А блокирует запись №1, потом пытается блокировать запись №2. Процесс Б блокирует запись №2, потом пытается блокировать запись №1. Результат — взаимоблокировка.
- Хорошо: Все процессы всегда сначала блокируют запись №1, а только потом — запись №2.
Уровни изоляции от задачи на: запрос, транзакцию, сессию, БД.
Короткие транзакции: Сокращать время удержания блокировок. Внутри транзакции — только самые необходимые операции с данными.

Для выбора уровня изоляции сводная таблица: