Astuce :
SQL Tips : Les transactions

But : Comprendre ce qu'est une transaction au sens SQL du terme, savoir l'utiliser : les avantages, les limitations. J'aborderai superficiellement la notion de degré d'isolation.

Prérequis : Savoir faire des requêtes.

Difficulté : 1 (Facile)

Tout d'abord une définition volontairement simple : une transaction est un ensemble d'une ou plusieurs requêtes SQL regroupées au sein d'un bloc qui est exécuté sur un jeu de données.  Le début d'une transaction est marqué par le mot clef BEGIN et la fin, en fonction de ce qu'on souhaite faire, par le mot clef ROLLBACK ou COMMIT. Tout les personnes ayant utilisé un serveur de base de données ont déjà fait une transaction. Une requête SQL est la plus petite transaction possible pour laquelle les mots clef de début et fin sont rajoutés de manière implicite par le serveur de base de données.

Nous allons reprendre la table pdb, créée lors du dernier article : lien

code_pdb length code_uniprotkb
1F88 348 P02699
3IYO 666 Q1AHV0
3HYD 7 P01308
2FGT 417 Q794W0
3B4T 262 Q1062

Nous avons vu la méthode pour créer une table à partir du résultat d'une requête. Cet article va nous permettre de voir comment remplir une table déjà existante avec le résultat d'une requête.

Quelques explications : La 1ère requête crée une table pdb_tmp qui est une copie de la table pdb. On ajoute ensuite les lignes ayant une longueur comprise entre 100 et 450 dans la table pdb avec la deuxième requête. Des lignes seront donc présentes en double dans la table, celle-ci n'ayant pas de contrainte ce doublement de ligne n'est pas gênant. On finit par une requête supprimant purement et simplement la table pdb_tmp. Ma remarque sur le doublement de ligne nécessite une explication : pour se situer dans la 3ième forme normale ma table devrait avoir une contrainte primaire ou unique sur une des colonnes qui la constitue : chaque ligne doit avoir un de ces attribut unique, souvent un identifiant,  par rapport aux autres lignes de la table. Si cela était le cas dans ma table PDB, le serveur de base de donnée me répondrait que mon ordre SQL d'insertion essaye d'ajouter une ligne que la contrainte d'unicité/primarité ne permet pas. Parlons maintenant de l'insertion à l'aide d'une requête SELECT. Un ordre SQL d'insertion ressemble à ceci :

Notre ordre SQL d'insertion est une combinaison d'un ordre SQL d'insertion classique avec une requête de sélection. Nous demandons dans notre requête de sélection, en utilisant l'étoile, d'extraire trois colonnes. En allant plus loin, il devient obligatoire de nommer chaque colonne dès lors que les tables ne sont pas identiques. Les colonnes misent en correspondance doivent aussi être identiques une à une : l'insertion d'une chaîne de caractère dans la deuxième colonne, qui sert à stocker un chiffre (type entier), n'est pas possible. Vous pouvez insérer une constante dans toutes les lignes comme dans l'exemple suivant :

Nous savons maintenant créer une table de toute pièce et la remplir avec un ordre SQL. Pour revenir au sujet de l'article, quelques explications sur les mots clefs COMMIT et ROLLBACK. En plus de terminer une transaction, ils ont chacun un rôle différent :

  • ROLLBACK : Restaure/Annule toutes les modifications effectuées depuis le début de la transaction.
  • COMMIT : Valide toutes les modifications effectuées depuis le début de la transaction.

Nous allons utiliser les transactions et leurs possibilités d'annulation, comme filet de sauvetage ! (mais pas uniquement)

Reprenons les trois mêmes requêtes mais, suite à une faute de frappe, la longueur minimale n'est plus 100 mais 1, les zéros ont été oubliés, vos requêtes SQL sont :

Vous n'avez pas vu l'erreur de frappe. Par acquis de conscience vous prenez le temps de vérifier

code_pdb length code_uniprotkb
1F88 348 P02699
3IYO 666 Q1AHV0
3HYD 7 P01308
2FGT 417 Q794W0
3B4T 262 Q10628
1F88 348 P02699
3HYD 7 P01308
2FGT 417 Q794W0
3B4T 262 Q10628

Le code PDB 3HYD est présent deux fois bien que sa longueur soit inférieure à 100. Vous souhaitez donc annuler vos opérations.

code_pdb length code_uniprotkb
1F88 348 P02699
3IYO 666 Q1AHV0
3HYD 7 P01308
2FGT 417 Q794W0
3B4T 262 Q10628

Votre table est revenue à son état d'origine.

Lorsque aucune erreur n'a été commise ? Remplacez la requête SQL ROLLBACK par COMMIT et votre modification est enregistrée. Il peut arriver qu'une erreur de SQL se glisse dans la(les) requête(s) de votre transaction, dans ce cas, un message vous informant que la transaction vient d'être automatiquement annulée sera affiché et il vous faudra recommencer depuis le début.

Le deuxième avantage des transactions vient des usages multi-utilisateurs des bases de données. Que ce passe-t-il lorsque quelqu'un consulte la table pdb entre la(les) modification(s)  de la table et la fin de la transaction (pour ne pas dire pendant l'exécution de la ou des modifications) ?

Sans rentrer dans le détail, le réglage par défaut du niveau d'isolation de Postgresql et Mysql permet, lors de la consultation par d'autres utilisateurs des mêmes tables, d'afficher ce qui a été commité et uniquement cela. En d'autres mots, les données apparaissant pour un autre utilisateur sont les données qui ont été validée par l'utilisateur faisant la modification, vous ne verrez jamais des données en cours de modification avec le réglage par défaut.

Pour aller plus loin dans les transactions, vous pouvez lire ceci : http://www.postgresql.org/docs/9.1/static/transaction-iso.html

Pour conclure, ce filet de sécurité permet d'effectuer toutes les modifications voulues, aussi compliquées soient-elles, sans mettre en péril vos données. Attention tout de même après un commit, la seule méthode permettant de revenir en arrière est une sauvegarde régulière de vos données.

Voulant que mes articles collent au plus proche des problèmes et sujets que vous voulez résoudre, je vous mets à contribution pour le sujet des prochains articles.

Crédit Image : Copyleft  UnderNews, aucun droit réservé.

Un commentaire sur “SQL Tips : Les transactions

  1. Je pense que les bases sont maintenant posées.
    Tu pourrais par exemple nous expliquer comment migrer une base de données d'un serveur à un autre, expliquer quels peuvent être les soucis durant cette manipulation et leurs solutions pour un de tes prochains billets.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

XSLT by CarLake