1.8.5.3 Transactions et opérations atomiques

Le serveur MySQL (version 3.23 MySQL-max et toutes les versions 4.0 et plus récent) supporte les transactions avec les gestionnaires de tables InnoDB et BDB . InnoDB dispose aussi de la compatibilité ACID totale . Types de tables MySQL .

Comment exploiter les capacités de MySQL pour protéger l'intégrité des données, et comment ces fonctionnalités se comparent elles avec les méthodes transactionnelles ?

• En mode transactionnel, si votre application a été écrite en dépendant de l'appel de ROLLBACK au lieu de COMMIT dans les situations critiques, les transactions sont plus pratiques. Les transactions s'assurent que les modifications non achevées ou les activités corrosives ne sont pas archivées dans la base. Le serveur a l'opportunité d'annuler automatiquement l'opération, et votre base de données est sauve.

  Le serveur MySQL, dans la plupart des cas, vous permet de résoudre les problèmes potentiels en incluant de simples vérifications avant les modifications, et en exécutant des scripts simples pour vérifier l'intégrité de vos bases de données, ainsi que les incohérences, et pour réparer automatiquement les problèmes, ou encore vous alerter si une erreur est identifiée. Notez qu'en utilisant simplement le log de MySQL, ou en utilisant un log supplémentaire, vous pouvez normalement réparer à la perfection toutes les tables, sans aucune perte de données.

• Souvent, les modifications de données transactionnelles fatales peuvent être réécrites de manière atomique. En général, tous les problèmes d'intégrité que les transactions résolvent peuvent être corrigés avec la commande LOCK TABLES ou des modifications atomiques, qui assurent que vous n'aurez jamais d'annulation automatique de la base, ce qui est un problème commun des bases transactionnelles.
• Même un système transactionnel peut perdre des données si le serveur s'arrête. La différence entre les systèmes repose alors dans ce petit laps de temps où ils peuvent perdre des données. Aucun système n'est sécurisé à 100%, mais simplement ``suffisamment sécurisé''. Même Oracle, réputé pour être la plus sûre des bases de données transactionnelles, est montré du doigt pour perdre des données dans ces situations.Pour être tranquille avec MySQL, que vous utilisiez les tables transactionnelles ou pas, vous n'avez besoin que de sauvegardes et de logs de modifications. Avec ces deux outils, vous pourrez vous protéger de toutes les situations que vous pourriez rencontrer avec d'autres bases de données transactionnelles. De toute manière, il est bon d'avoir des sauvegardes, indépendamment de la base que vous utilisez.

La méthode transactionnelle a ses avantages et ses inconvénients. De nombreux utilisateurs et développeurs d'applications dépendent de la facilité de pallier un problème lorsqu'une annulation semble nécessaire ou presque. Cependant, même si vous êtes néophyte des opérations atomiques, ou plus familier avec les transactions, prenez en considération le gain de vitesse que les tables non transactionnelles offrent. Ces gains vont de 3 a 5 fois la vitesse des tables transactionnelles les plus rapides et les mieux optimisées.

``Atomique'', avec le sens que nous lui donnons, n'a rien de magique. Ce terme signifie simplement que vous pouvez être certain que lorsque vous modifiez des données dans une table, aucun autre utilisateur ne peut interférer avec votre opération, et qu'il n'y aura pas d'annulation automatique (ce qui pourrait arriver avec des tables transactionnelles si nous ne sommes pas trop soigneux). Le serveur MySQL garantit aussi qu'il n'y aura pas de lectures erronées.

• Les boucles qui requièrent les transactions peuvent normalement être implémentées avec la commande LOCK TABLES , et vous n'avez nul besoin de curseur lorsque vous modifiez des lignes à la volée.
• Pour éviter d'utiliser l'annulation ROLLBACK , vous pouvez adopter la stratégie suivante :
  • Utilisez la commande LOCK TABLES ... pour verrouiller toutes les tables que vous voulez utiliser.
  • Testez vos conditions.
  • Modifiez si tout est correct.
  • Utilisez UNLOCK TABLES pour libérer vos tables.
  Ceci est probablement une méthode bien plus rapide que ne le proposent les transactions, avec des annulations ROLLBACK possibles mais pas certaines. La seule situation que ce cas ne prend pas en compte est l'interruption du processus au milieu d'une mise à jour. Dans ce cas, tous les verrous seront levés, mais certaines modifications peuvent ne pas avoir été exécutées.
• Vous pouvez aussi utiliser des fonctions pour modifier des lignes en une seule opération. Vous pouvez créer une application très efficace en utilisant cette technique :
  • Modifiez les champs par rapport à leur valeur actuelle.
  • Modifiez uniquement les champs que vous avez réellement changé.

  Par exemple, lorsque nous modifions les données d'un client, nous ne modifions que les données du client qui ont changé et nous vérifions uniquement si les données modifiées ou les données qui en dépendent ont changé comparativement aux données originales. Les tests sur les données modifiées sont faits avec la clause WHERE dans la commande UPDATE . Si la ligne a été modifiée, nous indiquons au client : "Some of the data you have changed has been changed by another user" . En français : "certaines données que vous voulez modifier ont été modifiées par un autre utilisateur". Puis nous affichons l'ancienne ligne et la nouvelle ligne, pour laisser l'utilisateur décider quelle version il veut utiliser.

  Cela nous conduit à un résultat proche du verrouillage de ligne, mais en fait, c'est bien mieux, car nous ne modifions que les colonnes qui en ont besoin, en utilisant des valeurs relatives. Cela signifie qu'une commande UPDATE typique ressemble à ceci :

  UPDATE tablename SET pay_back=pay_back+'relative change'; UPDATE customer   SET     customer_date='current_date',     address='new address',     phone='new phone',     dette=dette+'emprunt'   WHERE     customer_id=id AND address='old address' AND phone='old phone';

  Comme vous pouvez le voir, c'est très efficace, et fonctionne même si un autre client a modifié la valeur pay_back ou dette .
• Dans de nombreuses situations, les utilisateurs ont souhaité les commandes ROLLBACK et/ou LOCK TABLES afin de gérer des identifiant uniques pour certaines tables. Ils peuvent être gérés bien plus efficacement en utilisant une colonne de type AUTO_INCREMENT , en corrélation avec la fonction LAST_INSERT_ID() ou la fonction C mysql_insert_id() . Fonctions d'informations . mysql_insert_id() .

  Vous pouvez éviter le verrouillage de ligne. Certaines situations le requièrent vraiment, mais elles sont rares. Les tables InnoDB supportent le verrouillage de ligne. Avec les tables MyISAM , vous pouvez utiliser une colonne de type sémaphore, et faire ceci :

  UPDATE tbl_name SET row_flag=1 WHERE id=ID;

  MySQL retournera 1 pour le nombre de lignes affectées si la ligne a été trouvée, car row_flag ne vaut pas déjà 1 dans la ligne originale.

  Vous pouvez comprendre la requête ci-dessus comme si le serveur MySQL avait utilisé la commande suivante :

  UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;