Продвинутые транзакции в PostgreSQL: SAVEPOINT и обработка ошибок

Базовый BEGIN / COMMIT / ROLLBACK знают все. Но в реальных системах ошибки возникают в середине большой транзакции — и откатывать всё целиком бывает слишком дорого. Здесь на помощь приходят SAVEPOINT.

Синтаксис SAVEPOINT

BEGIN;

-- шаг 1
INSERT INTO orders (user_id, amount) VALUES (42, 1500);

SAVEPOINT after_order;

-- шаг 2 — потенциально опасная операция
UPDATE inventory SET qty = qty - 1 WHERE product_id = 7;

-- если шаг 2 упал — откат только к точке сохранения
ROLLBACK TO SAVEPOINT after_order;

-- продолжаем без шага 2
INSERT INTO order_issues (order_id, reason) VALUES (LASTVAL(), 'out_of_stock');

COMMIT;

Три ключевые команды:

После ROLLBACK TO SAVEPOINT транзакция не завершается — можно продолжать работу или создать новую точку.

Практический кейс: батч-импорт с частичным откатом

Представьте, что нужно импортировать 1000 строк. Если одна строка невалидна — хочется пропустить её, а не откатывать всё.

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM staging_orders LOOP
        SAVEPOINT row_save;
        BEGIN
            INSERT INTO orders (user_id, amount, product_id)
            VALUES (r.user_id, r.amount, r.product_id);
            RELEASE SAVEPOINT row_save;
        EXCEPTION WHEN OTHERS THEN
            ROLLBACK TO SAVEPOINT row_save;
            INSERT INTO import_errors (row_id, error_msg)
            VALUES (r.id, SQLERRM);
        END;
    END LOOP;
END;
$$;

Здесь каждая строка изолирована своим SAVEPOINT. При ошибке (WHEN OTHERS) откатываем только её, фиксируем проблему в import_errors и продолжаем импорт дальше.

Важно: EXCEPTION в PL/pgSQL неявно создаёт SAVEPOINT под капотом. Но явные SAVEPOINT дают контроль из клиентского кода (Python, Go и др.) — не только из процедур.

Уровни изоляции транзакций — напоминание

Поведение SAVEPOINT одинаково на всех уровнях, но сами транзакции ведут себя по-разному:

PostgreSQL по умолчанию — READ COMMITTED. Для финансовых операций рекомендуется REPEATABLE READ или SERIALIZABLE.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Обнаружение и повтор deadlock

Deadlock возникает, когда две транзакции ждут друг друга. PostgreSQL обнаруживает это автоматически и завершает одну из них с ошибкой ERROR: deadlock detected.

Правильная реакция на стороне приложения — повторить транзакцию:

import psycopg2
import time

MAX_RETRIES = 3

def transfer_funds(conn, from_id, to_id, amount):
    for attempt in range(MAX_RETRIES):
        try:
            with conn.cursor() as cur:
                cur.execute("BEGIN")
                cur.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
                cur.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )
                cur.execute("COMMIT")
                return  # успех
        except psycopg2.errors.DeadlockDetected:
            conn.rollback()
            if attempt < MAX_RETRIES - 1:
                time.sleep(0.1 * (attempt + 1))  # экспоненциальная задержка
            else:
                raise

Как избежать дедлоков превентивно: всегда обновляйте строки в одном и том же порядке. Если транзакция A блокирует строку 1, потом 2 — транзакция B должна делать то же самое, а не наоборот.

-- Правильно: сортируем по ID перед UPDATE
UPDATE accounts
SET balance = balance - amount
WHERE id = ANY(ARRAY[1, 5, 12]::int[])
ORDER BY id;  -- фиксированный порядок = нет дедлоков

Двухфазный коммит (2PC) — кратко

Если транзакция охватывает несколько баз данных (distributed transaction), используется двухфазный коммит:

-- Фаза 1: подготовка
PREPARE TRANSACTION 'order_tx_42';

-- Фаза 2: фиксация (из координатора)
COMMIT PREPARED 'order_tx_42';

-- Или откат
ROLLBACK PREPARED 'order_tx_42';

Незавершённые 2PC-транзакции видны в pg_prepared_xacts. Их нужно мониторить — зависшая подготовленная транзакция удерживает блокировки.

SELECT * FROM pg_prepared_xacts;

Итого

• SAVEPOINT — точка частичного отката внутри транзакции
• Используйте их в батч-операциях, чтобы не откатывать всё при одной ошибке
• Deadlock — нормальная ситуация, приложение должно повторять транзакцию
• Для предотвращения дедлоков: фиксированный порядок блокировок
• 2PC нужен только при распределённых транзакциях, в обычных системах — избыточен

Хотите научиться писать сложные транзакции и решать задачи на SQL с автопроверкой? Попробуйте тренажёр SQLlab.