Transactions dans les procédures

6.1 Pourquoi les transactions ?

Une transaction garantit que toutes les operations reussissent ou aucune. C'est le principe du "tout ou rien".

Exemple : un virement bancaire. On debite le compte A et on credite le compte B. Si le debit reussit mais le credit echoue (panne, erreur), le systeme est dans un etat incoherent : l'argent a disparu. La transaction empeche cela.

6.2 Syntaxe

sql

START TRANSACTION;
-- operations
COMMIT;     -- tout valider
-- ou
ROLLBACK;   -- tout annuler

6.3 Exemple : procedure de virement bancaire

Besoin : transferer un montant du compte A vers le compte B. Si une erreur survient, annuler toute l'operation.

sql

DELIMITER //

CREATE PROCEDURE virement(
    IN p_id_source INT,
    IN p_id_destination INT,
    IN p_montant DECIMAL(12,2),
    OUT p_resultat VARCHAR(100)
)
BEGIN
    DECLARE v_solde_source DECIMAL(12,2);

    -- Handler : en cas d'erreur SQL, annuler la transaction
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_resultat = 'Erreur : transaction annulee';
    END;

    START TRANSACTION;

    -- Verifier le solde du compte source
    SELECT solde INTO v_solde_source
    FROM compte
    WHERE id_compte = p_id_source
    FOR UPDATE;   -- verrouiller la ligne pour eviter les acces concurrents

    IF v_solde_source < p_montant THEN
        ROLLBACK;
        SET p_resultat = 'Erreur : solde insuffisant';
    ELSE
        -- Debiter le compte source
        UPDATE compte
        SET solde = solde - p_montant
        WHERE id_compte = p_id_source;

        -- Crediter le compte destination
        UPDATE compte
        SET solde = solde + p_montant
        WHERE id_compte = p_id_destination;

        COMMIT;
        SET p_resultat = 'Virement effectue avec succes';
    END IF;
END //

DELIMITER ;

Explication :

DECLARE EXIT HANDLER FOR SQLEXCEPTION : si une erreur SQL survient (table inexistante, violation de contrainte, etc.), le handler s'execute. EXIT signifie que la procedure s'arrete apres le handler. Le handler fait un ROLLBACK pour annuler toutes les operations.
START TRANSACTION : debut de la transaction.
FOR UPDATE : verrouille la ligne lue pour empecher un autre utilisateur de la modifier en meme temps (acces concurrent).
ROLLBACK en cas de solde insuffisant : annule la transaction (meme si aucune modification n'a encore ete faite, c'est une bonne pratique).
COMMIT : valide les deux UPDATE (debit et credit). Les deux sont appliques ensemble.

Test :

sql

-- Creer des comptes
INSERT INTO compte (id_client, solde) VALUES (1, 1000.00);
INSERT INTO compte (id_client, solde) VALUES (2, 500.00);

-- Virement reussi
CALL virement(1, 2, 200.00, @res);
SELECT @res;
-- 'Virement effectue avec succes'

SELECT * FROM compte;
-- Compte 1 : 800.00, Compte 2 : 700.00

-- Virement avec solde insuffisant
CALL virement(1, 2, 5000.00, @res);
SELECT @res;
-- 'Erreur : solde insuffisant'

SELECT * FROM compte;
-- Compte 1 : 800.00 (inchange), Compte 2 : 700.00 (inchange)

6.1 Pourquoi les transactions ?

6.2 Syntaxe

6.3 Exemple : procedure de virement bancaire

Chapitre terminé !

6.1 Pourquoi les transactions ?

Chapitre terminé !