Транзакции и план выполнения запроса

Все примеры в лабораторной выполняются на учебной базе demo, развёрнутой в лабораторной работе 3. Подключитесь к базе и установите схему поиска

\c demo
SET search_path TO bookings;

Убедитесь, что таблицы схемы bookings доступны

\dt

Версия демонстрационной базы. Все запросы в работе написаны для редакции 2017 года (demo_small.sql), как и в лабораторных 5–6. Начиная с 1 сентября 2025 года ее обновили, ознакомиться с актуальной версией можно на сайте Postgres Pro.

Подготовка рабочей таблицы

Большинство экспериментов в работе изменяют данные. Чтобы случайно не испортить учебную схему, заведём временную копию aircrafts. Она существует только в рамках текущего сеанса и сама пропадёт при отключении

CREATE TEMP TABLE aircrafts_tmp AS
SELECT * FROM aircrafts;

ALTER TABLE aircrafts_tmp ADD PRIMARY KEY (aircraft_code);

Проверка

SELECT * FROM aircrafts_tmp ORDER BY aircraft_code;

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

Транзакция — это последовательность операций, которые должны быть выполнены либо все вместе, либо ни одна. Классический пример, который вам сто процентов знаком, — банковский перевод: списание со счёта отправителя и зачисление на счёт получателя должны произойти оба или ни одно. Если после списания сервер упадёт, не успев выполнить зачисление, деньги «исчезнут» — это недопустимо.

В нашей базе «Авиаперевозки» естественный пример транзакции — оформление бронирования: одновременно вставляются строки в bookings, tickets и ticket_flights. Если хотя бы одна вставка не пройдёт, нужно отменить и остальные — иначе в базе окажется бронирование без билетов или билеты без рейсов.

Транзакции — основной инструмент СУБД, помогающий сохранять данные в согласованном виде при сбоях и одновременной работе нескольких пользователей.

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

BEGIN, COMMIT, ROLLBACK

В PostgreSQL любая команда выполняется внутри транзакции. По умолчанию psql оборачивает каждую отдельную команду в свою маленькую транзакцию и сразу её фиксирует — это режим автокоммита. Поэтому до сих пор мы транзакции не упоминали — они работали под капотом автоматически.

Чтобы объединить несколько команд в одну транзакцию, её нужно открыть явно

BEGIN — начать транзакцию
COMMIT — зафиксировать изменения (сохранить навсегда)
ROLLBACK — отменить все изменения, сделанные с момента BEGIN

Простейший пример

BEGIN;

UPDATE aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = 'SU9';

UPDATE aircrafts_tmp
SET range = range - 100
WHERE aircraft_code = '773';

COMMIT;

Обе команды выполняются вместе. Если бы между ними произошёл сбой сервера до COMMIT — ни одно изменение не сохранилось бы. Это и есть атомарность.

В psql подсказку командной строки удобно использовать как индикатор состояния транзакции:

demo=> — транзакция не открыта (автокоммит)
demo=*> — внутри открытой транзакции
demo=!> — транзакция в состоянии ошибки

Откат изменений. Главное преимущество явной транзакции — возможность передумать. Можно использовать на всякий случай при ручной правке данных:

BEGIN;

DELETE FROM aircrafts_tmp WHERE model ~ '^Boe';

SELECT * FROM aircrafts_tmp;
-- проверяем, что осталось

ROLLBACK;
-- передумали — и все три модели Boeing вернулись на место

После ROLLBACK таблица в точности такая же, как была до BEGIN. Открываем транзакцию, выполняем потенциально опасную команду, проверяем результат SELECT-ом, и потом решаем COMMIT или ROLLBACK.

Полезная особенность PostgreSQL. В транзакцию можно поместить не только обычные команды (INSERT/UPDATE/DELETE), но и команды изменения схемы — CREATE TABLE, ALTER TABLE, DROP TABLE. ROLLBACK откатит и их тоже, как будто ничего не было. Это удобно при разработке миграций базы: можно безопасно проверять новую структуру и при ошибке вернуть всё назад одной командой. Не во всех СУБД так — в MySQL, например, DDL фиксируется автоматически и откатить его нельзя

Что происходит при ошибке

Если внутри транзакции PostgreSQL встречает ошибку, вся транзакция помечается как сбойная. Все последующие команды до COMMIT или ROLLBACK отвергаются с сообщением

ERROR:  current transaction is aborted, commands ignored until end of transaction block

Из такого состояния выйти можно только командой ROLLBACK

BEGIN;

INSERT INTO aircrafts_tmp VALUES ('SU9', 'Дубль', 5000);
-- ошибка: ключ 'SU9' уже существует

INSERT INTO aircrafts_tmp VALUES ('XX1', 'Test', 1000);
-- ERROR: current transaction is aborted, commands ignored ...

COMMIT;
-- COMMIT превратится в ROLLBACK — это сообщит сервер

То есть если внутри транзакции поймали ошибку — нужно либо откатить транзакцию (ROLLBACK) и начать заново, либо завершить её. Продолжить с того же места не получится.

Документация: BEGIN, COMMIT, ROLLBACK

Задание. Откройте транзакцию, выполните UPDATE aircrafts_tmp SET range = 0, выведите содержимое таблицы и откатите транзакцию. Убедитесь, что после ROLLBACK значения range остались прежними

Транзакция в приложении: оформление бронирования

Рассмотрим, как транзакция выглядит в реальной задаче. Создадим новое бронирование с двумя билетами на двух пассажиров, отправив их по маршруту Москва — Красноярск и обратно

BEGIN;

INSERT INTO bookings (book_ref, book_date, total_amount)
VALUES ('ABC123', bookings.now(), 0);

INSERT INTO tickets (ticket_no, book_ref, passenger_id, passenger_name)
VALUES ('9991234567890', 'ABC123', '1234 123456', 'IVAN PETROV');

INSERT INTO tickets (ticket_no, book_ref, passenger_id, passenger_name)
VALUES ('9991234567891', 'ABC123', '4321 654321', 'PETR IVANOV');

INSERT INTO ticket_flights (ticket_no, flight_id, fare_conditions, amount)
VALUES ('9991234567890', 5572, 'Business', 12500),
       ('9991234567890', 13881, 'Economy', 8500);

INSERT INTO ticket_flights (ticket_no, flight_id, fare_conditions, amount)
VALUES ('9991234567891', 5572, 'Business', 12500),
       ('9991234567891', 13881, 'Economy', 8500);

UPDATE bookings
SET total_amount = (
    SELECT sum(amount)
    FROM ticket_flights
    WHERE ticket_no IN (
        SELECT ticket_no FROM tickets WHERE book_ref = 'ABC123'
    )
)
WHERE book_ref = 'ABC123';

SELECT * FROM bookings WHERE book_ref = 'ABC123';
-- total_amount = 42000.00

COMMIT;

Все вставки и финальный пересчёт суммы выполнены как единое целое. Если бы посередине, скажем, не нашёлся рейс 5572 — соответствующий INSERT упал бы с ошибкой, мы выполнили бы ROLLBACK, и в базе не осталось бы ни одной строки этого бронирования. Не было бы ситуации, когда бронирование создано, а билеты — нет.

После эксперимента бронирование лучше удалить, чтобы оно не мешало повторному запуску

BEGIN;
DELETE FROM ticket_flights WHERE ticket_no IN
    (SELECT ticket_no FROM tickets WHERE book_ref = 'ABC123');
DELETE FROM tickets WHERE book_ref = 'ABC123';
DELETE FROM bookings WHERE book_ref = 'ABC123';
COMMIT;

Задание. Повторите сценарий бронирования с одной намеренной ошибкой: в одном из INSERT INTO ticket_flights укажите несуществующий flight_id (например, 99999999). Получите ошибку внешнего ключа, выполните ROLLBACK и убедитесь SELECT-ом, что бронирования ABC123 в базе нет

Транзакции в коде приложения

При коде вы редко будете писать BEGIN/COMMIT руками — это сделает за вас драйвер базы данных. Но идея останется такой же: сгруппировать несколько операций в одну транзакцию, зафиксировать при успехе, откатить при ошибке. Типовой паттерн на любом языке выглядит примерно так (псевдокод в стиле TypeScript с драйвером pg)

const client = await pool.connect();       // получаем соединение из пула

try {
  await client.query('BEGIN');             // открываем явную транзакцию
  await client.query('INSERT INTO bookings ...');
  await client.query('INSERT INTO tickets ...');
  await client.query('INSERT INTO ticket_flights ...');
  await client.query('COMMIT');            // всё прошло — фиксируем
} catch (err) {
  await client.query('ROLLBACK');          // что-то пошло не так — откатываем всё
  throw err;                               // пробрасываем ошибку дальше
} finally {
  client.release();                        // возвращаем соединение в пул
}

На уровне SQL это эквивалентно

BEGIN;
INSERT INTO bookings ...;
INSERT INTO tickets ...;
INSERT INTO ticket_flights ...;
COMMIT;                                    -- или ROLLBACK при ошибке

Похожий шаблон try / commit / catch / rollback с минимальными синтаксическими отличиями вы в принципе можете встретить в бэкенде на любом языке при базой данных (TypeScript, Python, Java, Go, C#). В современных библиотеках обычно есть более лаконичная форма — функция-обёртка, которая сама ставит COMMIT при штатном завершении и ROLLBACK при исключении

await db.transaction(async (tx) => {
  await tx.query('INSERT INTO bookings ...');
  await tx.query('INSERT INTO tickets ...');
  await tx.query('INSERT INTO ticket_flights ...');
});
// COMMIT здесь, если не было исключений; ROLLBACK, если было

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

Когда работают двое одновременно

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

Самое наглядное проявление — что произойдёт, если два сеанса одновременно меняют одну и ту же строку. Откройте второе окно терминала, подключитесь к той же базе. Назовем их Сеанс A и Сеанс B.

Нюанс. Временные таблицы (TEMP) видны только своему сеансу. Поэтому пока создайте обычную копию. После этого не забудьте ее удалить: DROP TABLE aircrafts_shared

CREATE TABLE aircrafts_shared AS
SELECT * FROM aircrafts;
ALTER TABLE aircrafts_shared ADD PRIMARY KEY (aircraft_code);

Сеанс A:

BEGIN;
UPDATE aircrafts_shared
SET range = range + 100
WHERE aircraft_code = 'SU9';
-- транзакция не закрыта

Сеанс B:

UPDATE aircrafts_shared
SET range = range + 200
WHERE aircraft_code = 'SU9';
-- команда «висит» — не возвращает приглашение

Сеанс B не получит ошибку и не выполнится — он будет ждать, пока транзакция A не завершится. PostgreSQL не даёт двум транзакциям одновременно изменить одну строку, иначе одно из изменений было бы потеряно. Завершим транзакцию A

Сеанс A:

COMMIT;

Полсе этого Сеанс B завершит свой UPDATE. Значение range для SU9 теперь увеличилось на 300 (исходное значение + 100 от A + 200 от B). Если бы вместо COMMIT сеанс A сделал ROLLBACK, сеанс B применил бы +200 к исходному значению.

Если транзакция надолго затянулась, другие пользователи будут её ждать. Поэтому транзакции лучше держать короткими.

PostgreSQL поддерживает четыре уровня изоляции транзакций — они задают, что одна транзакция знает от других. По умолчанию используется READ COMMITTED (видны только зафиксированные изменения других транзакций). Для подавляющей части логики этого достаточно. Однако, иногда могут возникать интерсные ситуации.

Парадокс READ COMMITTED. Сеанс A в открытой транзакции меняет дальность Cessna (CN1) с 1200 на 2100, а Bombardier CRJ-200 (CR2) — с 2700 на 1900. Параллельно сеанс B пытается выполнить DELETE FROM aircrafts_shared WHERE range < 2000. Команда зависает на блокировке строки CN1 и ждёт. После COMMIT в сеансе A команда DELETE перечитывает только ту строку, на которой висела (CN1), видит у неё новое значение range = 2100, понимает, что условие уже не выполняется, и сообщает DELETE 0. При этом в таблице есть строка CR2 с range = 1900, которая под условие подходит — но повторный поиск по всей таблице не выполняется, и она остаётся незамеченной. Это допустимый компромисс между производительностью и строгостью изоляции, на более высоких уровнях (REPEATABLE READ, SERIALIZABLE) такого эффекта нет.

Задание. Воспроизведите эксперимент из этого раздела в двух сеансах. Зафиксируйте, в какой момент сеанс B «висит». Завершите транзакцию A командой COMMIT и подтвердите, что сеанс B немедленно разблокировался.

Как СУБД выполняет запросы (EXPLAIN)

EXPLAIN мы чуть-чут затрагивали, когда смотрели, попал ли запрос в индекс. . EXPLAIN показывает план выполнения запроса — последовательность шагов, которыми СУБД будет искать данные.

Сам запрос при этом не выполняется

EXPLAIN
SELECT * FROM bookings WHERE book_ref = 'ABC123';

Пример вывода

Index Scan using bookings_pkey on bookings  (cost=0.42..8.44 rows=1 width=21)
  Index Cond: ((book_ref)::text = 'ABC123'::text)

Что здесь интересного:

Index Scan using bookings_pkey — стратегия поиска. Здесь СУБД использует индекс первичного ключа: ей не нужно перебирать всю таблицу, она сразу попадает в нужную строку
cost=0.42..8.44 — оценочная стоимость в условных единицах планировщика. Первое число — затраты до выдачи первой строки, второе — до выдачи всех
rows=1 — сколько строк планировщик ожидает получить
width=21 — средняя ширина строки в байтах

Если индекса нет, план будет другим

EXPLAIN
SELECT * FROM tickets WHERE passenger_name = 'IVAN PETROV';

Seq Scan on tickets  (cost=0.00..10650.16 rows=1 width=104)
  Filter: ((passenger_name)::text = 'IVAN PETROV'::text)

Seq Scan — последовательное сканирование. Оценка себстоимости в этом случае существенно больше, потому что потребуется перебор всей таблицы tickets.

EXPLAIN ANALYZE. Если добавить ключевое слово ANALYZE, запрос будет выполнен по-настоящему, и в плане появятся фактические числа

EXPLAIN ANALYZE
SELECT * FROM bookings WHERE book_ref = 'ABC123';

Index Scan using bookings_pkey on bookings  (cost=0.42..8.44 rows=1 width=21)
                                             (actual time=0.025..0.027 rows=1 loops=1)
  Index Cond: ((book_ref)::text = 'ABC123'::text)
Planning Time: 0.184 ms
Execution Time: 0.052 ms

Появились:

actual time=0.025..0.027 — реальное время в миллисекундах
rows=1 (после actual) — фактическое количество строк
Planning Time / Execution Time — суммарное время планирования и выполнения

Сравнение оценочных и фактических чисел помогает понять, правильно ли планировщик представляет данные. Если оценочное rows сильно отличается от фактического — это сигнал обновить статистику командой ANALYZE имя_таблицы.

EXPLAIN ANALYZE безопасен для SELECT, но не для UPDATE/DELETE/INSERT — нет. Чтобы посмотреть план модифицирующей команды без её выполнения, оберните вызов в транзакцию

BEGIN;
EXPLAIN ANALYZE
UPDATE aircrafts_tmp SET range = range + 1;
ROLLBACK;

Основные типы узлов, которые часто встречаются в простых запросах:

Seq Scan — последовательный перебор всей таблицы (когда нет подходящего индекса или таблица маленькая)
Index Scan — поиск по индексу с переходом к таблице за каждой записью (когда возвращается мало строк)
Bitmap Index Scan + Bitmap Heap Scan — гибрид: сначала по индексу собираются «адреса» строк, потом таблица читается одним проходом (когда строк средне-много)
Nested Loop / Hash Join / Merge Join — три способа соединения таблиц (для запросов с JOIN)
Sort — сортировка (для ORDER BY)
Aggregate — вычисление count, sum, avg и т. п.

Вообще вариантов очень много. Но уже на текущий момент мы сможем так отличать идёт ли запрос по индексу или последовательно перебирает таблицу, так вы уже сможете справиться с большинством проблем анализа долгого выполнения запросов.

Документация: EXPLAIN, Использование EXPLAIN

Задание. На таблице tickets выполните два варианта запроса с EXPLAIN ANALYZE:

SELECT * FROM tickets WHERE ticket_no = '0005432000987' (подставьте реально существующий номер — например, через SELECT ticket_no FROM tickets LIMIT 1)
SELECT * FROM tickets WHERE passenger_name = 'IVAN PETROV' (имя также выбираете сами)

Сравните результаты выдачи и результаты прогнозируемых и фактически полученных значений.

Дополнительно можно потренироваться (по желанию):

Е. П. Моргунов. PostgreSQL. Основы языка SQL. — СПб.: БХВ-Петербург, 2018. Глава 9 «Транзакции» — подробный разбор четырёх уровней изоляции и явных блокировок с практическими сценариями в двух сеансах
pgexercises.com — раздел Updates для тренировки практических навыков работы с DML внутри транзакций
PostgreSQL Tutorial — Transactions — короткое практическое введение на английском с примерами

DML и индексы Итоговое задание