SQL Tips : Les transactions

But : Com­prendre ce qu'est une tran­sac­tion au sens SQL du terme, savoir l'utiliser : les avan­tages, les limi­ta­tions. J'aborderai super­fi­ciel­le­ment la notion de degré d'isolation.

Pré­re­quis : Savoir faire des requêtes.

Dif­fi­cul­té : 1 (Facile)

Tout d'abord une défi­ni­tion volon­tai­re­ment simple : une tran­sac­tion est un ensemble d'une ou plu­sieurs requêtes SQL regrou­pées au sein d'un bloc qui est exé­cu­té sur un jeu de don­nées.  Le début d'une tran­sac­tion est mar­qué par le mot clef BEGIN et la fin, en fonc­tion de ce qu'on sou­haite faire, par le mot clef ROLLBACK ou COMMIT. Tout les per­sonnes ayant uti­li­sé un ser­veur de base de don­nées ont déjà fait une tran­sac­tion. Une requête SQL est la plus petite tran­sac­tion pos­sible pour laquelle les mots clef de début et fin sont rajou­tés de manière impli­cite par le ser­veur de base de don­nées.

Nous allons reprendre la table pdb, créée lors du der­nier 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 à par­tir du résul­tat d'une requête. Cet article va nous per­mettre de voir com­ment rem­plir une table déjà exis­tante avec le résul­tat d'une requête.

Quelques expli­ca­tions : 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 lon­gueur com­prise 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 dou­ble­ment de ligne n'est pas gênant. On finit par une requête sup­pri­mant pure­ment et sim­ple­ment la table pdb_​tmp. Ma remarque sur le dou­ble­ment de ligne néces­site une expli­ca­tion : pour se situer dans la 3ième forme nor­male ma table devrait avoir une contrainte pri­maire ou unique sur une des colonnes qui la consti­tue : chaque ligne doit avoir un de ces attri­but unique, sou­vent un iden­ti­fiant,  par rap­port aux autres lignes de la table. Si cela était le cas dans ma table PDB, le ser­veur de base de don­née me répon­drait que mon ordre SQL d'insertion essaye d'ajouter une ligne que la contrainte d'unicité/primarité ne per­met pas. Par­lons main­te­nant de l'insertion à l'aide d'une requête SELECT. Un ordre SQL d'insertion res­semble à ceci :

Notre ordre SQL d'insertion est une com­bi­nai­son d'un ordre SQL d'insertion clas­sique avec une requête de sélec­tion. Nous deman­dons dans notre requête de sélec­tion, en uti­li­sant l'étoile, d'extraire trois colonnes. En allant plus loin, il devient obli­ga­toire de nom­mer chaque colonne dès lors que les tables ne sont pas iden­tiques. Les colonnes misent en cor­res­pon­dance doivent aus­si être iden­tiques une à une : l'insertion d'une chaîne de carac­tère dans la deuxième colonne, qui sert à sto­cker un chiffre (type entier), n'est pas pos­sible. Vous pou­vez insé­rer une constante dans toutes les lignes comme dans l'exemple sui­vant :

Nous savons main­te­nant créer une table de toute pièce et la rem­plir avec un ordre SQL. Pour reve­nir au sujet de l'article, quelques expli­ca­tions sur les mots clefs COMMIT et ROLLBACK. En plus de ter­mi­ner une tran­sac­tion, ils ont cha­cun un rôle dif­fé­rent :

• ROLLBACK : Restaure/​Annule toutes les modi­fi­ca­tions effec­tuées depuis le début de la tran­sac­tion.
• COMMIT : Valide toutes les modi­fi­ca­tions effec­tuées depuis le début de la tran­sac­tion.

Nous allons uti­li­ser les tran­sac­tions et leurs pos­si­bi­li­tés d'annulation, comme filet de sau­ve­tage ! (mais pas uni­que­ment)

Repre­nons les trois mêmes requêtes mais, suite à une faute de frappe, la lon­gueur mini­male 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 pre­nez le temps de véri­fier

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 lon­gueur soit infé­rieure à 100. Vous sou­hai­tez donc annu­ler vos opé­ra­tions.

code_​pdb	length	code_​uniprotkb
1F88	348	P02699
3IYO	666	Q1AHV0
3HYD	7	P01308
2FGT	417	Q794W0
3B4T	262	Q10628

Votre table est reve­nue à son état d'origine.

Lorsque aucune erreur n'a été com­mise ? Rem­pla­cez la requête SQL ROLLBACK par COMMIT et votre modi­fi­ca­tion est enre­gis­trée. Il peut arri­ver qu'une erreur de SQL se glisse dans la(les) requête(s) de votre tran­sac­tion, dans ce cas, un mes­sage vous infor­mant que la tran­sac­tion vient d'être auto­ma­ti­que­ment annu­lée sera affi­ché et il vous fau­dra recom­men­cer depuis le début.

Le deuxième avan­tage des tran­sac­tions vient des usages mul­ti-uti­li­sa­teurs des bases de don­né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 tran­sac­tion (pour ne pas dire pen­dant l'exécution de la ou des modi­fi­ca­tions) ?

Sans ren­trer dans le détail, le réglage par défaut du niveau d'isolation de Post­gres­ql et Mys­ql per­met, lors de la consul­ta­tion par d'autres uti­li­sa­teurs des mêmes tables, d'afficher ce qui a été com­mi­té et uni­que­ment cela. En d'autres mots, les don­nées appa­rais­sant pour un autre uti­li­sa­teur sont les don­nées qui ont été vali­dée par l'utilisateur fai­sant la modi­fi­ca­tion, vous ne ver­rez jamais des don­nées en cours de modi­fi­ca­tion avec le réglage par défaut.

Pour aller plus loin dans les tran­sac­tions, vous pou­vez lire ceci : http://​www​.post​gres​ql​.org/​d​o​c​s​/​9​.​1​/​s​t​a​t​i​c​/​t​r​a​n​s​a​c​t​i​o​n​-​i​s​o​.​h​tml

Pour conclure, ce filet de sécu­ri­té per­met d'effectuer toutes les modi­fi­ca­tions vou­lues, aus­si com­pli­quées soient-elles, sans mettre en péril vos don­nées. Atten­tion tout de même après un com­mit, la seule méthode per­met­tant de reve­nir en arrière est une sau­ve­garde régu­lière de vos don­nées.

Vou­lant que mes articles collent au plus proche des pro­blèmes et sujets que vous vou­lez résoudre, je vous mets à contri­bu­tion pour le sujet des pro­chains articles.

Cré­dit Image : Copy­left  Under­News, aucun droit réser­vé.