Что такое свойства ACID и как PostgreSQL их гарантирует?

Пример: демонстрация атомарности и изоляции

-- Сессия 1: перевод денег между счетами
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- До COMMIT сессия 2 не видит промежуточного состояния
COMMIT;

-- Проверяем уровень изоляции по умолчанию
SHOW transaction_isolation;
-- read committed

-- Для защиты от phantom reads используем REPEATABLE READ или SERIALIZABLE
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts;  -- снимок зафиксирован здесь
-- ... другие операции ...
COMMIT;

Что такое ACID

ACID — четыре свойства, которые PostgreSQL гарантирует для каждой транзакции:

• Atomicity (атомарность) — транзакция либо применяется целиком, либо не применяется вовсе. При сбое или ROLLBACK все изменения отменяются.
• Consistency (согласованность) — после фиксации данные соответствуют всем объявленным ограничениям: NOT NULL, UNIQUE, CHECK, FOREIGN KEY. Нарушение любого из них вызывает откат.
• Isolation (изоляция) — параллельные транзакции не видят промежуточных состояний друг друга. PostgreSQL реализует изоляцию через MVCC: каждая транзакция работает со своим снимком данных.
• Durability (долговечность) — зафиксированная транзакция сохранена на диске. WAL (Write-Ahead Log) гарантирует, что даже при аварийном завершении процесса данные не потеряются.

Как PostgreSQL реализует каждое свойство

Atomicity и Durability обеспечивает WAL: перед изменением страниц данных PostgreSQL записывает операцию в журнал. При старте после сбоя процесс recovery «доигрывает» незавершённые WAL-записи или откатывает незафиксированные транзакции.

Isolation реализована через MVCC (Multi-Version Concurrency Control): каждая строка хранит xmin (транзакция, создавшая версию) и xmax (транзакция, удалившая её). Читатели видят только те версии, которые были зафиксированы до их снимка — без блокировки писателей.

Consistency — ответственность ограничений схемы. PostgreSQL проверяет их в конце каждого оператора (или в конце транзакции для DEFERRABLE-ограничений).

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

PostgreSQL поддерживает четыре уровня, но фактически реализует три различных поведения:

• READ UNCOMMITTED — трактуется как READ COMMITTED (грязное чтение в PostgreSQL невозможно).
• READ COMMITTED (по умолчанию) — каждый оператор видит свежий снимок. Non-repeatable reads возможны.
• REPEATABLE READ — снимок фиксируется на начало транзакции. Phantom reads исключены за счёт MVCC.
• SERIALIZABLE — SSI (Serializable Snapshot Isolation): PostgreSQL обнаруживает аномалии и откатывает транзакцию с ошибкой 40001, если обнаружен конфликт сериализации.

Durability и fsync

-- Посмотреть текущие настройки надёжности записи
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('fsync', 'synchronous_commit', 'wal_level');

При synchronous_commit = off транзакция возвращает успех до записи WAL на диск — риск потери последних нескольких транзакций при аварии, но без нарушения целостности (только durability). fsync = off — опасно, допустимо только в тестах.

Подводные камни

• Уровень READ COMMITTED не защищает от non-repeatable reads: два одинаковых SELECT в одной транзакции могут вернуть разные результаты, если параллельная транзакция зафиксировалась между ними.
• MVCC создаёт мёртвые версии строк (dead tuples); без регулярного VACUUM таблица раздувается, индексы замедляются.
• SERIALIZABLE может откатывать транзакции с кодом 40001 — приложение обязано реализовать retry-логику.
• Внешние эффекты (отправка email, HTTP-запрос) не участвуют в транзакции: если COMMIT прошёл, а внешний вызов упал — данные сохранены, но действие не выполнено (и наоборот).
• DDL-операции (CREATE TABLE, ALTER TABLE) в PostgreSQL транзакционны, но некоторые (CREATE INDEX CONCURRENTLY) нельзя выполнять внутри явной транзакции.
• TRUNCATE транзакционен в PostgreSQL (в отличие от MySQL MyISAM), но держит ACCESS EXCLUSIVE lock на всю таблицу.
• Длинные транзакции блокируют autovacuum и не дают удалить старые версии строк — это ведёт к bloat и росту размера таблиц.
• synchronous_commit = local не гарантирует сохранность на standby при синхронной репликации — для этого нужен synchronous_standby_names.

• Отвечать про acid в postgresql как про абстрактный SQL без учета MVCC, WAL, planner или конкретной версии PostgreSQL.
• Переносить поведение MySQL или ClickHouse на PostgreSQL без проверки документации.
• Не связывать механизм с последствиями для индексов, транзакций, блокировок или эксплуатации.
• Показывать синтаксис, который не поддерживается PostgreSQL.

• Кандидат объясняет acid в postgresql через реальный механизм PostgreSQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.