5.5 Comment se débrouiller sans COMMIT/ROLLBACK

MySQL ne supporte pas COMMIT-ROLLBACK. Le problème tient à ce que COMMIT-ROLLBACK requiert une structure de table completement différente ce celle qui est utilisée actuellement. MySQL aurait besoin d'autres threads pour nettoyer automatiquement les tables et l'utilisation du disque serait augmentée d'autant. Ce qui ralentirait MySQL d'un facteur de 2 à 4. MySQL est beaucoup plus rapide que bien d'autres bases SQL (en général, de 2 à 3 fois plus rapide.). Une des raisons de cette vélocité est l'absence de COMMIT-ROLLBACK.

Pour le moment, nous sont concentrés sur l'implémentation du langage de serveur SQL (comme par exemple, les fonctions enregistrées). Avec ces fonctions, vous ne devriez faire appel au COMMIT-ROLLBACK que de manière exceptionnelle.

Les boucles que nécessite les transactions peuvent être remplacées avantageusement par les LOCK TABLES, et vous n'aurez pas besoin de curseurs si vous pouvez modifier des enregistrements à la volée.

Les transactions et les curseurs sont sur la liste de voeux, mais elles ne sont pas prioritaires. Si nous les implémentons, ce sera sous la forme d'une option de CREATE TABLE. Cela signifie que COMMIT-ROLLBACK ne fonctionnera qu'avec ces tables, ce qui ne pénalisera que les tables qui auront cette option, et non plus la base toute entière.

Ici, à TcX, nous avons un plus grand besoin d'une base de données rapide que d'une base de données généraliste. Si nous trouvons un moyen d'ajouter ces fonctionnalités sans perte de vitesse, nous le ferons sûrement. Pour le moment, nous avons d'autres choses plus importantes. Reportez tous à la liste des voeux pour savoir quelles sont les priorités (les clients de haut niveau de support peuvent cependant faire bouger les choses).

Le problème actuel est au niveau de ROLLBACK. Sans le ROLLBACK, vous pouvez effectuer des commandes de type COMMIT en utilisant la commande LOCK TABLES. Pour permettre le support du ROLLBACK, MySQL devra enregistrer tous les vieux enregistrements qui sont modifiés ou effacé, pour pouvoir les restituer si un ROLLBACK intervient. Pour les cas simples, ce n'est pas compliqué à mettre en place (l'utilitaire actuel isamlog peut servir à ce propos), mais cela peut se révéler plus compliqué pour les commandes ALTER/DROP/CREATE TABLE.

Pour éviter d'utiliser le ROLLBACK, vous pouvez suivre la stratégie suivante :

  1. Utilisez LOCK TABLES ... pour verrouiller les tables auxquelles vous accédez
  2. Testez les conditions d'utilisation.
  3. Modifiez si tout est OK.
  4. Utilisez UNLOCK TABLES pour libérer la table.

Généralement, cette méthode est beaucoup plus rapide que les transactions, et le ROLLBACK est souvent possible, mais pas toujours. Le seul point critique est que si le threads est tué au milieu de la modification, les verrous seront libérés, mais une partie des modifications ne sera pas faites.

Vous pouvez aussi utiliser les fonctions qui modifient un seul enregistrement à la fois. Vous pouvez créer des applications très efficaces avec la technique suivante :

  • Modifier un champs par rapport à sa valeur actuelle
  • Modifier seulement les champs qui ont changés

Par exemple, lors vous modifiez les informations concernant un client, ne modifiez que les informations qui ont changées, et non pas celle qui sont restées constantes. La recherche des valeurs est faites avec la clause WHERE de la commande UPDATE. Si l'enregistrement a changé, on peut retourner au client une message du type : "Les informations n'ont pas été modifiées, car un autre utilisateur est en train de modifier les valeurs ". Alors, on affiche l'ancienne valeur et la nouvelle, ce qui permet à l'utilisateur de décider quelle version utiliser.

Cela fournit un mécanisme du genre ``verrouillage de colonne'' mais c'est en fait un peut mieux, car seule les colonnes qui en on besoin sont utilisées. Une commande UPDATE ressemblera alors à ceci : 

UPDATE tablename SET pay_back=pay_back+'différence de valeur';
UPDATE customer
  SET
    customer_date='date actuellee',
    address='nouvelle adresse',
    phone='nouveau numero de telephone',
    débit =débit + 'autre_débit'
  WHERE
    customer_id=id AND address='vieille addresse' AND phone='vieux numero de telephone';

Comme vous pouvez le voir, c'est une méthode très efficace, et qui fonctionne même si un autre client a changé la valeur entre temps.

Dans certains cas, l'utilisateur ont demandé le ROLLBACK et/ou LOCK TABLES dans le but de gérer des identifiants uniques dans des tables . Il vaut mieux utiliser le type de colonne AUTO_INCREMENT et la fonction SQL LAST_INSERT_ID() ou l' API C : mysql_insert_id(). mysql_insert_id().

A TcX, nous n'avons jamais eu besoin d'un verrouillage de ligne, car nous avons toujours réussit à contourner le problème. Dans certains cas, le verrouillage de ligne était nécessaire, mais c'est très rarement le cas. Si vous voulez le verrouillage de ligne, utiliser un flag sur une colonne dans la table comme ci : 

UPDATE nom_table SET row_flag=1 WHERE id=ID;

MySQL retourne 1, ce qui correspond au nombre de ligne affectées par la commande, si la ligne a été trouvée, et que le flag row_flag n'était pas déjà à 1.

Vous pouvez considérer que MySQL traite la requête ci dessus de la manière suivante : 

UPDATE nom_table SET row_flag=1 WHERE id=ID and row_flag <> 1;