Tutoriel : Les transactions avec MySQL et PDO

Table des matières

Les transactions avec MySQL et PDO
Explications
Les moteurs de stockage de MySQL
La théorie : MySQL
La pratique : utilisation de PDO

Les transactions avec MySQL et PDO

Lorsqu'on débute avec MySQL, on ne se soucie pas trop de savoir si toutes les requêtes que l'on va faire vont parfaitement s'exécuter. On peut bien sûr mettre un <?php or exit(mysql_error()); ?> à la fin de chaque requête (ou un système plus élaboré) mais cela ne suffit pas toujours.

En effet il faut que le serveur MySQL renvoie une erreur (donc s'il tombe en panne à ce moment là c'est marron) ou que le serveur PHP puisse aussi traiter cette erreur (et comme son collègue il peut aussi avoir des soucis). Il faut donc s'assurer que pour certaines requêtes (INSERT et UPDATE entre autres) tout s'est déroulé comme on le voulait avant d'appliquer les changements dans la base de données.

Le but des transaction est justement de s'assurer que tous ces changements ont été correctement effectués avant de les appliquer définitivement. Et en cas de problèmes les transactions permettent de revenir en arrière.

Vous ne voyez toujours pas l'intérêt des transactions ? Au contraire ça vous intéresse et vous voulez en savoir plus ? Ça tombe bien c'est le but de ce tutoriel.

Explications

Des exemples pour mieux comprendre

Ce premier exemple est là pour vous montrer que dans certains cas il est indispensable que toutes les requêtes soient effectuées.

Les transactions bancaires

On va prendre un honnête citoyen (Monsieur Michou) qui veut acheter un serveur qui vaut 2000€ qu'il décide de payer par CB. il donne alors son numéro et toutes les informations qui vont avec afin que la banque puisse effectuer le transfert d'argent entre le compte de monsieur Michou et le compte du vendeur de serveur. Cette banque qui après s'être assurée que tout était en règle va alors virer l'argent depuis le compte de l'acheteur vers celui du vendeur.

Tout d'abord la banque prélève 2000€ sur le compte de M. Michou.

UPDATE compte SET argent = argent - 2000 WHERE compte_proprio = michou

Ensuite elle envoie cet argent sur le compte du vendeur de serveurs.

UPDATE compte SET argent = argent + 2000 WHERE compte_proprio = vendeur

Le transfert s'étant bien effectué, monsieur Michou reçoit alors son serveur. Là tout s'est bien passé car les deux requêtes se sont convenablement déroulées, mais il peut aussi y avoir une erreur lors du transfert. reprenons l'exemple précédent :

Tout d'abord la banque prélève 2000€ sur le compte de M. Michou.

UPDATE compte SET argent = argent - 2000 WHERE compte_proprio = michou

Ensuite elle envoit cet argent sur le compte du vendeur de serveurs.

//plantage du serveur MySQL

Et là c'est le drame (pour monsieur Michou), la banque lui a retirée 2000€ sur son compte, mais cet argent n'est jamais arrivé sur le compte du vendeur qui donc n'enverra jamais le serveur vu qu'il n'a pas reçu d'argent. Il faudrait donc pouvoir s'assurer que ces deux requêtes se soient correctement effectuées avant de les appliquer. C'est l'objet de ce tutoriel.

---

Les moteurs de stockage de MySQL

La particularité de MySQL est de proposer plusieurs moteurs de stockage dans une même base de données. Sans entrer dans les détails je vais vous faire une brève présentation de quelques uns de ces moteurs actuels en détaillant un peu leurs particularités, tout cela afin de comprendre quand et pourquoi utiliser tel ou tel moteur de stockage. Car ces moteurs ont des spécificités bien particulières qui ont même entrainées des idées reçues pendant un temps.

MyIsam : le plus répandu

Des avantages ...

MyIsam est le moteur par défaut de MySQL, si jusqu'à présent la notion de moteur de stockage vous était inconnue, c'est surement ce moteur là que vous utilisez depuis vos début avec MySQL. Ce moteur est très populaire car il est très simple d'utilisation (pour un débutant) et offre de très bonnes performances sur des tables très fréquemment ouvertes en lecture-écriture.

Son autre point fort est de proposer un index FULL-TEXT, qui permet de faire des recherche assez précises (en comparaison avec LIKE) sur des colonnes de texte et qui permet donc à chacun d'avoir un petit moteur de recherche, notamment grâce à un tri par pertinence.

...et des inconvénients

Pour rester sur l'index FULL-TEXT, celui souffre néanmoins sur les grosses tables, de plus sa configuration (taille des mots notamment) n'est accessible que sur un serveur dédié.

Mais les 2 plus gros défauts du moteur MyIsam est qu'il ne supporte ni les clefs étrangères, ni les transactions (qui font l'objet de ce tutoriel). C'est d'ailleurs la forte présence de ce moteur (de plus proposé par défaut) qui fait croire à certains que MySQL ne gère pas les transactions. C'est faux, mais il faut pour cela utiliser un moteur de stockage qui les supporte : InnoDB.

InnoDB : un moteur pour des bases robustes

Contrairement à MyIsam, InnoDB est un moteur qu'on utilise pour ses fonctionnalités qui en font le moteur le plus utilisé dans les secteurs sensibles, c'est-à-dire nécessitant une cohérence et une grande intégrité des données (finances, jeux en ligne, architecture complexe très sollicitée, etc ...).

Ses deux grands points forts sont sa gestion des clefs étrangères et son support des transactions (ah je sens un regain d'intérêt petit lecteur ^^ ). Ces mécanismes transactionnels présentent une grande compatibilité aux critères ACID.

Concernant ses défauts : outre le fait d'avoir des tables plus volumineuses (en moyenne 25% plus grosses) et de ne pas proposer d'index FULL-TEXT, InnoDB est légèrement plus lent dans les opérations, mais cela est dû aux tests d'intégrité (les clef étrangères et les transactions) qui permettent de garder une base cohérente.

C'est donc ce moteur de stockage que nous allons utiliser pour la suite de ce tutoriel.

Memory (Heap) : tout dans la Ram

Comme son nom l'indique, ce moteur de stockage stocke les données de la table en mémoire, la structure quand à elle est stockée dans un fichier. Son principal intérêt est sa rapidité d'accès, très utile pour une table très fortement sollicitée. Le problème c'est qu'en cas d'arrêt du serveur, toutes les données stockées sont supprimées (puisque stockée dans la Ram qui se vide lors de l'arrêt du courant).

Il faut donc utiliser ce moteur de stockage pour des données qui ne sont pas indispensables au fonctionnement d'un site tel qu'un compteur de visiteur ou un système de chat (sauf si vous voulez garder une trace des discussions).

Des moteurs de stockage pour MySQL, il y en a pleins d'autres mais les principaux sont là, passons sans plus attendre à la pratique.

---

La théorie : MySQL

Comme cela a été dit précédemment, il faut utiliser le moteur de stockage InnoDB, pour cela il faut le spécifier lors de la création de la table sur laquelle on va travailler :

CREATE TABLE compte
(
    -- liste des champs
)
ENGINE = InnoDB;

Cette table toute simple que l'on va utiliser, en reprenant le premier exemple, comporte 2 champs :

• 
  

  le nom du propriétaire du compte

  
  


• 
  

  le montant sur le compte

  
  

CREATE TABLE compte
(
    nom VARCHAR (30) NOT NULL,
    montant MEDIUMINT UNSIGNED NOT NULL
)
ENGINE = InnoDB;

On va bien sûr y insérer nos 2 utilisateurs, le vendeur et M. Michou (ici appelé acheteur) en leur donnant à chacun un compte bien remplis.

INSERT INTO compte
(nom,montant)
VALUES
('vendeur', '10000'),
('acheteur', '25000');

Les transactions : COMMIT et ROLLBACK

Le principe des transactions est très simple à mettre en oeuvre : on lance la transaction avec START TRANSACTION; puis on effectue les opérations sur les tables, et ensuite on a 2 choix :

• 
  

  On valide les opérations précédentes avec COMMIT.

  
  


• 
  

  On annule tous les changements avec ROLLBACK.

  
  

Or par défaut, le moteur InnoDB est réglé pour valider automatiquement toutes les transactions car il considère chaque requête individuelle comme une transaction : on annule ce comportement grâce à la commande SET autocommit = 0; , on va voir tout de suite ce que ça donne en pratique :

-- on désactive l'autocommit
SET autocommit = 0;
-- on lance la transaction
START TRANSACTION;
-- on effectue cette simple requête sur notre table compte
UPDATE compte SET montant = montant + 20000 WHERE nom = 'vendeur';

Vous pouvez effectuer cette suite de requêtes autant de fois que vous voulez, le montant du vendeur ne changera pas d'un pouce, la transaction n'ayant pas été validée. Pour cela il faut faire appel à la commande COMMIT qui va valider les requêtes effectuées lors de la transaction :

-- on désactive l'autocommit
SET autocommit = 0;
-- on lance la transaction
START TRANSACTION;
-- on effectue cette simple requête sur notre table compte
UPDATE compte SET montant = montant + 20000 WHERE nom = 'vendeur';
-- on valide la transaction
COMMIT;

Et là comme par magie la requête est prise en compte.

Il y a la commande COMMIT pour valider, mais il y a aussi la commande ROLLBACK pour annuler, cela permet de revenir à la structure avant le début de la transaction, en clair on fait un Ctrl + Z sur notre base de données. Très utile si il y a un problème comme celui ci par exemple :

-- on désactive l'autocommit
SET AUTOCOMMIT =0;
-- on lance la transaction
START TRANSACTION;
-- on effectue cette simple requête sur notre table compte
UPDATE compte SET montant = montant + 20000 WHERE nom = 'vendeur';
-- cette requête retourne une erreur
UPDATE compte SET montant = montant - 20000 WHERE nom = 'machin';
-- on annule donc la transaction
ROLLBACK ;

Si ces deux opérations avaient été effectuées hors d'une transaction, le vendeur aurait bien reçu son argent mais il y aurait eut un problème puisque le membre machin n'existe pas, donc au final la somme totale dans ma table n'est plus la même, on a plus alors une base cohérente d'où l'importance des transactions.

Afin d'utiliser au mieux les transactions, on va maintenant passer à des exemples en utilisant PHP.

---

La pratique : utilisation de PDO

PDO comme interface avec MySQL

Présentation de PDO

Il existe de nombreux moyens pour se connecter à une base de données avec PHP, l'un d'eux est d'utiliser PDO qui permet notamment d'utiliser les transactions nativement. Pour ceux qui ne connaissent pas je vous conseille de lire ce tutoriel afin d'avoir cette interface en main.

PDO fait notamment intervenir le concept de programmation orientée objet, cela reste néanmoins très simple et accessible à celui qui n'aurait jamais programmé en objet. La première chose à faire est de se connecter à la base de données : il faut comme avec le pilote MySQl habituel fournir les informations suivantes :

• 
  

  L'adresse de la base de données (localhost si vous travaillez en local)

  
  


• 
  

  Le nom de la base de données (ici transactions)

  
  


• 
  

  Le nom d'utilisateur (ici root)

  
  


• 
  

  Et bien sûr le mot de passe pour accéder à la base de données (ici test)

  
  

Il ne reste plus qu'à créer un objet PDO pour interagir avec MySQL.

<?php $pdo = new PDO('mysql:host=localhost;dbname=transactions', 'root', 'test'); ?>

Pour plus de détails je vous conseille de lire le tutoriel que je vous ai suggéré précédemment.

Les exceptions

Un des avantages de PDO est de pouvoir utiliser les exceptions. Cela va nous permettre de lancer une transaction et selon le résultat (échec ou réussite de la transaction) agir en conséquence :

• 
  

  En cas de réussite : continuer la suite du script (confirmation, validation, etc).

  
  


• 
  

  En cas d'échec : une erreur ayant été détectée, il est alors possible de la récupérer (dans un log par exemple) pour ensuite la traiter. Le plus souvent les erreurs détectées sont des problèmes de clefs étrangères ou tout simplement des erreurs de codage. En ce sens les exceptions sont aussi très utiles pour débugger un système sans tout bousiller lors des tests.

  
  

Le plus simple pour moi étant de vous monter un exemple, vous comprendrez tout de suite mieux. Une va utiliser un exception pour se connecter à la base de données précédente.

<?php
try
{
    $pdo = new PDO('mysql:host=localhost;dbname=transactions', 'root', 'test');
}
catch(Exception $e)
{
    echo 'Echec de la connexion à la base de données';
    exit();
}
?>

La première partie peut être vue comme une tentative de la part du système (try veut dire essai en anglais) d'exécuter le code placé dans la première accolade. Si tout se passe bien alors tout va bien mais si la portion de script renvoie une erreur, elle va alors pouvoir être utilisée par l'instruction catch (attraper en anglais). Ensuite le code placé dans les accolades sera exécuté. Dans notre exemple on va alors afficher l'erreur afin de savoir pourquoi la connexion à la base de données a échouée.

<?php
catch(Exception $e)
{
    echo 'Erreur : '.$e->getMessage().'<br />';
    echo 'N° : '.$e->getCode();
}

La plupart du temps l'erreur est explicite (mauvais mot de passe, la bdd n'existe pas, le serveur SQl ne répond pas), sinon avec le numéro d'erreur donné et une recherche sur Google on trouve très vite la solution. Ce système est utilisé sur le SdZ pour afficher une page d'erreur lorsque le serveur PostgreSQL a un problème (souvent une surcharge).

Une transaction complète

La première étape consiste à initialiser la transaction avec la méthode PDO beginTransaction (on a rarement fait plus simple). Je ne reprends pas le code précédent vous êtes grand.

<?php $pdo->beginTransaction(); ?>

La suite est très simple, il suffit d'exécuter simplement les requêtes SQL que l'on désire (ces requêtes sont fantaisistes) :

<?php
$pdo->query('SELECT * FROM machin WHERE bidule = \'truc\'');

$pdo->query('INSERT INTO machin SET bidule = \'truc\', chose = \'moi\'');

$pdo->query('UPDATE machin SET nombre = nombre + 1');

Enfin on applique toutes ces requêtes avec la méthode commit :

<?php $pdo->commit(); ?>

Si tout se passe bien alors ces 3 requêtes seront appliquées à la base de données, mais en cas d'erreur on va la traiter et annuler les requêtes précédentes avec la méthode rollback.

<?php $pdo->rollback(); ?>

Ce qui avec le système des exceptions donne cela au final :

<?php
try
{
    //on tente d'exécuter les requêtes suivantes dans une transactions

    //on lance la transaction
    $pdo->beginTransaction();

    //nos 3 requêtes
    $pdo->query('SELECT * FROM machin WHERE bidule = \'truc\'');

    $pdo->query('INSERT INTO machin SET bidule = \'truc\', chose = \'moi\'');

    $pdo->query('UPDATE machin SET nombre = nombre + 1');

    //si jusque là tout se passe bien on valide la transaction
    $pdo->commit();
   
    //on affiche un petit message de confirmation
    echo 'Tout s\'est bien passé.';
}
catch(Exception $e) //en cas d'erreur
{
    //on annule la transation
    $pdo->rollback();

    //on affiche un message d'erreur ainsi que les erreurs
    echo 'Tout ne s\'est pas bien passé, voir les erreurs ci-dessous<br />';
    echo 'Erreur : '.$e->getMessage().'<br />';
    echo 'N° : '.$e->getCode();

    //on arrête l'exécution s'il y a du code après
    exit();
}

Voilà donc à quoi ressemble une transaction complète, cela permet d'être sûr que tout s'est bien passé et qu'on va pas se retrouver avec une base de données erronées comme par exemple un message d'un forum qui n'est rattaché à aucun sujet car la création du sujet a échoué.

Les transactions sont surtout utilisées sur des bases de données nécessitant une fiabilité à toute épreuve (finance, santé, défense). Tous les grands SGBDR (Oracle, PostgreSQl, Firebird, DB2, SQl-SERVEr, etc) fournissent cette fonctionnalité qui est indispensable pour certains.

Je suis conscient que pour des petits sites web les transactions ne sont pas une nécessité, néanmoins j'espère que ce tutoriel vous a plu.