Модуль 07 — Транзакции, ACID и уровни изоляции

Как СУБД гарантирует целостность данных при параллельной работе нескольких клиентов

Прогресс курса Модуль 8 из 9

Что вы освоите в этом модуле

01

Что такое транзакция

Транзакция — группа SQL-операций, которые выполняются как одно целое: либо все, либо ни одна. Классический пример — перевод денег: нельзя списать с одного счёта без зачисления на другой.

-- Простая транзакция
BEGIN;                             -- начать транзакцию

UPDATE communities
SET    rating = 5
WHERE  name = 'TechTalks';

SELECT * FROM communities WHERE name = 'TechTalks';
-- В этой сессии уже видны изменения, в других — нет

COMMIT;                            -- зафиксировать изменения
-- Или: ROLLBACK; -- откатить все изменения в транзакции

ACID — гарантии транзакций

  • Atomicity (Атомарность) — всё или ничего. Если транзакция не завершена — никаких следов.
  • Consistency (Консистентность) — до и после транзакции данные соответствуют всем ограничениям.
  • Isolation (Изолированность) — параллельные транзакции не влияют друг на друга.
  • Durability (Долговечность) — зафиксированные изменения сохраняются даже при сбое.
02

Аномалии параллельного доступа

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

Четыре классические аномалии

  • Потерянное обновление (Lost Update) — сессия 1 читает значение, сессия 2 его обновляет и фиксирует, сессия 1 записывает поверх, «затирая» изменения сессии 2.
  • «Грязное» чтение (Dirty Read) — сессия 2 видит незафиксированные изменения сессии 1, которые потом откатываются.
  • Неповторяемое чтение (Non-Repeatable Read) — сессия 1 дважды читает одну строку внутри транзакции и получает разные результаты, потому что между чтениями сессия 2 изменила и зафиксировала данные.
  • Фантомное чтение (Phantom Read) — сессия 1 повторяет запрос и получает новые строки, добавленные сессией 2 между двумя запросами.
03

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

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

Уровни изоляции и аномалии

Уровень изоляции Dirty Read Non-Repeatable Phantom Read
READ UNCOMMITTEDВозможно¹ВозможноВозможно
READ COMMITTED (по умолчанию)НетВозможноВозможно
REPEATABLE READНетНетНет²
SERIALIZABLEНетНетНет

¹ PostgreSQL не реализует Dirty Read, ² PostgreSQL защищает от Phantom при REPEATABLE READ

-- Проверить текущий уровень изоляции
SHOW TRANSACTION ISOLATION LEVEL;

-- Установить уровень для транзакции
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ...операции...
COMMIT;

-- Установить для сессии
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
04

Воспроизведение аномалий — потерянное обновление

Для воспроизведения нужны две параллельные сессии (два окна psql).

Lost Update при READ COMMITTED

ВремяСессия 1Сессия 2
t1BEGIN;
SELECT rating FROM communities WHERE name='TechTalks'; → 4.6
t2BEGIN;
SELECT rating FROM communities WHERE name='TechTalks'; → 4.6
t3UPDATE communities SET rating=4 WHERE name='TechTalks';
COMMIT;
t4UPDATE communities SET rating=3.6 WHERE name='TechTalks';
COMMIT; — перезаписывает 4 → 3.6!
При REPEATABLE READ сессия 2 в t4 получит ошибку «could not serialize access» — PostgreSQL обнаружит конфликт. Это безопаснее, но требует повторных попыток на уровне приложения.
05

Взаимная блокировка (Deadlock)

Deadlock — ситуация, когда сессия 1 ждёт ресурс, захваченный сессией 2, а сессия 2 ждёт ресурс сессии 1. Бесконечное взаимное ожидание.

Пример Deadlock

Сессия 1Сессия 2
BEGIN;
UPDATE communities SET rating=1 WHERE id=1; — заблокировала строку 1		 BEGIN;
UPDATE communities SET rating=2 WHERE id=2; — заблокировала строку 2
UPDATE communities SET rating=1 WHERE id=2; — ждёт сессию 2 UPDATE communities SET rating=2 WHERE id=1; — ждёт сессию 1
PostgreSQL обнаружит deadlock и автоматически откатит одну из транзакций с ошибкой: ERROR: deadlock detected

Как избежать дедлоков

  • Всегда обновляйте строки в одном и том же порядке во всех транзакциях.
  • Минимизируйте время удержания блокировок — делайте транзакции короткими.
  • Используйте SELECT … FOR UPDATE для явной блокировки строк перед изменением.
  • При ошибке deadlock — просто повторите транзакцию.

Ключевые выводы модуля

Запомните главное

  • PostgreSQL по умолчанию работает на уровне READ COMMITTED — защищает от Dirty Read
  • REPEATABLE READ в PostgreSQL защищает также от Phantom Read (сильнее стандарта SQL)
  • При конфликте REPEATABLE READ PostgreSQL отдаёт ошибку — повторите транзакцию
  • Deadlock: PostgreSQL обнаруживает автоматически и откатывает одну из транзакций
  • Избегайте Deadlock: всегда обновляйте строки в одном порядке во всех транзакциях
Практические задания — на учебном портале. Задания, критерии оценивания, сдача работ и оценки преподавателя — в курсе на portal.nevabit.ru. Учётную запись выдаёт преподаватель.
Модуль 06: DDL Все модули Модуль 08: Функции