Анализ типичных ошибок при работе с вложенными транзакциями

Программирование на Postgres — то ещё приключение: порой оно превращается в квест под названием «Найди свою ошибку». В этом блоке мы поговорим о типичных ошибках и подводных камнях, которые могут встретиться при работе с вложенными транзакциями. Погнали!

Неправильное использование транзакционных команд внутри функций и процедур

Ошибка: попытка использовать COMMIT, ROLLBACK или SAVEPOINT внутри FUNCTION.

Почему: В PostgreSQL функции (CREATE FUNCTION ... LANGUAGE plpgsql) всегда выполняются в рамках одной внешней транзакции, и любые транзакционные команды внутри функции запрещены. Попытка использовать их приведёт к ошибке синтаксиса.

Пример ошибки:

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- Ошибка: транзакционные команды запрещены
END;
$$ LANGUAGE plpgsql;

Как правильно:

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

Ошибка: попытка использовать ROLLBACK TO SAVEPOINT в процедуре на языке PL/pgSQL.

Почему: в PostgreSQL 17 разрешены только команды COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT внутри процедур (CREATE PROCEDURE ... LANGUAGE plpgsql). Но ROLLBACK TO SAVEPOINT в PL/pgSQL пользоваться нельзя! Любая такая попытка завершится синтаксической ошибкой.

Пример ошибки:

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- Ошибка! Нельзя использовать
END;
$$;

Как правильно:

Для “частичного отката” используйте блоки BEGIN ... EXCEPTION ... END — они автоматически создают сейвпоинт; при ошибке внутри блока все изменения откатятся к его началу.

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- операции, которые могут вызвать ошибку
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Откат внутри блока BEGIN ... EXCEPTION ... END';
    END;
END;
$$;

Вложенные вызовы процедур: ограничения и типовые ошибки

Ошибка: вызов процедуры с явным COMMIT/ROLLBACK внутри уже открытой клиентской транзакции.

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

Пример:

# В Python с psycopg2 по умолчанию autocommit=False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- Ошибка при попытке COMMIT внутри my_proc

Как правильно:

• Перед вызовом процедур переключите соединение в режим autocommit.
• Не вызывайте процедуры через функции или SELECT.

Ошибка: вызов процедур с транзакционным контролем (COMMIT, ROLLBACK) не работает, если они вызываются НЕ командой CALL (например, через SELECT).

Почему: Только вызов через CALL (или в анонимном DO-блоке) позволяет управлять транзакциями. Вызываться из функции — нельзя.

Проблемы с блокировками и взаимоблокировками (Deadlock)

Блокировки — это как непрошенные гости: сначала мешают, потом вызывают хаос. Deadlock возникает, когда транзакции ждут друг друга до бесконечности. Вот типичный пример:

1. Транзакция A блокирует строку в таблице orders и пытается обновить строку в таблице products.
2. Транзакция B блокирует строку в таблице products и пытается обновить строку в таблице orders.

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

Пример:

-- Транзакция A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;

-- Транзакция B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- Теперь транзакция A пытается обновить ту же строку в `products`,
-- а транзакция B пытается изменить строку в `orders`.
-- Deadlock!

Как избежать?

1. Всегда обновляйте данные в одном и том же порядке. Например, сначала orders, потом products.
2. Избегайте слишком долгих транзакций.
3. Используйте LOCK с умом, указывая минимальный уровень блокировки.

Неправильное использование динамического SQL (EXECUTE)

Динамический SQL, если использовать его неосмотрительно, может стать настоящим источником головной боли. Самая распространённая ошибка — SQL-инъекция. Например:

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

Если user_input содержит что-то вроде 1; DROP TABLE orders;, то можно попрощаться с таблицей orders.

Как избежать? Используйте подготовленные запросы:

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

Такой подход защитит ваше приложение от SQL-инъекций.

Откат транзакции после некорректной обработки ошибок

Если ошибки не обрабатываются должным образом, транзакция может остаться в невалидном состоянии. Например:

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, 'Pending');

BEGIN;
-- Какая-то операция, которая вызывает ошибку
INSERT INTO non_existing_table VALUES (1);
-- Ошибка, но транзакция не была завершена

COMMIT; -- Ошибка: текущая транзакция прервана

Из-за возникшей ошибки весь код застревает.

Как избежать? Используйте блоки EXCEPTION для корректного отката:

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'Pending');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Ошибка произошла, транзакция откатится.';
END;

Как избежать ошибок: советы и рекомендации

• При написании сложной процедуры всегда начинайте с псевдокода. Пропишите все шаги и точки возможных ошибок.
• Используйте SAVEPOINT для изолированного отката транзакций. Но не забывайте освобождать их после использования.
• Старайтесь избегать долгих транзакций — чем дольше транзакция, тем выше шанс блокировок.
• Для вложенных вызовов процедур убедитесь, что внешний и внутренний контексты транзакции правильно синхронизированы.
• Всегда проверяйте производительность своих процедур с помощью EXPLAIN ANALYZE.
• Логируйте ошибки в таблицы или текстовые файлы — это облегчит отладку.

Примеры ошибок и их исправление

Пример 1: Ошибка при вызове вложенной процедуры

Код с ошибкой:

BEGIN;

CALL process_order(5);

-- Внутри process_order произошёл ROLLBACK
-- Вся транзакция становится недействительной
COMMIT; -- Ошибка

Исправленный код:

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- Откат только при ошибке
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

Пример 2: Проблема Deadlock

Код с ошибкой:

-- Транзакция A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;
-- Ждёт `products`

-- Транзакция B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- Ждёт `orders`

Исправление:

-- Оба запроса выполняются в одном и том же порядке:
-- Сначала `products`, потом `orders`.
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'Processing' WHERE id = 1;
COMMIT;

Эти ошибки указывают, почему работа с транзакциями требует внимательности и опыта. Но, как известно, чем больше практика, тем меньше шансов получить ROLLBACK в реальной жизни (и карьере).