Содержание
- Что такое ACID и какую задачу решают ACID-транзакции
- Четыре свойства ACID
- Atomicity, всё или ничего
- Consistency, только допустимые состояния
- Isolation, параллельные транзакции не мешают друг другу
- Durability, коммит переживает сбой
- Как PostgreSQL обеспечивает ACID под капотом
- MVCC и снимки данных
- WAL и надёжный коммит
- SSI и предикатные блокировки
- Уровни изоляции и аномалии в PostgreSQL
- ACID, BASE и lakehouse-форматы
- Когда строгий ACID нужен и когда его ослабляют
- Практика, ACID-транзакции на PostgreSQL 18
- Стенд и запуск
- Atomicity и откат упавшего перевода
- Consistency и ограничения схемы
- Изоляция на двух сессиях
- Write skew на Repeatable Read и Serializable
- Durability после аварийной остановки
- Заключение
- Референсные ссылки
ACID (Atomicity, Consistency, Isolation, Durability, атомарность, согласованность, изоляция, долговечность) это четыре гарантии, которые база данных даёт каждой транзакции. Изменения транзакции применяются целиком или не применяются вовсе, база переходит только между допустимыми состояниями, параллельные транзакции не видят чужих промежуточных результатов, а подтверждённый коммит переживает сбой сервера. На ACID-транзакциях держатся реляционные OLTP-системы (Online Transaction Processing, оперативная обработка транзакций) вроде PostgreSQL, а в озёрах данных те же гарантии дают табличные форматы Delta Lake и Apache Iceberg.
Ниже разобрано, каким механизмом PostgreSQL 18 закрывает каждую букву и почему буква I на практике означает выбор уровня изоляции. Сценарии из практики воспроизведены на PostgreSQL 18.6, остальное опирается на официальную документацию.
Что такое ACID и какую задачу решают ACID-транзакции
Транзакция это группа операций, которую база выполняет как одно целое. Классический пример это перевод денег, где надо списать 30 с одного счёта и зачислить 30 на другой. Приложение может упасть между двумя UPDATE, параллельный перевод может прочитать баланс посередине, а сервер может отключиться сразу после ответа «готово».
ACID сводит эти угрозы к четырём свойствам и работает как контракт. Приложение группирует операции и обрабатывает ошибки, а база обещает результат целый или никакой, но не промежуточный.
Четыре свойства ACID
Все четыре свойства видны на одном переводе между счетами alice и bob, от пары списание и зачисление до записи в журнал перед ответом клиенту.
Atomicity, всё или ничего
Atomicity (атомарность) означает, что транзакция неделима. Если любая операция внутри неё падает, база отменяет все изменения транзакции, включая уже выполненные. Другие транзакции увидят либо оба UPDATE перевода, либо ни одного.
В PostgreSQL у атомарности есть неочевидная деталь. После первой ошибки транзакция переходит в состояние aborted и отвергает все команды, а COMMIT в ней возвращает тег ROLLBACK вместо ошибки. Приложение, которое не проверило предыдущий шаг, решит, что перевод прошёл. Частичный откат даёт точка сохранения (savepoint) и команда ROLLBACK TO SAVEPOINT.
Consistency, только допустимые состояния
Consistency (согласованность, иногда консистентность) гарантирует, что транзакция переводит базу из одного допустимого состояния в другое. Допустимость задают ограничения схемы, а именно CHECK, FOREIGN KEY, UNIQUE и NOT NULL. Если при выполнении транзакции нарушено хотя бы одно ограничение, она не фиксируется целиком, даже если нарушение случилось на последнем операторе.
Эта буква наполовину лежит на приложении, потому что база проверяет только объявленные ей правила. Инвариант «на дежурстве хотя бы один врач» не выражается ограничением на одну строку и уходит в зону изоляции. Проверку внешнего ключа можно отложить до коммита через DEFERRABLE, а CHECK откладывать нельзя.
Isolation, параллельные транзакции не мешают друг другу
Isolation (изоляция) определяет, что видит транзакция, пока рядом работают другие. Идеал это сериализуемость (serializability), когда результат совпадает с каким-нибудь последовательным порядком тех же транзакций. Она дорога, поэтому стандарт SQL вводит четыре уровня изоляции, и уровни ниже высшего разрешают свои аномалии.
Durability, коммит переживает сбой
Durability (долговечность) означает, что подтверждённый COMMIT переживает падение процесса, ОС или питания, для чего запись о коммите попадает в энергонезависимую память до ответа клиенту. Документация PostgreSQL предупреждает, что многие SSD по умолчанию не выполняют команды сброса кэша. Проверить своё хранилище помогает утилита pg_test_fsync.
Как PostgreSQL обеспечивает ACID под капотом
Атомарность и изоляцию в PostgreSQL обеспечивает MVCC, долговечность журнал WAL, сериализуемость SSI, а согласованность ограничения схемы.
MVCC и снимки данных
MVCC (Multi-Version Concurrency Control, многоверсионное управление конкурентным доступом) хранит несколько версий строки. UPDATE не перезаписывает строку, а создаёт новую версию. Поле xmin хранит номер создавшей версию транзакции, xmax номер той, что её заменила или удалила. Оператор видит снимок данных (snapshot) и по этим полям выбирает видимую версию, поэтому чтение не блокирует запись и наоборот.
Отсюда и атомарность. В прогоне упавшая транзакция 757 оставила на странице новую версию строки, а старой проставила xmax = 757. Откат ничего не стирает, он помечает транзакцию прерванной в pg_xact, журнале статусов транзакций, и её версии становятся невидимыми.
WAL и надёжный коммит
WAL (Write-Ahead Log, журнал предзаписи) это последовательный журнал изменений. Страницы данных меняются на диске только после того, как записи журнала о них сброшены на носитель. При COMMIT сервер сбрасывает журнал и только потом отвечает клиенту. После сбоя журнал проигрывается от последней контрольной точки, а незавершённые транзакции остаются невидимыми, потому что записи об их коммите нет.
SSI и предикатные блокировки
SSI (Serializable Snapshot Isolation, сериализуемая изоляция снимков) реализует Serializable в PostgreSQL с версии 9.1. Транзакции работают на снимках, как на Repeatable Read, а сервер отслеживает зависимости чтение-запись через предикатные блокировки SIReadLock. Они ничего не блокируют, а только запоминают, кто что читал. Если зависимости не укладываются в последовательный порядок, одна транзакция получает ошибку SQLSTATE 40001.
При последовательном сканировании SIReadLock ставится сразу на всю таблицу, и от этого растёт число ложных конфликтов. Объём таблицы блокировок задаёт max_pred_locks_per_transaction, по умолчанию 64.
Greenplum для инженеров данных и аналитиков данных
Код курса
GPDE
Ближайшая дата курса
11 января, 2027
Продолжительность
24 ак.часов
Стоимость обучения
76 800
Уровни изоляции и аномалии в PostgreSQL
Стандарт SQL описывает уровни через запрещённые аномалии. Грязное чтение (dirty read) видит незафиксированные чужие данные. Неповторяемое чтение (nonrepeatable read) видит в повторном SELECT изменения чужой зафиксированной транзакции. Фантом (phantom read) это новые строки по тому же условию. Аномалия сериализации даёт итог, невозможный ни при каком последовательном порядке. Сводка взята из раздела Transaction Isolation документации PostgreSQL 18 и подтверждена прогоном.
| Уровень | Грязное чтение | Неповторяемое чтение | Фантом | Аномалия сериализации |
|---|---|---|---|---|
| Read Uncommitted | допускается стандартом, в PostgreSQL невозможно | возможно | возможен | возможна |
| Read Committed (по умолчанию) | невозможно | возможно | возможен | возможна |
| Repeatable Read | невозможно | невозможно | допускается стандартом, в PostgreSQL невозможен | возможна |
| Serializable | невозможно | невозможно | невозможен | невозможна |
Различимых уровней в PostgreSQL три. Read Uncommitted ведёт себя как Read Committed, хотя SHOW transaction_isolation отвечает read uncommitted. На Read Committed каждый оператор видит данные, зафиксированные до его начала. Repeatable Read реализован как Snapshot Isolation, снимок берётся на первом операторе и держится до конца транзакции.
Самая коварная аномалия называется write skew (косая запись). Две транзакции читают одни данные, решают по прочитанному и меняют разные строки, поэтому конфликта записи нет, а общий инвариант нарушен. В прогоне два дежурных врача одновременно уходят со смены. На Repeatable Read обе транзакции фиксируются, и на дежурстве никого. На Serializable вторая получает 40001, а повтор оставляет врача на смене.
Цена Serializable в повторах. Приложение ловит SQLSTATE 40001 и повторяет транзакцию целиком, потому что прочитанному в отменённой транзакции доверять нельзя. Для длинных отчётов есть SERIALIZABLE READ ONLY DEFERRABLE, такая транзакция сначала ждёт снимка, на котором аномалия невозможна. Как уровни изоляции и MVCC работают в распределённой СУБД на основе PostgreSQL, разбирает статья блога о распределённых транзакциях и уровнях изоляции в Greenplum.
ACID, BASE и lakehouse-форматы
BASE (Basically Available, Soft state, Eventually consistent, базовая доступность, гибкое состояние, согласованность в конечном счёте) это противоположный набор компромиссов части распределённых NoSQL-хранилищ. Система отвечает всегда, пусть устаревшими данными, а реплики сходятся со временем. Спецификации у BASE нет, гарантии каждой системы читаются в её документации.
| Критерий | ACID | BASE |
|---|---|---|
| Приоритет | корректность каждой транзакции | доступность и масштаб записи |
| Что видит читатель | зафиксированные данные в пределах уровня изоляции | возможно устаревшие данные, реплики сходятся со временем |
| Конкурентная запись | блокировки, снимки, ошибка и повтор при конфликте | разрешение конфликтов после записи, часто «последняя запись побеждает» |
| Поведение при сбое узла или сети | часть операций ждёт или отклоняется | операции продолжаются, расхождение устраняется позже |
| Типичные задачи | платежи, остатки, бронирования, учёт | ленты, счётчики, каталоги, телеметрия |
Вокруг буквы C частая путаница. Consistency в ACID означает соблюдение ограничений и инвариантов. C в CAP-теореме (Consistency, Availability, Partition tolerance) означает, что все узлы отвечают так, будто копия данных одна. PostgreSQL с асинхронной репликой соблюдает ACID на основном сервере, но чтение с реплики может вернуть устаревшее значение.
У объектного хранилища нет транзакций, поэтому ACID в озере данных строится на метаданных. В Apache Iceberg изменение таблицы создаёт новый файл метаданных и подменяет старый атомарно, и спецификация называет такую подмену основой сериализуемой изоляции. Читатель работает со снимком, текущим на момент загрузки метаданных. Если версия сменилась за время записи, писатель повторяет коммит.
Delta Lake применяет оптимистичное управление конкурентностью (optimistic concurrency control). Операция читает версию таблицы, пишет новые файлы и перед коммитом проверяет конфликт с тем, что зафиксировано после чтения. При конфликте она падает с исключением вроде ConcurrentAppendException, а таблица остаётся целой. Два INSERT не мешают друг другу, а UPDATE, DELETE и MERGE INTO конфликтуют на общих файлах.
Архитектура Данных
Код курса
ARMG
Ближайшая дата курса
21 декабря, 2026
Продолжительность
24 ак.часов
Стоимость обучения
76 800
Когда строгий ACID нужен и когда его ослабляют
Ослабляют обычно не ACID целиком, а одну букву для одного потока данных, и решение зависит от цены ошибки:
- Деньги, остатки, бронирования. Нужен полный ACID, а инварианты между строками закрываются уровнем Serializable с повтором по 40001 или явной блокировкой SELECT FOR UPDATE.
- Обычные OLTP-операции. Read Committed хватает, если значение считается в SQL вроде balance = balance + 10. Если приложение читает число и пишет результат из кода, второе обновление перетрёт первое.
- Метрики, логи, клики. Для таких сессий ставят synchronous_commit = off. В прогоне к статье про WAL на том же стенде это подняло пропускную способность со 150-156 до 4.2-5.9 тыс. транзакций в секунду ценой потери последних долей секунды при сбое.
- Аналитика на озере данных. Delta Lake и Iceberg дают атомарный коммит и снимки, но конкурентная запись в одни файлы оборачивается повторами. Документация Delta Lake советует партиционировать таблицы по столбцам, которые стоят в условиях изменений.
Таким образом, выбор сводится к вопросу, какую аномалию бизнес готов пережить и сколько стоит её исключить.
Практика, ACID-транзакции на PostgreSQL 18
По традиции весь код используемый в статье выкладываем на наш GitHub репозиторий
Стенд это PostgreSQL 18.6 в Docker Compose и psycopg 3.3.8 на Python 3.12, прогон шёл 2026-11-05 на Ubuntu 24.04 и занял 13 секунд.
Стенд и запуск
Сервер описан файлом docker-compose.yml, порт открыт только на локальном интерфейсе.
# postgres 18.6 в Docker, прогнано на стенде 2026-11-05: один сервер для демо ACID-транзакций
services:
pg:
image: postgres:18.6
container_name: acid-pg
mem_limit: 1g
environment:
POSTGRES_USER: demo
POSTGRES_PASSWORD: demo
POSTGRES_DB: acid
ports:
- "127.0.0.1:55435:5432"
volumes:
- pgdata:/var/lib/postgresql
# SQL-сценарии видны внутри контейнера в /demo/sql
- ./sql:/demo/sql:ro
healthcheck:
# -h 127.0.0.1: на время initdb временный сервер слушает только сокет, по TCP готов уже рабочий
test: ["CMD-SHELL", "pg_isready -h 127.0.0.1 -U demo -d acid"]
interval: 2s
timeout: 3s
retries: 30
volumes:
pgdata:
Драйвер для сценариев изоляции ставится в виртуальное окружение по файлу requirements.txt.
# psycopg 3.3.8 с бинарной libpq, прогнано на стенде 2026-11-05 psycopg[binary]==3.3.8
Скрипт acid_demo.sh гоняет SQL-сценарии через psql в контейнере, изоляцию через isolation_demo.py и убивает сервер сигналом KILL.
#!/usr/bin/env bash
# postgres 18.6 в Docker, psycopg 3.3.8, прогнано на стенде 2026-11-05
# Пять сценариев ACID по очереди: ./acid_demo.sh all
# или по одному: ./acid_demo.sh atomicity|consistency|isolation|write_skew|durability
set -uo pipefail
cd "$(dirname "$0")" || exit 1
# -e печатает каждый запрос перед результатом, 2>&1 внутри контейнера держит ошибки рядом с запросом
psql_file() {
docker compose exec -T pg sh -c "psql -U demo -d acid -X -e -v ON_ERROR_STOP=1 -f /demo/sql/$1 2>&1"
}
PY=${PY:-.venv/bin/python}
atomicity() { psql_file 01_atomicity.sql; }
consistency() { psql_file 02_consistency.sql; }
isolation() { "$PY" isolation_demo.py reads lost_update; }
write_skew() { "$PY" isolation_demo.py write_skew; }
durability() {
psql_file 05_before_crash.sql
# незафиксированная транзакция висит в отдельной сессии в момент сбоя
docker compose exec -T pg psql -U demo -d acid -X -q \
-c "BEGIN; INSERT INTO orders (note) VALUES ('не зафиксирован'); SELECT pg_sleep(120);" \
>/dev/null 2>&1 &
local bg=$!
for _ in $(seq 30); do
n=$(docker compose exec -T pg psql -U demo -d acid -XAtc \
"SELECT count(*) FROM pg_stat_activity WHERE state = 'active' AND query LIKE '%pg_sleep(120)%' AND pid <> pg_backend_pid()")
[ "$n" = 1 ] && break
sleep 1
done
echo "--- сессий с открытой транзакцией и вставкой: $n"
local since
since=$(date -u '+%Y-%m-%dT%H:%M:%SZ')
echo "--- docker kill -s KILL acid-pg: сервер убит без контрольной точки"
docker kill -s KILL acid-pg >/dev/null
wait "$bg" 2>/dev/null
docker compose up -d --wait 2>&1 | tail -n 1
echo "--- лог сервера после рестарта"
docker logs --since "$since" acid-pg 2>&1 \
| grep -E "interrupted|not properly shut down|redo starts|redo done|ready to accept" \
| sed -E 's/^[0-9-]+ [0-9:.]+ UTC \[[0-9]+\] //'
psql_file 05_after_crash.sql
}
case ${1:-all} in
atomicity|consistency|isolation|write_skew|durability) "$1" ;;
all)
echo "=== 1. Atomicity ==="; atomicity
echo; echo "=== 2. Consistency ==="; consistency
echo; echo "=== 3. Isolation: чтения и потерянное обновление ==="; isolation
echo; echo "=== 4. Isolation: write skew ==="; write_skew
echo; echo "=== 5. Durability ==="; durability ;;
*) echo "использование: $0 all|atomicity|consistency|isolation|write_skew|durability" >&2; exit 2 ;;
esac
Запуск с нуля занимает четыре команды.
# PostgreSQL 18.6, Docker Compose 2.40.3, Python 3.12.3, проверено на стенде 2026-11-05 python3 -m venv .venv && .venv/bin/pip install -r requirements.txt docker compose up -d --wait ./acid_demo.sh all docker compose down -v
Atomicity и откат упавшего перевода
Сценарий переводит 30 от alice к bob и посреди перевода получает деление на ноль, а потом заглядывает на страницу таблицы через расширение pageinspect.
-- postgres 18.6 в Docker, прогнано на стенде 2026-11-05
-- Atomicity: перевод из двух UPDATE либо применяется целиком, либо не применяется вовсе.
-- Ошибки в этом файле ожидаемые, поэтому psql не останавливается на них.
\set ON_ERROR_STOP off
CREATE EXTENSION IF NOT EXISTS pageinspect;
DROP TABLE IF EXISTS accounts CASCADE;
CREATE TABLE accounts (
id int PRIMARY KEY,
owner text NOT NULL,
balance numeric(12,2) NOT NULL
);
INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50);
\echo '--- 1.1 до перевода'
SELECT id, owner, balance FROM accounts ORDER BY id;
\echo '--- 1.2 перевод 30 от alice к bob, ошибка после первого UPDATE'
BEGIN;
SELECT pg_current_xact_id() AS failed_xid \gset
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
-- внутри транзакции списание уже видно
SELECT id, balance FROM accounts ORDER BY id;
-- имитация сбоя приложения посреди перевода
SELECT 1 / 0;
-- после ошибки транзакция в состоянии aborted, любые команды отвергаются
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
-- COMMIT в такой транзакции сервер выполняет как ROLLBACK
COMMIT;
\echo '--- 1.3 после неудачного перевода балансы не изменились'
SELECT id, owner, balance FROM accounts ORDER BY id;
SELECT :'failed_xid' AS failed_xid, pg_xact_status(:'failed_xid'::xid8) AS status;
\echo '--- 1.4 версия строки от отменённой транзакции физически лежит на странице, но невидима'
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0))
ORDER BY lp;
\echo '--- 1.5 SAVEPOINT: откат части транзакции без потери остального'
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
ROLLBACK TO SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;
# вывод прогона на стенде 2026-11-05, PostgreSQL 18.6, фрагмент run_output.txt SELECT 1 / 0; psql:/demo/sql/01_atomicity.sql:24: ERROR: division by zero UPDATE accounts SET balance = balance + 30 WHERE id = 2; psql:/demo/sql/01_atomicity.sql:26: ERROR: current transaction is aborted, commands ignored until end of transaction block COMMIT; ROLLBACK --- 1.3 после неудачного перевода балансы не изменились SELECT id, owner, balance FROM accounts ORDER BY id; id | owner | balance ----+-------+--------- 1 | alice | 100.00 2 | bob | 50.00 (2 rows) failed_xid | status ------------+--------- 757 | aborted (1 row) --- 1.4 версия строки от отменённой транзакции физически лежит на странице, но невидима lp | t_xmin | t_xmax | t_ctid ----+--------+--------+-------- 1 | 756 | 757 | (0,3) 2 | 756 | 0 | (0,2) 3 | 757 | 0 | (0,3) (3 rows) --- 1.5 SAVEPOINT: откат части транзакции без потери остального id | owner | balance ----+-------+--------- 1 | alice | 90.00 2 | bob | 60.00 (2 rows)
На COMMIT сервер ответил ROLLBACK, балансы остались 100.00 и 50.00, транзакция 757 в статусе aborted. Точка сохранения отменила бонус 1000, остальное зафиксировалось.
Consistency и ограничения схемы
Второй сценарий добавляет CHECK на баланс и таблицу переводов с откладываемыми внешними ключами.
-- postgres 18.6 в Docker, прогнано на стенде 2026-11-05
-- Consistency: ограничения схемы не дают зафиксировать недопустимое состояние.
-- Ошибки в этом файле ожидаемые, поэтому psql не останавливается на них.
\set ON_ERROR_STOP off
DROP TABLE IF EXISTS transfers, accounts CASCADE;
CREATE TABLE accounts (
id int PRIMARY KEY,
owner text NOT NULL,
balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);
-- внешние ключи объявлены откладываемыми: по умолчанию проверяются сразу,
-- но внутри транзакции проверку можно перенести на COMMIT
CREATE TABLE transfers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
from_id int NOT NULL REFERENCES accounts (id) DEFERRABLE INITIALLY IMMEDIATE,
to_id int NOT NULL REFERENCES accounts (id) DEFERRABLE INITIALLY IMMEDIATE,
amount numeric(12,2) NOT NULL CHECK (amount > 0)
);
INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50);
\echo '--- 2.1 перевод 150 при балансе 100: CHECK (balance >= 0) отменяет всю транзакцию'
BEGIN;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 150);
UPDATE accounts SET balance = balance + 150 WHERE id = 2;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;
SELECT count(*) AS transfers_rows FROM transfers;
\echo '--- 2.2 перевод на несуществующий счёт: внешний ключ проверяется сразу'
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 99, 10);
\echo '--- 2.3 отложенная проверка: перевод раньше счёта, счёт создан до COMMIT'
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 3, 10);
INSERT INTO accounts VALUES (3, 'carol', 0);
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 3;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;
\echo '--- 2.4 отложенная проверка: счёт так и не создан, ошибка приходит на COMMIT'
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 4, 10);
SELECT count(*) AS transfers_inside_txn FROM transfers;
COMMIT;
SELECT count(*) AS transfers_rows FROM transfers;
# вывод прогона на стенде 2026-11-05, PostgreSQL 18.6, фрагмент run_output.txt
UPDATE accounts SET balance = balance - 150 WHERE id = 1;
psql:/demo/sql/02_consistency.sql:25: ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL: Failing row contains (1, alice, -50.00).
COMMIT;
ROLLBACK
transfers_rows
----------------
0
(1 row)
--- 2.2 перевод на несуществующий счёт: внешний ключ проверяется сразу
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 99, 10);
psql:/demo/sql/02_consistency.sql:31: ERROR: insert or update on table "transfers" violates foreign key constraint "transfers_to_id_fkey"
DETAIL: Key (to_id)=(99) is not present in table "accounts".
--- 2.3 отложенная проверка: перевод раньше счёта, счёт создан до COMMIT
3 | carol | 10.00
--- 2.4 отложенная проверка: счёт так и не создан, ошибка приходит на COMMIT
transfers_inside_txn
----------------------
2
(1 row)
COMMIT;
psql:/demo/sql/02_consistency.sql:48: ERROR: insert or update on table "transfers" violates foreign key constraint "transfers_to_id_fkey"
DETAIL: Key (to_id)=(4) is not present in table "accounts".
transfers_rows
----------------
1
(1 row)
Нарушение CHECK на последнем UPDATE отменило и вставку в transfers, и зачисление bob. С отложенной проверкой перевод на счёт carol прошёл, а ошибка внешнего ключа для счёта 4 пришла на самом COMMIT.
Изоляция на двух сессиях
Скрипт isolation_demo.py держит сессии A и B и выполняет их операторы строго по очереди, поэтому аномалии воспроизводятся без гонки.
# psycopg 3.3.8, postgres 18.6 в Docker, прогнано на стенде 2026-11-05
"""Изоляция транзакций в PostgreSQL на двух сессиях с явным чередованием шагов.
Каждый шаг выполняется по очереди, а не наперегонки, поэтому аномалии воспроизводятся
детерминированно, без зависимости от таймингов.
python isolation_demo.py all # все сценарии
python isolation_demo.py reads lost_update # несколько сценариев за один запуск
python isolation_demo.py reads # грязное, неповторяемое и фантомное чтение
python isolation_demo.py lost_update # потерянное обновление на RC и RR
python isolation_demo.py write_skew # write skew на RR и SERIALIZABLE, повтор по 40001
Адрес сервера берётся из ACID_DSN, по умолчанию локальный контейнер из docker-compose.yml.
"""
import os
import sys
from decimal import Decimal
import psycopg
sys.stdout.reconfigure(line_buffering=True)
DSN = os.environ.get(
"ACID_DSN", "host=127.0.0.1 port=55435 dbname=acid user=demo password=demo"
)
def fmt(value):
"""Decimal и прочее печатаем как в psql, без обёрток Python."""
return value if isinstance(value, (int, bool)) or value is None else str(value)
class Session:
"""Одна сессия PostgreSQL. autocommit=True, чтобы BEGIN и COMMIT были видны явно."""
def __init__(self, name):
self.name = name
self.conn = psycopg.connect(DSN, autocommit=True)
def run(self, sql, params=None):
# в лог идёт запрос с подставленными значениями, на сервер уходят параметры отдельно
shown = sql % tuple(f"'{p}'" if isinstance(p, str) else str(p) for p in params) if params else sql
print(f"[{self.name}] {shown}")
try:
cur = self.conn.execute(sql, params)
except psycopg.Error as e:
# SQLSTATE и класс исключения psycopg: по ним приложение решает, повторять ли
print(f" !! SQLSTATE {e.sqlstate} {type(e).__name__}: {e.diag.message_primary}")
return e
if cur.description:
rows = [tuple(fmt(v) for v in row) for row in cur.fetchall()]
print(f" -> {rows}")
return rows
return None
def close(self):
self.conn.close()
def setup(admin):
admin.run("DROP TABLE IF EXISTS accounts, doctors CASCADE")
admin.run(
"CREATE TABLE accounts (id int PRIMARY KEY, owner text NOT NULL, "
"balance numeric(12,2) NOT NULL)"
)
admin.run("CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL)")
def reset_accounts(admin):
admin.run("TRUNCATE accounts")
admin.run("INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 50)")
def reads(admin, a, b):
"""Чтения внутри транзакции A, пока B меняет данные."""
print("\n=== 3.1 Грязное чтение: READ UNCOMMITTED в PostgreSQL ведёт себя как READ COMMITTED ===")
reset_accounts(admin)
b.run("BEGIN")
b.run("UPDATE accounts SET balance = 0 WHERE id = 1")
a.run("BEGIN ISOLATION LEVEL READ UNCOMMITTED")
a.run("SHOW transaction_isolation")
a.run("SELECT balance FROM accounts WHERE id = 1")
a.run("COMMIT")
b.run("ROLLBACK")
for level in ("READ COMMITTED", "REPEATABLE READ"):
print(f"\n=== 3.2 Неповторяемое и фантомное чтение на {level} ===")
reset_accounts(admin)
a.run(f"BEGIN ISOLATION LEVEL {level}")
a.run("SELECT balance FROM accounts WHERE id = 1")
a.run("SELECT count(*) FROM accounts WHERE balance >= 50")
# B меняет строку и добавляет новую, подходящую под условие A, и фиксирует
b.run("UPDATE accounts SET balance = balance - 30 WHERE id = 1")
b.run("INSERT INTO accounts VALUES (3, 'carol', 500)")
a.run("SELECT balance FROM accounts WHERE id = 1")
a.run("SELECT count(*) FROM accounts WHERE balance >= 50")
a.run("COMMIT")
def lost_update(admin, a, b):
"""Чтение, вычисление в приложении, запись: классическое потерянное обновление."""
for level in ("READ COMMITTED", "REPEATABLE READ"):
print(f"\n=== 3.3 Потерянное обновление на {level}: A кладёт +10, B кладёт +20 ===")
reset_accounts(admin)
a.run(f"BEGIN ISOLATION LEVEL {level}")
b.run(f"BEGIN ISOLATION LEVEL {level}")
bal_a = a.run("SELECT balance FROM accounts WHERE id = 1")[0][0]
bal_b = b.run("SELECT balance FROM accounts WHERE id = 1")[0][0]
# новое значение посчитано в приложении по прочитанному, а не выражением в SQL
a.run("UPDATE accounts SET balance = %s WHERE id = 1", (Decimal(bal_a) + 10,))
a.run("COMMIT")
res = b.run("UPDATE accounts SET balance = %s WHERE id = 1", (Decimal(bal_b) + 20,))
if isinstance(res, psycopg.errors.SerializationFailure):
b.run("ROLLBACK")
# повтор транзакции целиком: новый снимок, новое чтение, новая запись
print(" .. повтор транзакции B целиком")
b.run(f"BEGIN ISOLATION LEVEL {level}")
bal_b = b.run("SELECT balance FROM accounts WHERE id = 1")[0][0]
b.run("UPDATE accounts SET balance = %s WHERE id = 1", (Decimal(bal_b) + 20,))
b.run("COMMIT")
admin.run("SELECT balance AS final_balance FROM accounts WHERE id = 1")
def go_off_duty(s, name):
"""Врач уходит с дежурства, только если на дежурстве останется хотя бы один."""
on_call = s.run("SELECT count(*) FROM doctors WHERE on_call")[0][0]
if on_call >= 2:
return s.run("UPDATE doctors SET on_call = false WHERE name = %s", (name,))
print(f" .. {name} остаётся: на дежурстве {on_call}")
return None
def write_skew(admin, a, b):
"""Инвариант «на дежурстве хотя бы один врач» не выражен ограничением схемы."""
for level in ("REPEATABLE READ", "SERIALIZABLE"):
print(f"\n=== 4. Write skew на {level}: alice и bob одновременно уходят с дежурства ===")
admin.run("TRUNCATE doctors")
admin.run("INSERT INTO doctors VALUES ('alice', true), ('bob', true)")
a.run(f"BEGIN ISOLATION LEVEL {level}")
b.run(f"BEGIN ISOLATION LEVEL {level}")
go_off_duty(a, "alice")
res_update = go_off_duty(b, "bob")
if level == "SERIALIZABLE":
# предикатные блокировки SSI: ничего не блокируют, только фиксируют, кто что читал
admin.run(
"SELECT CASE l.pid WHEN %s THEN 'A' WHEN %s THEN 'B' END AS session, "
"l.locktype, c.relname, l.mode "
"FROM pg_locks l LEFT JOIN pg_class c ON c.oid = l.relation "
"WHERE l.mode = 'SIReadLock' ORDER BY l.pid, c.relname",
(a.conn.info.backend_pid, b.conn.info.backend_pid),
)
a.run("COMMIT")
res = b.run("COMMIT")
failure = psycopg.errors.SerializationFailure
if isinstance(res, failure) or isinstance(res_update, failure):
print(" .. повтор транзакции B целиком")
b.run(f"BEGIN ISOLATION LEVEL {level}")
go_off_duty(b, "bob")
b.run("COMMIT")
admin.run("SELECT name, on_call FROM doctors ORDER BY name")
admin.run("SELECT count(*) FILTER (WHERE on_call) AS on_call_now FROM doctors")
def main():
what = sys.argv[1:] or ["all"]
scenarios = {"reads": reads, "lost_update": lost_update, "write_skew": write_skew}
if what != ["all"] and not set(what) <= set(scenarios):
print(f"использование: {sys.argv[0]} all|{'|'.join(scenarios)}", file=sys.stderr)
return 2
admin, a, b = Session("admin"), Session("A"), Session("B")
try:
admin.run("SHOW default_transaction_isolation")
admin.run("SHOW max_pred_locks_per_transaction")
setup(admin)
for name, fn in scenarios.items():
if what == ["all"] or name in what:
fn(admin, a, b)
finally:
for s in (admin, a, b):
s.close()
return 0
if __name__ == "__main__":
sys.exit(main())
# вывод прогона на стенде 2026-11-05, PostgreSQL 18.6, psycopg 3.3.8, фрагмент run_output.txt
=== 3.1 Грязное чтение: READ UNCOMMITTED в PostgreSQL ведёт себя как READ COMMITTED ===
[A] SHOW transaction_isolation
-> [('read uncommitted',)]
[A] SELECT balance FROM accounts WHERE id = 1
-> [('100.00',)]
=== 3.2 Неповторяемое и фантомное чтение на READ COMMITTED ===
[A] SELECT balance FROM accounts WHERE id = 1
-> [('70.00',)]
[A] SELECT count(*) FROM accounts WHERE balance >= 50
-> [(3,)]
=== 3.2 Неповторяемое и фантомное чтение на REPEATABLE READ ===
[A] SELECT balance FROM accounts WHERE id = 1
-> [('100.00',)]
[A] SELECT count(*) FROM accounts WHERE balance >= 50
-> [(2,)]
=== 3.3 Потерянное обновление на READ COMMITTED: A кладёт +10, B кладёт +20 ===
[admin] SELECT balance AS final_balance FROM accounts WHERE id = 1
-> [('120.00',)]
=== 3.3 Потерянное обновление на REPEATABLE READ: A кладёт +10, B кладёт +20 ===
[B] UPDATE accounts SET balance = 120.00 WHERE id = 1
!! SQLSTATE 40001 SerializationFailure: could not serialize access due to concurrent update
[B] ROLLBACK
.. повтор транзакции B целиком
[B] BEGIN ISOLATION LEVEL REPEATABLE READ
[B] SELECT balance FROM accounts WHERE id = 1
-> [('110.00',)]
[B] UPDATE accounts SET balance = 130.00 WHERE id = 1
[B] COMMIT
[admin] SELECT balance AS final_balance FROM accounts WHERE id = 1
-> [('130.00',)]
Незафиксированный нулевой баланс A не увидела даже на Read Uncommitted. На Read Committed повторные чтения дали 70.00 и три строки вместо 100.00 и двух, на Repeatable Read снимок не сдвинулся. Потерянное обновление на Read Committed дало 120.00 вместо 130.00, а на Repeatable Read запись B упала с 40001, и повтор дал 130.00.
Write skew на Repeatable Read и Serializable
Тот же скрипт запускает сценарий с врачами командой isolation_demo.py write_skew. Каждая сессия снимает своего врача с дежурства, только если видит на смене двоих.
# вывод прогона на стенде 2026-11-05, PostgreSQL 18.6, psycopg 3.3.8, фрагмент run_output.txt
=== 4. Write skew на REPEATABLE READ: alice и bob одновременно уходят с дежурства ===
[A] SELECT count(*) FROM doctors WHERE on_call
-> [(2,)]
[A] UPDATE doctors SET on_call = false WHERE name = 'alice'
[B] SELECT count(*) FROM doctors WHERE on_call
-> [(2,)]
[B] UPDATE doctors SET on_call = false WHERE name = 'bob'
[A] COMMIT
[B] COMMIT
[admin] SELECT count(*) FILTER (WHERE on_call) AS on_call_now FROM doctors
-> [(0,)]
=== 4. Write skew на SERIALIZABLE: alice и bob одновременно уходят с дежурства ===
-> [('A', 'relation', 'doctors', 'SIReadLock'), ('A', 'page', 'doctors_pkey', 'SIReadLock'), ('B', 'relation', 'doctors', 'SIReadLock'), ('B', 'page', 'doctors_pkey', 'SIReadLock')]
[A] COMMIT
[B] COMMIT
!! SQLSTATE 40001 SerializationFailure: could not serialize access due to read/write dependencies among transactions
.. повтор транзакции B целиком
[B] BEGIN ISOLATION LEVEL SERIALIZABLE
[B] SELECT count(*) FROM doctors WHERE on_call
-> [(1,)]
.. bob остаётся: на дежурстве 1
[B] COMMIT
[admin] SELECT count(*) FILTER (WHERE on_call) AS on_call_now FROM doctors
-> [(1,)]
На Serializable в pg_locks видны SIReadLock обеих сессий, а ошибка 40001 пришла на COMMIT, а не на UPDATE, это стоит учесть в коде повтора.
Durability после аварийной остановки
Сценарий долговечности фиксирует пять вставок отдельными транзакциями, держит в другой сессии шестую вставку в открытой транзакции и убивает сервер, не давая ему записать контрольную точку.
-- postgres 18.6 в Docker, прогнано на стенде 2026-11-05
-- Durability: подтверждённые транзакции перед аварийной остановкой сервера.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text NOT NULL);
SHOW synchronous_commit;
SHOW fsync;
-- пять отдельных транзакций, каждая получила от сервера COMMIT
INSERT INTO orders (note) VALUES ('подтверждён 1');
INSERT INTO orders (note) VALUES ('подтверждён 2');
INSERT INTO orders (note) VALUES ('подтверждён 3');
INSERT INTO orders (note) VALUES ('подтверждён 4');
INSERT INTO orders (note) VALUES ('подтверждён 5');
SELECT count(*) AS committed_rows FROM orders;
-- postgres 18.6 в Docker, прогнано на стенде 2026-11-05
-- Durability: что пережило аварийную остановку.
SELECT id, note FROM orders ORDER BY id;
SELECT count(*) FILTER (WHERE note LIKE 'подтверждён%') AS committed_survived,
count(*) FILTER (WHERE note = 'не зафиксирован') AS uncommitted_survived
FROM orders;
# вывод прогона на стенде 2026-11-05, PostgreSQL 18.6, фрагмент run_output.txt
committed_rows
----------------
5
(1 row)
--- сессий с открытой транзакцией и вставкой: 1
--- docker kill -s KILL acid-pg: сервер убит без контрольной точки
Container acid-pg Healthy
--- лог сервера после рестарта
LOG: database system was interrupted; last known up at 2026-11-05 18:24:10 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/1BA1C50
LOG: redo done at 0/1C7D290 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOG: database system is ready to accept connections
committed_survived | uncommitted_survived
--------------------+----------------------
5 | 0
(1 row)
Сервер сам проиграл журнал от 0/1BA1C50 до 0/1C7D290. Пять подтверждённых строк на месте, а вставка из открытой транзакции пропала.
Заключение
ACID это контракт из четырёх гарантий, и в PostgreSQL за каждой стоит свой механизм. Атомарность и изоляцию дают версии строк MVCC, согласованность ограничения схемы, долговечность журнал WAL. Главное для разработчика скрыто в букве I, потому что Read Committed допускает потерянное обновление и write skew, а уровни выше требуют повтора по SQLSTATE 40001. Greenplum построен как кластер экземпляров PostgreSQL и наследует эту транзакционную модель, с ним работают на курсе по Greenplum для инженеров данных.



