Analyse des erreurs typiques lors du travail avec des transactions imbriquées

Coder sur Postgres, c'est parfois une vraie aventure : ça se transforme vite en chasse au bug façon « Trouve ton erreur ». Ici, on va causer des erreurs classiques et des pièges qui t'attendent avec les transactions imbriquées. C'est parti !

Mauvaise utilisation des commandes transactionnelles dans les fonctions et procédures

Erreur : essayer d'utiliser COMMIT, ROLLBACK ou SAVEPOINT dans une FUNCTION.

Pourquoi : Dans PostgreSQL, les fonctions (CREATE FUNCTION ... LANGUAGE plpgsql) s'exécutent toujours dans une seule transaction externe, et toute commande transactionnelle à l'intérieur d'une fonction est interdite. Si tu tentes, tu te prends une erreur de syntaxe.

Exemple d'erreur :

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- Erreur : commandes transactionnelles interdites
END;
$$ LANGUAGE plpgsql;

La bonne façon :

Pour les opérations atomiques qui doivent être « tout ou rien », utilise des fonctions sans commandes transactionnelles explicites. Si tu veux valider les changements étape par étape — utilise des procédures.

Erreur : essayer d'utiliser ROLLBACK TO SAVEPOINT dans une procédure en PL/pgSQL.

Pourquoi : Dans PostgreSQL 17, seules les commandes COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT sont autorisées dans les procédures (CREATE PROCEDURE ... LANGUAGE plpgsql). Mais ROLLBACK TO SAVEPOINT en PL/pgSQL, c'est interdit ! Toute tentative finit en erreur de syntaxe.

Exemple d'erreur :

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- Erreur ! Interdit d'utiliser
END;
$$;

La bonne façon :

Pour un “rollback partiel”, utilise les blocs BEGIN ... EXCEPTION ... END — ils créent automatiquement un savepoint ; si une erreur se produit dans le bloc, tout est rollback au début du bloc.

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- opérations qui peuvent planter
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Rollback dans le bloc BEGIN ... EXCEPTION ... END';
    END;
END;
$$;

Appels imbriqués de procédures : limitations et erreurs classiques

Erreur : appeler une procédure avec un COMMIT/ROLLBACK explicite alors qu'une transaction client est déjà ouverte.

Pourquoi : Les procédures avec contrôle transactionnel ne marchent bien qu'en mode autocommit (une procédure = une transaction), sinon si tu fais COMMIT ou ROLLBACK dans la procédure, tu te prends une erreur : la transaction est déjà ouverte côté client.

Exemple :

# En Python avec psycopg2, autocommit=False par défaut
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- Erreur si COMMIT dans my_proc

La bonne façon :

• Passe la connexion en mode autocommit avant d'appeler des procédures.
• N'appelle pas de procédures via des fonctions ou SELECT.

Erreur : les procédures avec contrôle transactionnel (COMMIT, ROLLBACK) ne fonctionnent pas si elles sont appelées autrement que par CALL (genre via SELECT).

Pourquoi : Seul un appel via CALL (ou dans un bloc DO anonyme) permet de gérer les transactions. Depuis une fonction — c'est niet.

Problèmes de verrous et deadlocks

Les verrous, c'est comme des invités relous : d'abord ils gênent, puis c'est le chaos. Un deadlock, c'est quand deux transactions s'attendent mutuellement à l'infini. Exemple typique :

1. La transaction A verrouille une ligne dans la table orders et veut mettre à jour une ligne dans products.
2. La transaction B verrouille une ligne dans products et veut mettre à jour une ligne dans orders.

Résultat : aucune transaction ne peut avancer. C'est comme deux voitures qui essaient de passer en même temps dans un virage étroit — bouchon assuré.

Exemple :

-- Transaction A
BEGIN;
UPDATE orders SET status = 'En traitement' WHERE id = 1;

-- Transaction B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- Maintenant, la transaction A veut mettre à jour la même ligne dans `products`,
-- et la transaction B veut modifier la ligne dans `orders`.
-- Deadlock !

Comment éviter ?

1. Mets toujours à jour les données dans le même ordre. Par exemple, d'abord orders, puis products.
2. Évite les transactions trop longues.
3. Utilise LOCK intelligemment, avec le niveau de verrou minimal.

Mauvaise utilisation du dynamic SQL (EXECUTE)

Le dynamic SQL, si tu l'utilises à l'arrache, c'est la porte ouverte aux emmerdes. L'erreur la plus courante — l'injection SQL. Exemple :

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

Si user_input contient un truc du genre 1; DROP TABLE orders;, tu peux dire adieu à la table orders.

Comment éviter ? Utilise les requêtes préparées :

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

Comme ça, ton appli sera protégée contre les injections SQL.

Rollback de la transaction après une mauvaise gestion des erreurs

Si tu gères mal les erreurs, la transaction peut rester dans un état foireux. Exemple :

BEGIN;

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

BEGIN;
-- Une opération qui plante
INSERT INTO non_existing_table VALUES (1);
-- Erreur, mais la transaction n'est pas terminée

COMMIT; -- Erreur : la transaction courante est interrompue

À cause de l'erreur, tout le code est bloqué.

Comment éviter ? Utilise les blocs EXCEPTION pour rollback proprement :

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'En attente');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Une erreur est survenue, la transaction va être rollback.';
END;

Comment éviter les erreurs : conseils et astuces

• Quand tu codes une procédure complexe, commence toujours par du pseudo-code. Note chaque étape et les points où ça peut planter.
• Utilise SAVEPOINT pour rollback isolé. Mais pense à les libérer après usage.
• Évite les transactions longues — plus c'est long, plus tu risques des verrous.
• Pour les appels imbriqués de procédures, assure-toi que les contextes transactionnels externe et interne sont bien synchronisés.
• Teste toujours la perf de tes procédures avec EXPLAIN ANALYZE.
• Loggue les erreurs dans des tables ou des fichiers texte — ça aide grave pour le debug.

Exemples d'erreurs et leur correction

Exemple 1 : Erreur lors de l'appel d'une procédure imbriquée

Code avec erreur :

BEGIN;

CALL process_order(5);

-- À l'intérieur de process_order, il y a eu un ROLLBACK
-- Toute la transaction devient invalide
COMMIT; -- Erreur

Code corrigé :

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- Rollback seulement en cas d'erreur
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

Exemple 2 : Problème de deadlock

Code avec erreur :

-- Transaction A
BEGIN;
UPDATE orders SET status = 'En traitement' WHERE id = 1;
-- Attend `products`

-- Transaction B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- Attend `orders`

Correction :

-- Les deux requêtes sont faites dans le même ordre :
-- D'abord `products`, puis `orders`.
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'En traitement' WHERE id = 1;
COMMIT;

Ces erreurs montrent pourquoi bosser avec les transactions demande de l'attention et de la pratique. Mais plus tu pratiques, moins tu risques de te prendre un ROLLBACK dans la vraie vie (et dans ta carrière).