Аналіз типових помилок при роботі з вкладеними транзакціями

Програмування на 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 всередині вже відкритої клієнтської транзакції.

Чому: процедури з транзакційним контролем коректно працюють лише в режимі autocommit (одна процедура — одна транзакція), інакше при спробі всередині процедури використати 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 у реальному житті (і кар'єрі).