IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

À quoi ça sert ???

À en croire certains intégristes (de MySQL pour ne pas le citer) certains des éléments des bases de données s'avéreraient parfaitement inutiles.
Aux orties donc les intégrités référentielles, les sous-requêtes, les requêtes ensemblistes, les triggers, les procédures stockées et autres UDF…
Bref un retour en arrière sur vingt années pendant lesquelles les bases de données relationnelles n'ont cessé de progresser.

Est-il réellement possible de se passer de tels éléments ?
Cet article fait le point sur la question, et en prime fait tomber le mythe de la rapidité de MySQL ! ♪

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

Préambule

Un gestionnaire de bases de données est un logiciel capable de traiter des données structurées dans un contexte de concurrence. C'est ainsi que plusieurs utilisateurs doivent pouvoir accéder aux données et les modifier sans qu'il en résulte de dégâts causés par des manipulations qui s'enchevêtrent. C'est ce que l'on appelle le respect de l'intégrité des données…
Nous allons voir les techniques que propose la norme SQL afin de garantir la bonne marche d'une base de données relationnelle.

1. À quoi servent les transactions ?

Image non disponible

Les transactions sont la clé même de toute problématique d'accès concurrent. Même lors de simples lectures, le SGBDR doit pouvoir assurer le respect de la cohérence des données dans le temps. Bien entendu, lors de modifications de données, les transactions servent à garantir que tout soit mené à bien sans qu'il en résulte la moindre anomalie, ou à l'inverse, si une anomalie se produit, de revenir à l'état antérieur, c'est-à-dire l'état qu'avaient les données avant le démarrage de la transaction.

Pour un aperçu de la chose, on peut lire l'article de Henri Cesbron Lavau sur le sujet à l'URL :
https://www.developpez.com/hcesbronlavau/Transactions.htm

La norme a défini deux éléments pour piloter les transactions :

  • la transaction elle-même ;
  • et sa « perméabilité » envers les transactions exécutées en parallèle.

La transaction permet de définir l'atomicité du traitement considéré.
Un traitement atomique est un traitement qui est considéré comme fonctionnant en tout ou rien : soit toutes les opérations relatives aux traitements sont exécutées, soit elles sont toutes annulées et les données reviennent dans l'état antérieur qui précédait juste la transaction.
La notion d'« atomique » vient du grec. L'atome pour les Grecs était la plus petite partie insécable de la matière. Autrement dit on ne pouvait pas couper plus finement la matière. Un code atomique est donc un programme qui s'exécute sans jamais être interrompu par un processus concurrent. Il a donc l'exclusivité des ressources pendant tout le temps de son exécution.

Pour mieux comprendre le problème, prenons un exemple simple. Soit les tables :

 
Sélectionnez
CREATE TABLE T_VOL
(VOL_ID INTEGER,
 VOL_REFERENCE CHAR(6),
 VOL_DATE DATE,
 VOL_PLACES_LIBRES INTEGER)
 
Sélectionnez
INSERT INTO T_VOL VALUES (1, 'AF 714', 7)
INSERT INTO T_VOL VALUES (2, 'AF 812', 6)
INSERT INTO T_VOL VALUES (4, 'AF 325', 258)
 
Sélectionnez
CREATE TABLE T_CLIENT_VOL
(CLI_ID  INTEGER, 
 VOL_ID INTEGER,
 VOL_PLACE_PRISE INTEGER)
 
Sélectionnez
INSERT INTO T_CLIENT_VOL VALUES (7, 1, 2)
INSERT INTO T_CLIENT_VOL VALUES (82, 4, 1)

Un client dont la clé est 77, veut prendre 5 places sur le vol 2.
Deux requêtes sont à exécuter. La première va décompter le nombre de places de la table T_VOL et la seconde va insérer une ligne dans la table T_CLIENT_VOL pour ce nouveau client. A priori les requêtes sont très simples d'expression :

 
Sélectionnez
UPDATE T_VOL
SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
WHERE  VOL_ID = 2

INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)

Apparemment tout marche bien… Sauf que nous n'avons pas pensé au contexte de concurrence. Et si un autre utilisateur fait un traitement similaire au même moment ? Que se passe-t-il ? Bien entendu je vois d'avance des voix s'élevant en disant que c'est quasiment impossible, parce que cela se joue à quelques millièmes de seconde… Mais en informatique, surtout aujourd'hui 1/1000e de seconde c'est 200 instructions machine !!! Donc, concurrence il peut y avoir, et il y aura certainement un jour ou l'autre…

Une première idée est de tester si réellement il y a bien au moins 5 places libres…
D'où le code suivant :

 
Sélectionnez
if (SELECT VOL_PLACES_LIBRES 
    FROM T_VOL WHERE VOL_ID = 2) >= 5
then
begin
   UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
   WHERE  VOL_ID = 2

   INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
end

Néanmoins, à tout moment juste après le begin ou l'update les données peuvent changer et conduire à la catastrophe…
Étudions le cas où deux processus concurrents effectuent des requêtes similaires et ajoutons le client 88 qui va demander 3 places sur le vol 2…

 
Sélectionnez
T1 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 5
   then
   begin
 
 
 

T3    UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
      WHERE  VOL_ID = 2
 
 

T5    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
   end
 
Sélectionnez
T2 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 3
   then
   begin
 
 

T4    UPDATE T_VOL
      SET   VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
      WHERE  VOL_ID = 2

T6    INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
   end

Voici le contenu des tables pour les temps T1 à T6 pour le vol 2 et les clients 77 et 88 :

Temps

 
Sélectionnez
T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------
 
Sélectionnez
T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

T1

 
Sélectionnez
2       AF 812         6
 

T2

 
Sélectionnez
2       AF 812         6
 

T3

 
Sélectionnez
2       AF 812         1
 

T4

 
Sélectionnez
2       AF 812         -2
 

T5

 
Sélectionnez
2       AF 812         -2
 
Sélectionnez
77      2       5

T6

 
Sélectionnez
2       AF 812         -2
 
Sélectionnez
77      2       5
88      2       3

Nous avons généré des places négatives et surbooké l'avion.

1-1. Transaction ou code client ?

Est-il possible de contourner le problème par un code plutôt que par une transaction ? Hélas non, car à chaque fois que nous testerons nous pouvons avoir un autre ordre SQL qui s'intercale. Par exemple l'idée de renverser la vapeur si nous avons généré des places négatives est bien tentante…

 
Sélectionnez
T1 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 5
   then
   begin
 
 
 

T3    UPDATE T_VOL
      SET        VOL_PLACES_LIBRES = 
        VOL_PLACES_LIBRES - 5
      WHERE  VOL_ID = 2
 
 
 

T5 if (SELECT VOL_PLACES_LIBRES
          FROM T_VOL WHERE VOL_ID = 2) < 0
 

T7    then
      begin
         UPDATE T_VOL
         SET    VOL_PLACES_LIBRES = 
             VOL_PLACES_LIBRES + 5
         WHERE  VOL_ID = 2 
      return
      end 
 
 
 
 
 
 

T9    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
   end
 
Sélectionnez
T2 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 3
   then
   begin
 
 

T4    UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = 
              VOL_PLACES_LIBRES - 3
      WHERE  VOL_ID = 2
 
 

T6 if (SELECT VOL_PLACES_LIBRES
          FROM T_VOL WHERE VOL_ID = 2) < 0
 
 
 
 
 
 

T8 then
     begin
        UPDATE T_VOL
        SET        VOL_PLACES_LIBRES = 
            VOL_PLACES_LIBRES + 3
        WHERE  VOL_ID = 2 
       return
     end 
 

T10    INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
   end

Hélas exécutée dans un contexte de concurrence elle devient tout aussi inacceptable !
Voici le contenu des tables pour les temps T1 à T10 pour le vol 2 et les clients 77 et 88 :

Temps

 
Sélectionnez
T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------
 
Sélectionnez
T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

T1

 
Sélectionnez
2       AF 812         6
 

T2

 
Sélectionnez
2       AF 812         6
 

T3

 
Sélectionnez
2       AF 812         1
 

T4

 
Sélectionnez
2       AF 812         -2
 

T5

 
Sélectionnez
2       AF 812         -2
 

T6

 
Sélectionnez
2       AF 812         -2
 

T7

 
Sélectionnez
2       AF 812         3
 

T8

 
Sélectionnez
2       AF 812         6
 

Nous n'avons toujours pas résolu notre problème. Car aucun client n'a été servi ! C'est encore pire, vous allez entraîner la faillite de votre compagnie en continuant comme ça !

En fait, seule une transaction peut permettre de nous sauver de l'embarras.

1-2. Pourquoi pas des verrous ?

Alors certains diront, « mais il suffit de poser un verrou sur la table … ou la ligne… ».
D'accord, sauf que cela n'existe pas en SQL et que les verrous sont des mécanismes internes en principe inaccessibles. Le verrou est en fait l'un des moyens d'assurer la concurrence entre les utilisateurs, mais ses effets dans le cadre d'une utilisation directe peuvent être plus pervers que l'utilisation des transactions.
En effet qui n'a jamais entendu parler du « verrou mortel », « dealock » en anglais, aussi appelé étreinte fatale.

L'utilisation de verrous, même si elle est permise par le SGBDR est à proscrire totalement sans une maîtrise parfaite des phénomènes de concurrence qui suppose d'avoir fait un diagramme du parallélisme des tâches afin de débusquer les points de concurrence sujets à blocage. Cela c'était l'informatique de grand-papa à l'aide de fichiers que l'on ouvrait en mode exclusif pour effectuer ses modifications en COBOL…
Encore faut-il savoir quels types de verrous sont disponibles sur le SGBDR : optimistes, pessimistes, exclusifs, partagés, de table, de page, de ligne de colonne ??????????

En revanche, voici comment ce problème est résolu par une gestion de transaction :

 
Sélectionnez
BEGIN TRANSACTION PLACE_AVION

UPDATE T_VOL
SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
WHERE  VOL_ID = 2

INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)

if (SELECT VOL_PLACES_LIBRES
    FROM T_VOL WHERE VOL_ID = 2) < 0
then
   ROLLBACK TRANSACTION PLACE_AVION
else
   COMMIT TRANSACTION PLACE_AVION

Étudions les effets de ce code en concurrence :

 
Sélectionnez
T1 BEGIN TRANSACTION PLACE_AVION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
   WHERE  VOL_ID = 2
T4 INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
T5 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) < 0
T6 then
       ROLLBACK TRANSACTION PLACE_AVION 1
T7 else
       COMMIT TRANSACTION PLACE_AVION 1
 
Sélectionnez
T2 BEGIN TRANSACTION PLACE_AVION 2

-- la transaction attend la libération
-- des ressources de l'autre transaction
 
 
 
 
 
 
 
 

T8 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
   WHERE  VOL_ID = 2
T9 INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
T10 if (SELECT VOL_PLACES_LIBRES
        FROM T_VOL WHERE VOL_ID = 2) < 0
T11 then
      ROLLBACK TRANSACTION PLACE_AVION 2
T12 else
      COMMIT TRANSACTION PLACE_AVION 2

Comme nous allons le voir, tout a bien fonctionné :

Temps

 
Sélectionnez
T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------
 
Sélectionnez
T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

TRANSACTION

T1

 
Sélectionnez
2       AF 812         6
 

DEBUT PLACE_AVION POUR UTILISATEUR 1

T2

 
Sélectionnez
2       AF 812         6
 

DEBUT PLACE_AVION POUR UTILISATEUR 2

T3

 
Sélectionnez
2       AF 812         1
 

TRANSACTION 2 MISE EN SOMMEIL

T4

 
Sélectionnez
2       AF 812         1
 
Sélectionnez
77      2       5
 

T5

 
Sélectionnez
2       AF 812         1
 
Sélectionnez
77      2       5
 

T7

 
Sélectionnez
2       AF 812         1
 
Sélectionnez
77      2       5

FIN PLACE_AVION POUR UTILISATEUR 1 AVEC COMMIT

T8

 
Sélectionnez
2       AF 812         -2
 
Sélectionnez
77      2       5
88      2       3

REPRISE DE LA TRANSACTION 2

T9

 
Sélectionnez
2       AF 812         -2
 
Sélectionnez
77      2       5
88      2       3
 

T10

 
Sélectionnez
2       AF 812         -2
 
Sélectionnez
77      2       5
88      2       3
 

T11

 
Sélectionnez
2       AF 812         1
 
Sélectionnez
77      2       5

FIN PLACE_AVION POUR UTILISATEUR 2 AVEC ROLLBACK

T6 n'est pas exécuté comme T12, l'utilisateur 1 voit sa transaction validée et le 2 la voit annulée. C'est-à-dire que le contenu des tables revient aux mêmes données qu'il y avait au début de la transaction T2.

1-3. Comment piloter une transaction ?

Nous venons de voir comment démarrer une transaction et comment la valider ou l'annuler. Il faut toujours qu'une transaction possède un ordre COMMIT ou ROLLBACK, sans quoi elle perdure jusqu'à plus soif… en principe jusqu'à ce que l'utilisateur se déconnecte.

ATTENTION : la norme prévoit que toute connexion à un SGBDR entame une transaction. C'est vrai sur certains serveurs, faux sur d'autres comme SQL Server qui travaillent en AUTOCOMMIT, c'est-à-dire que chaque ordre SQL constitue une transaction en soi, immédiatement autovalidée ou autoannulée. Dans ce cas, il faut obligatoirement commencer une transaction par l'ordre SQL BEGIN TRANSACTION.

Le serveur fait de l'AUTO COMMIT (hors norme SQL)

Le Serveur ne fait pas d'AUTO COMMIT (norme SQL)

 
Sélectionnez
BEGIN TRANSACTION [nom_transaction]
...
ROLLBACK | COMMIT TRANSACTION [nom_transaction]
...
COMMIT | ROLLBACK TRANSACTION [nom_transaction]
 
Sélectionnez
...
COMMIT | ROLLBACK
...
ROLLBACK | COMMIT

Parlons maintenant du niveau d'isolation, la fameuse « perméabilité » dont je vous ai parlé au début. C'est un concept un peu difficile à retenir, car il doit s'apprécier uniquement en pensant à deux transactions s'exécutant en concurrence. N'oublions pas que le SGBDR compte tenu des volumes de données à traiter doit pouvoir servir tout un chacun avec la même chance d'accès au serveur et donc les processus sont parallélisés.
Nous pourrions comparer l'isolation d'une transaction à la perméabilité d'un tuyau. Par exemple deux robinets (l'un de vin rouge, l'autre de blanc) situés côte à côte peuvent être dotés en sortie :

  • d'aucun tuyau (d'où des éclaboussures et le mélange des liquides pour faire du rosé !) ;
  • d'un tuyau en tissu qui s'humidifie, et peut polluer l'autre liquide s’il entre en contact ;
  • d'un tuyau en verre qui nous permet de voir les liquides couler et donc d'ajuster le débit ;
  • ou enfin d'un tuyau en métal opaque ne permettant ni vision ni fuite…

Le pilotage du niveau d'isolation est assuré par l'ordre SQL :

 
Sélectionnez
SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITED | READ COMMITED | REPEATABLE READ | SERIALIZABLE}

1-4. Anomalies du fait de processus concurrents

Ou les anomalies « transactionnelles »…

Pour comprendre l'utilité de ces différents niveaux, nous allons nous intéresser aux trois types d'anomalies possibles qui peuvent survenir lors de l'exécution concurrente d'ordres SQL.

  • L'anomalie la plus grave est la lecture impropre (lecture sale ou dirty read) : elle se produit lorsqu'une transaction lit des données qui n'ont pas encore été validées.
  • Suit, l'anomalie de lecture non répétable (non repeatable read) : deux lectures successives des mêmes données ne produisent pas le même résultat dans la même ligne.
  • Enfin la lecture fantôme (phantom read) est une anomalie qui se produit lorsque des données nouvelles apparaissent ou disparaissent dans des lectures successives.

Voici un exemple de lecture impropre : l'utilisateur 1 ajoute 10 places et annule sa transaction, tandis que l'utilisateur 2 veut 7 places si elles sont disponibles…

 
Sélectionnez
T1 BEGIN TRANSACTION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 10
   WHERE  VOL_ID = 2
 
 
 

T5 ROLLBACK TRANSACTION 1
 
Sélectionnez
T2 BEGIN TRANSACTION 2
 
 
 

T4 if (SELECT VOL_PLACES_LIBRES 
       FROM T_VOL WHERE VOL_ID = 2) >= 7
   then

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 7
      WHERE  VOL_ID = 2
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

Et les données qui sont manipulées :

Temps

 
Sélectionnez
T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------
 
Sélectionnez
T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

TRANSACTION

T1

 
Sélectionnez
2       AF 812         6
 

DEBUT TRANSACTION POUR UTILISATEUR 1

T2

 
Sélectionnez
2       AF 812         6
 

DEBUT TRANSACTION POUR UTILISATEUR 2

T3

 
Sélectionnez
2       AF 812         16
   

T4

 
Sélectionnez
2       AF 812         16
   

T5

 
Sélectionnez
2       AF 812         6
 

FIN TRANSACTION POUR UTILISATEUR 1 AVEC ROLLBACK

T6

 
Sélectionnez
2       AF 812         -1
   

T7

 
Sélectionnez
2       AF 812         -1
 
Sélectionnez
77      2       5
 

T8

 
Sélectionnez
2       AF 812         -1
 
Sélectionnez
77      2       5

FIN TRANSACTION POUR UTILISATEUR 2 AVEC COMMIT

Le temps d'un update avorté, la transaction 2 a lu des informations qu'elle n'aurait jamais dû voir et en a tiré la conclusion qu'elle pouvait servir les places… Conclusion surbooking !

Voici maintenant un exemple de lecture non répétable : nous allons considérer le cas ou notre opérateur désire toutes les places d'un vol s’il y en a plus de 4…

 
Sélectionnez
T3 BEGIN TRANSACTION 1
T4 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 4
   WHERE  VOL_ID = 2
T5 COMMIT TRANSACTION 1
 
Sélectionnez
T1 BEGIN TRANSACTION 2
T2 if (SELECT VOL_PLACES_LIBRES 
       FROM T_VOL WHERE VOL_ID = 2) >= 4
   then
 
 
 
 
 

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES -
             (SELECT VOL_PLACES_LIBRES
              FROM T_VOL WHERE VOL_ID = 2) 
      WHERE  VOL_ID = 2
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 2,
              (SELECT VOL_PLACES_LIBRES
               FROM T_VOL WHERE VOL_ID = 2))
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

Et les données qui sont manipulées :

Temps

 
Sélectionnez
T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------
 
Sélectionnez
T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

TRANSACTION

T1

 
Sélectionnez
2       AF 812         6
 

DEBUT TRANSACTION POUR UTILISATEUR 1

T2

 
Sélectionnez
2       AF 812         6
   

T3

 
Sélectionnez
2       AF 812         16
 

DEBUT TRANSACTION POUR UTILISATEUR 2

T4

 
Sélectionnez
2       AF 812         2
   

T5

 
Sélectionnez
2       AF 812         2
 

FIN TRANSACTION POUR UTILISATEUR 1 AVEC COMMIT

T6

 
Sélectionnez
2       AF 812         0
   

T7

 
Sélectionnez
2       AF 812         0
 
Sélectionnez
77      2       2
 

T8

 
Sélectionnez
2       AF 812         0
 
Sélectionnez
77      2       2

FIN TRANSACTION POUR UTILISATEUR 2 AVEC COMMIT

Notre utilisateur 2 voulait au moins 4 places et en a reçu 2… Conclusion, vous avez perdu un client !

Dernier cas, la lecture fantôme : notre utilisateur 2, désire n'importe quel vol pas cher pour emmener son équipe de foot (soit 11 personnes) à une destination quelconque.

 
Sélectionnez
T3 BEGIN TRANSACTION 1
T4 INSERT INTO T_VOL
   VALUES (5, 'AF 111', 125)
T5 COMMIT TRANSACTION 1
 
Sélectionnez
T1 BEGIN TRANSACTION 2
T2 if EXISTS (SELECT *
              FROM T_VOL
       WHERE VOL_PLACE_LIBRE >= 11)   then
 
 
 

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 11
      WHERE  VOL_PLACES_LIBRES >= 11
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 4, 11)
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

Et les données qui sont manipulées :

Temps

 
Sélectionnez
T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------
 
Sélectionnez
T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

TRANSACTION

T1

 
Sélectionnez
4       AF 325         258
 

DEBUT TRANSACTION POUR UTILISATEUR 1

T2

 
Sélectionnez
4       AF 325         258
   

T3

 
Sélectionnez
4       AF 325         258
 

DEBUT TRANSACTION POUR UTILISATEUR 2

T4

 
Sélectionnez
4       AF 325         258
5       AF 111         125
   

T5

 
Sélectionnez
4       AF 325         258
5       AF 111         125
 

FIN TRANSACTION POUR UTILISATEUR 1 AVEC COMMIT

T6

 
Sélectionnez
4       AF 325         247
5       AF 111         114
   

T7

 
Sélectionnez
4    AF 325         247
5       AF 111         114
 
Sélectionnez
77      4       11
 

T8

 
Sélectionnez
4       AF 325         247
5       AF 111         114
 
Sélectionnez
77      2       11

FIN TRANSACTION POUR UTILISATEUR 2 AVEC COMMIT

(1)11 places ont été volatilisées du vol AF 111 et c'est comme cela qu'un certain été, des avions d'Air France volaient à vide avec toutes les places réservées !!!

1-5. Niveau d'isolation des transactions et anomalies transactionnelles

C'est pour se préserver de telles anomalies que la norme SQL 2 a mis en place le niveau d'isolation des transactions.

Le tableau ci-dessous résume les différents niveaux d'isolations praticables et les anomalies qu'elles doivent impérativement éviter :

Anomalie

READ UNCOMMITED (niveau 0) possibilité de lire des informations qui sont en cours d'insertion, mais non validées

READ COMMITED (niveau 1) Des données peuvent être modifiées avant la fin de la transaction

REPEATABLE READ (niveau 2) De nouvelles lignes peuvent apparaître avant la fin de la transaction

SERIALIZABLE (niveau 3) les transactions sont placées en série ou le SGBDR fait « comme ci »

Lecture impropre

possible

impossible

impossible

impossible

Lecture non répétable

possible

possible

impossible

impossible

Lecture fantôme

possible

possible

possible

impossible

En principe, la norme fixe le niveau d'isolation par défaut du SGBDR à SERIALIZABLE !

En pratique, c'est rarement le cas… Par exemple SQL Server de Microsoft fonctionne par défaut, au niveau d'isolation 1 (READ COMMITED) ce qui explique sa relative grande rapidité, mais de possibles anomalies transactionnelles si l'on n’y prend pas garde.

Mais me direz, vous… Pourquoi accepter de descendre en dessous du niveau SERIALIZABLE ?
Simplement parce que ce niveau – le plus contraignant – entraîne une pénalisation certaine du serveur en termes de performance ! Or, nous n'avons pas toujours besoin de ce niveau d'extrême isolation… La norme SQL laisse le choix de piloter les transactions comme le développeur le veut, à condition qu'il ait ce choix et qu'il en mesure toutes les conséquences… En cette matière hélas, nombre de développeurs laissent faire le server sans se poser les bonnes questions….

Il est conseillé de se situer au moins au niveau correspondant aux utilisations suivantes :

  • UPDATE avec mise à jour de clé (primaire ou étrangères), INSERT : SERIALIZABLE
  • UPDATE sur valeurs courantes, DELETE filtrés sur clés : REPEATABLE READ
  • SELECT intègres, DELETE sans filtre : READ COMMITED
  • SELECT non intègres : READ UNCOMMITED

1-6. Conclusion

Vous avez compris que dès que des mises à jour interviennent en concurrence il peut survenir des anomalies transactionnelles, ce qui, dans ce cas, fait perdre à la base l'intégrité de ses données…
Il est donc impossible de se passer de la logique transactionnelle, sauf à définir un seul utilisateur à même d'effectuer toutes les mises à jour (INSERT, UPDATE, DELETE) ce qui cantonne les bases de données qui en sont dépourvues à des utilisations du genre « bases de données documentaires » où l'essentiel de l'activité du serveur constitue de la lecture de données.

1-7. Où placer les transactions ? Sur le serveur ou sur le client ?

OUI, mais… on peut gérer des transactions soit dans des procédures stockées au sein du serveur, soit dans du code client… Qu'est-ce qui est préférable ?

L'idée de manipuler des transactions depuis un code client (VB, Delphi, Java, C++…) est séduisante, mais « casse-gueule » et peut entraîner le pire du pire : un blocage total du serveur. En effet dès que l'on entame une transaction, le SGBDR pose les verrous adéquats sur les ressources visées par la procédure. Si le client perd la main sur son code et ne provoque jamais de COMMIT ou ROLLBACK, les ressources ne sont pas libérées et entraînent l'impossibilité pour les autres utilisateurs d'accéder aux données verrouillées. C'est pourquoi une logique transactionnelle doit toujours être exécutée au plus près du serveur et non sur le poste client, à moins que vous ayez prévu l'artillerie lourde : poste sur onduleur on line ou réseau électrique sur alimentation secourue, OS hyper stable, antivirus, etc.
De plus, il convient que la procédure ne soit jamais en attente d'une manipulation de l'utilisateur (comme une demande de saisie ou de confirmation), car toute attente bloque les ressources un certain temps et met en attente d'autres utilisateurs. C'est alors le château de cartes, chaque utilisateur attend qu'un autre libère les ressources et cela peut entraîner le blocage total du SGBDR, par exemple un verrou mortel…

C'est pourquoi on veillera à placer les transactions, soit dans une procédure stockée (l'idéal en termes de sécurité et d'intégrité) soit dans des objets métier appelés par un serveur d'application ou d'objet aussi bien sécurisé que le serveur SGBDR et dans un réseau connectiquement proche.

1-8. Qu'est-ce que le « verrou mortel » ?

Ce phénomène se produit lorsque deux (ou plus) utilisateurs veulent accéder aux mêmes ressources, mais dans une séquence de temps inverse. Nous avons dit que le SGBDR posait des verrous adéquats sur les ressources par rapport aux objets concernés par les transactions. Le type de verrou dépend d'ailleurs de l'ordre SQL exécuté et du niveau d'isolation demandé. Certains traitements nécessitent la pose simultanée de plusieurs verrous sur des tables différentes. Le verrou mortel s'appelle dans la littérature informatique « dead lock » en anglais, et encore étreinte fatale ou interblocage en français.
Regardons ce qui se passe dans deux transactions sujettes à un tel verrou mortel…

L'utilisateur 1 veut prendre 3 places d'avion sur le vol AF 714, tandis que l'utilisateur 2, qui vient d'être hospitalisé veut restituer 5 places sur ce même vol…

 
Sélectionnez
T1 BEGIN TRANSACTION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
   WHERE  VOL_ID = 1
 
 

T5 INSERT INTO T_CLIENT_VOL VALUES (77, 1, 3)
 
 

T7 COMMIT
 
Sélectionnez
T2 BEGIN TRANSACTION 2
 
 

T4 DELETE FROM T_CLIENT_VOL
   WHERE CLI_ID = 88
     AND VOL_ID = 1

T6 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 5
   WHERE  VOL_ID = 1

T8 COMMIT

Voyons ce qui se passe dans le détail, en matière de verrous, lors du déroulement concurrent de ces deux transactions :

T1

 
Sélectionnez
BEGIN TRANSACTION 1
 
Sélectionnez
La transaction T1 démarre normalement

T2

 
Sélectionnez
BEGIN TRANSACTION 2
 
Sélectionnez
La transaction T2 démarre normalement

T3

 
Sélectionnez
UPDATE T_VOL
SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
WHERE  VOL_ID = 1
 
Sélectionnez
AVANT d'exécuter la requête, le SGBDR pose
un verrou sur la table T_VOL pour l'utilisateur 1

T4

 
Sélectionnez
DELETE FROM T_CLIENT_VOL
WHERE CLI_ID = 88
  AND VOL_ID = 1
 
Sélectionnez
AVANT d'exécuter la requête, le SGBDR pose
un verrou sur la table T_CLIENT_VOL pour 
l'utilisateur 2

T5

 
Sélectionnez
INSERT INTO T_CLIENT_VOL VALUES (77, 1, 3)
 
Sélectionnez
Le SGBDR tente d'obtenir un verrou sur la table
T_CLIENT_VOL pour l'utilisateur T1,  mais il lui
est momentanément refusé parce que la table est
verrouillée par T2

T6

 
Sélectionnez
UPDATE T_VOL
SET VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 5
   WHERE  VOL_ID = 1
 
Sélectionnez
Le SGBDR tente d'obtenir un verrou sur la table
T_VOL pour l'utilisateur T2,  mais il lui
est momentanément refusé parce que la table est
verrouillée par T1

À ce stade les deux transactions sont en attente de libération de ressources. Aucune ne va se terminer, car chacune d'elle attend que l'autre libère la ressource dont elle à besoin. C'est l'interblocage, l'étreinte fatale, le verrou mortel ou deadlock.

1-9. Mais alors ? Les transactions peuvent induire un blocage ???

Oui et non. Cela dépend du SGBDR et du niveau d'isolation. Certains SGBDR comme Oracle ou InterBase sont dotés d'un algorithme qui empêche tout interblocage. Cet algorithme peut être un « time out », qui tue momentanément un processus au hasard lorsque plusieurs processus sont bloqués. Bien entendu, le niveau d'isolation joue aussi beaucoup sur ce phénomène. Un niveau sérializable indique au serveur de placer les transactions en série (ou de faire comme ci). Cela peut garantir qu'il n'y ait pas d'interblocage, mais pénalise le serveur puisqu'il devient impossible de paralléliser les processus. Une autre astuce est de toujours manipuler les tables, dans vos procédures stockées, comme dans votre code client, dans le même ordre (par exemple l'ordre alphabétique du nom de table)…
Notez que certains GSBDR, comme MS SQL Server sont assez sujets à l'interblocage et le seul remède est en général de « tuer » un utilisateur (enfin, sa connexion !).

2. À quoi sert l’intégrité référentielle ?

Image non disponible

L'intégrité référentielle sert à empêcher qu'une ligne d'une table qui référence une ligne d'une autre table voie le lien logique entre les deux lignes briser. Que serait une facture si le client venait à être effacé de la table des clients ?

Image non disponible

Ici, les commandes 6 et 7 font référence aux clients 4 et 9 qui n'existent pas dans la table T_CLIENT…

2-1. Le mécanisme

Le mécanisme d'intégrité référentielle doit permettre d'assurer :

  • que tout client référencé par une autre table ne soit pas supprimé, ou alors 
  • que l'on supprime aussi toutes les lignes filles des tables qui référencent le client supprimé ;
  • que la référence du client, si elle est modifiée, soit répercutée dans toutes les lignes des tables filles qui la référence, ou alors 
  • que toute modification de cette référence soit interdite, si des lignes de tables filles l'utilise.

La norme SQL 2 a prévu les modes de gestion des intégrités référentielles suivants :

 
Sélectionnez
ON DELETE { NO ACTION | CASCADE }
 
Sélectionnez
ON UPDATE { NO ACTION | CASCADE }

Qui signifie que :

 
Sélectionnez
ON DELETE NO ACTION

Si une suppression intervient alors que le client est référencé par une commande, la suppression est avortée

 
Sélectionnez
ON DELETE CASCADE

Si une suppression intervient alors que le client est référencé par une commande, la ou les commandes référencées pour ce client sont aussi supprimées

 
Sélectionnez
ON UPDATE NO ACTION

Si une mise a jour de la clé du client intervient alors que ce client est référencé par une commande, la modification est avortée

 
Sélectionnez
ON UPDATE CASCADE

Si une mise a jour de la clé du client intervient alors que ce client est référencé par une commande, la ou les commandes référencées pour ce client voient leur clé étrangère prendre la nouvelle valeur

Bien entendu on peut parfaitement supprimer un client qui ne possède pas de commande ou modifier la valeur de sa clé même lorsque le mode NO ACTION est actif.

Dans tous les cas, le mode NO ACTION est à préférer. En effet le mode CASCADE peut entraîner une avalanche de suppressions ou de mises à jour du plus mauvais effet sur les performances du SGBDR !

Ce mécanisme simple et robuste est complété par d'autres possibilités qu'offre la norme SQL et qui sont : SET DEFAULT et SET NULL.

 
Sélectionnez
ON DELETE SET DEFAULT

Si une suppression intervient alors que le client est référencé par une commande, les lignes des tables filles référencées par ce client voient la valeur de la clé passer à la valeur par défaut définie lors de la création de la table.

 
Sélectionnez
ON DELETE SET NULL

Si une suppression intervient alors que le client est référencé par une commande, la ou les commandes référencées pour ce client voient la valeur de la clé étrangère passer à NULL.

 
Sélectionnez
ON UPDATE SET DEFAULT

Si une mise à jour de la clé du client intervient alors que ce client est référencé par une commande, les lignes des tables filles référencées par ce client voient la valeur de la clé passer à la valeur par défaut définie lors de la création de la table.

 
Sélectionnez
ON UPDATE SET NULL

Si une mise a jour de la clé du client intervient alors que ce client est référencé par une commande, la ou les commandes référencées pour ce client voient la valeur de la clé étrangère passer à NULL.

Ces deux nouvelles règles proposent une alternative afin de gérer de manière intelligente une pseudo cascade…

Quel intérêt me direz vous d'avoir des commandes dont la référence du client est NULL ou bien 0 (un client générique dont l'existence physique est fausse, par exemple moi-même…) ?
L'intérêt est simple : pouvoir faire le ménage dans les tables de manière ultérieure… Par exemple on pourra prévoir de supprimer toutes les commandes dont la référence client est NULL ou 0 la nuit, lorsque le trafic du serveur est très faible dans un batch planifié déclenché automatiquement. Bien entendu, dans ce cas il faut faire attention à ne pas comptabiliser dans les requêtes les lignes dont la référence du client est NULL ou 0. Ainsi un cumul du montant des commandes pour connaitre le chiffre d'affaires généré mensuellement ne doit pas prendre en compte les lignes avec une référence de client 0 ou NULL…

Tous les SGBDR ne sont pas aussi performants que la norme l'impose en matière de gestion de l'intégrité référentielle. En effet, certains comme ORACLE ou InterBase ont implémenté la plupart de ces règles de gestion. D'autres comme MS SQL Server ne proposent que le NO ACTION. Dans ce dernier cas, l'utilisation de triggers permet de simuler les autres règles.

2-2. L'intégrité référentielle, fondement des SGBDR…

La gestion de l'intégrité référentielle est de loin le point le plus important pour décider si un SGBD est relationnel ou non. Dépourvu de ce mécanisme il agit comme au bon vieux temps des fichiers COBOL. Muni de ce mécanisme il agit en responsable de l'intégrité des données. Autrement dit un SGBD qui n'est pas doté d'un mécanisme de gestion des intégrités référentielles ne mérite tout simplement pas le nom de système de gestion de bases de données « relationnelles ».
C'est malheureusement le cas de MySQL, qui représente une régression certaine par rapport à des systèmes de fichiers plats comme Paradox qui la possède !

2-3. Peut-on se passer de l'intégrité référentielle ?

Dans la réalité c'est impossible ! La tentation de ne pas utiliser l'intégrité référentielle (ou d'avoir un SGBD qui ne le supporte pas) et faire cela dans du code client est séduisante… Mais casse-gueule : l'idée fallacieuse qui consiste à dire, je supprime d'abord les factures ensuite les clients, est inacceptable si elle n'est pas conduite dans une transaction. En effet rien n'empêche le code client de s'arrêter (panne disque, coupure de courant, réseau HS, pause café…) entre les deux requêtes ce qui supprime les commandes sans avoir pu supprimer le client…

À lire sur le sujet :
La gestion de l'intégrité référentielle

Mais, lisez la suite, car on peut effectivement se passer de l'intégrité référentielle à condition que la base de données supporte les triggers…

3. À quoi servent les déclencheurs (Triggers) ?

Image non disponible

S'il avait deux choses à retenir pour constituer un SGBDR au sens relationnel du terme, ce serait les transactions et les triggers. Avec ces deux outils, on peut aisément créer tous les mécanismes d'intégrité référentielle que l'on souhaite et assurer une parfaite intégrité des données. C'est d'ailleurs pour cela que la norme SQL 3 a imposé l'utilisation des triggers.

3-1. C'est quoi un trigger ?

Le terme français est « déclencheur ». Un trigger est donc un élément de code qui se déclenche sur un événement précis, se rapportant à un objet de la base de données. C'est exactement le même concept que la programmation événementielle dans le cadre d'interfaces graphiques. Je ne serais d'ailleurs pas étonné que les OnClick et autre OnMouseDown n'aient été inspirés par la notion de triggers des SGBDR, car il ne faut pas oublier que les SGBDR existaient bien avant les premiers langages faisant référence à des événements graphiques. Mais ceci n'est pas le débat.

Au sens de la norme SQL 3, un trigger se définit uniquement sur les objets de type TABLE, concerne les événements suivants : INSERT, UPDATE, DELETE et peut être déclenché BEFORE (c'est-à-dire avant survenance de l'événement) ou AFTER (c'est-à-dire après survenance de l'événement).
La grande différence entre le code événementiel graphique des langages comme Delphi ou Visual Basic, c'est que l'on peut placer du code, avant ou après le survenance de l'événement, alors que dans les langages graphiques, ce paramètre n'existe pas (enfin, j'allais oublier l'extraordinaire langage ObjectPal de Paradox qui proposait non seulement le pilotage avant après dans le même code, mais encore le « bouillonnement » de l'événement, c'est-à-dire sa remontée depuis le plus grand conteneur jusqu'à l'objet cible ! - au cas où vous l'auriez oublié, celui qui a créé ObjectPal a ensuite créé Delphi puis C# maintenant chez Microsoft !!!).

La syntaxe normative SQL 3 d'un trigger est la suivante :

 
Sélectionnez
CREATE TRIGGER nom_trigger
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON nom_table
code

Elle est plus conséquente dans son étendue, mais seule cette partie nous intéresse, car elle en synthétise toutes les possibilités.

La chose la plus attractive dans un trigger, sont les pseudotables OLD et NEW qui contiennent les données en cours d'insertion, de mise à jour ou de suppression…

3-2. Comment ça marche ?

Examinons ce qui se passe dans un cas concret.
Soit les tables :

 
Sélectionnez
CREATE TABLE T_CLIENT
(CLI_ID INTEGER NOT NULL PRIMARY KEY,
 CLI_NOM CHAR(32) NOT NULL,
 CLI_PRENOM VARCHAR(32),
 CLI_STATUT CHAR(1))
 
Sélectionnez
INSERT INTO T_CLIENT VALUES (1, 'DUPONT', 'Marcel', 'C')
INSERT INTO T_CLIENT VALUES (4, 'DUFOUR', 'Martin', 'C')
INSERT INTO T_CLIENT VALUES (17, 'DUHAMEL', 'Manuel', 'C')
INSERT INTO T_CLIENT VALUES (161, 'DUBOIS', 'Marie', 'C')
 
Sélectionnez
CREATE TABLE T_PROSPECT
(PRP_ID INTEGER NOT NULL PRIMARY KEY,
 PRP_NOM CHAR(32) NOT NULL,
 PRP_PRENOM VARCHAR(32))
 
Sélectionnez
INSERT INTO T_PROSPECT VALUES (14, 'Laporte', 'Eric')
INSERT INTO T_PROSPECT VALUES (17, 'Lambert', 'Ernest')
INSERT INTO T_PROSPECT VALUES (161, 'Laurent', 'Emeric')
INSERT INTO T_PROSPECT VALUES (4874, 'Lautier', 'Etienne')

Notre utilisateur veut faire passer tous les prospects dans la table T_CLIENT avec le statut 'P'. Oui, mais, au passage il veut leur donner une clé compatible avec le CLI_ID et reformater le nom en majuscules…

Or un simple ordre d'insertion basique du SQL va violer la contrainte d'unicité de la clé et rejeter en bloc l'insertion…

 
Sélectionnez
INSERT INTO T_CLIENT
SELECT PRP_ID, PRP_NOM, PRP_PRENOM, 'P'
FROM T_PROSPECT
 
Sélectionnez
Violation de la contrainte PRIMARY KEY 'PK__T_CLIENT__251C81ED'.
Impossible d'insérer une clé en double dans l'objet 'T_CLIENT'.
L'instruction a été arrêtée.

Mais nous pouvons pallier ce problème, sans rien toucher de notre ordre d'insertion en ajoutant un trigger qui va rectifier les données en cours d'insertion AVANT qu'elles ne soient réellement déposées dans la table :

 
Sélectionnez
CREATE TRIGGER TRG_INS_BEF_CLIENT
BEFORE INSERT 
ON T_CLIENT

UPDATE NEW /* cette pseudo table contient les prospects avant leur insertion ! */
SET PRP_ID = PRP_ID + (SELECT MAX(CLI_ID) FROM T_CLIENT), 
    PRP_NOM = UPPER(PRP_NOM) 
/* on modifie les données de la pseudo table avant de les insérer définitivement */

Dès lors notre insertion va bien se passer :

 
Sélectionnez
INSERT INTO T_CLIENT
SELECT PRP_ID, PRP_NOM, PRP_PRENOM, 'P'
FROM T_PROSPECT
 
Sélectionnez
(4) lignes ajoutées

Pour confirmation de ce qui s'est passé, nous pouvons relire la table client :

 
Sélectionnez
SELECT * 
FROM T_CLIENT
 
Sélectionnez
CLI_ID      CLI_NOM                          CLI_PRENOM                       CLI_STATUT 
----------- -------------------------------- -------------------------------- ---------- 
1           DUPONT                           Marcel                           C
4           DUFOUR                           Martin                           C
17          DUHAMEL                          Manuel                           C
161         DUBOIS                           Marie                            C
175         LAPORTE                          Eric                             P
178         LAMBERT                          Ernest                           P
322         LAURENT                          Emeric                           P
5035        LAUTIER                          Etienne                          P

3-3. À quoi ça sert ?

Mais, me direz-vous, cela nous aurions pu le faire directement dans l'ordre d'insertion… Par exemple avec la requête suivante :

 
Sélectionnez
INSERT INTO T_CLIENT
SELECT PRP_ID + (SELECT MAX(CLI_ID) FROM T_CLIENT), UPPER(PRP_NOM), PRP_PRENOM, 'P'
FROM T_PROSPECT

C'est tout à fait vrai, mais si vous aviez des données provenant de toute autre table, alors il aurait fallu adpater votre code et le modifier pour chaque cas, alors que dans le cas d'un trigger, ce code est générique quelle que soit l'insertion que vous voulez faire et d'où qu'elle provienne !

Les cas d'utilisation des triggers sont assez larges :

  • le formatage de données ;
  • l'auto incrémentation de clés ;
  • la suppression en cascade ;
  • l'abandon d'une suppression si elle entraîne des lignes orphelines

et plus généralement le traitement d'associations provenant de modèles complexes comme

  • les relations 1:n où n est une limite fixe (par exemple 3) ;
  • les antirelations (par exemple les mots noirs, voir Indexation textuelle) ;
  • les modèles à héritage (par exemple une entité générique VEHICULE et des entités spécialisées comme VOITURE, AVION et BATEAU…) ;
  • les arbres modélisés par intervalles (voir Gestion d'arbres par représentation intervallaire ) ;

Essayez donc de modéliser une relation dans laquelle le lien entre table mère et table fille ne doit pas dépasser un maximum de trois occurrences… Par exemple le prêt de livres dans une bibliothèque municipale ?

Dans un tel cas, pour empêcher une insertion surnuméraire, il suffit de compter les lignes déjà insérées, et dès que le nombre de lignes dépasse 3, alors l'invocation d'un ordre ROLLBACK empêche la pseudo table NEW d'attendre la table cible. Les données surnuméraires ne sont alors pas insérées.

Voici l'exemple d'un tel trigger :

 
Sélectionnez
CREATE TRIGGER TRG_INS_BEF_PRET
BEFORE INSERT 
ON T_CLIENT
FOR EACH ROW

IF EXISTS(SELECT 1
          FROM   T_PRET P
                 JOIN NEW N
                      ON P.EMPRUNTEUR_ID = N.EMPRUNTEUR_ID 
          HAVING COUNT(*) = 3)
THEN
   ROLLBACK

Que fait-on dans ce code ? D'abord notez l'expression FOR EACH ROW, qui signifie que le déclencheur va s'activer autant de fois qu'il y a de lignes dans la table NEW, en substituant la table NEW et ses n lignes en n déclenchements d'une table NEW ne contenant qu'une seule ligne.
Ensuite, notez qu'on lie la table NEW à la table des prêts sur l'identifiant de l'emprunteur. Puis on compte le nombre de lignes de cette jointure et on invoque un ROLLBACK si ce comptage est égal à 3, afin de ne pas rajouter de nouvelles lignes d'emprunts.
Simple non ? Et tellement élégant !

3-4. Peut-on s'en passer ? Faire autrement ?

Si l'on n'utilise pas de modèles complexes et que l'intégrité référentielle est en place, alors il est parfaitement possible de se passer des triggers. Mais vu leur souplesse, la sécurité qu'ils apportent, et la concision du code qu'ils offrent, il serait folie de s'en priver !

4. À quoi servent les sous-requêtes ?

Image non disponible

Certains commentaires d'utilisateurs indiquent régulièrement dans leurs mails qu'il suffit de faire des jointures pour remplacer toute sous-requête. Il est bien évident que le commité de normalisation du SQL composé de membres éminents comme Chris DATE, ne se serait pas évertué à les faire exister au sein de la norme si elles ne servaient à rien !

4-1. C'est quoi une sous-requête ?

Une sous-requête est une requête située à l'intérieur d'une autre requête. La plupart du temps il s'agit d'ordres SELECT imbriqués.
Pour un cours sur le sujet, voir :
les sous-requêtes

4-2. Peut-on s'en passer, faire autrement ?

Un simple exemple, donné par Peter GULUTZAN montre qu'il est impossible de s'en passer :
Soit la table :

 
Sélectionnez
CREATE TABLE TAB1 (COL1 INT) 
CREATE TABLE TAB2 (COL2 INT)
 
Sélectionnez
INSERT INTO TAB1 VALUES (1) 
INSERT INTO TAB1 VALUES (1) 
INSERT INTO TAB1 VALUES (2) 
INSERT INTO TAB2 VALUES (1) 
INSERT INTO TAB2 VALUES (2) 
INSERT INTO TAB2 VALUES (2)

Tentez donc de reproduire la requête si dessous et son résultat sans sous-requête…

 
Sélectionnez
SELECT TAB1.COL1 AS COL 
FROM   TAB1 
WHERE  TAB1.COL1 IN (SELECT TAB2.COL2 
                     FROM   TAB2)
 
Sélectionnez
COL 
---
1
1 
2

Bien entendu dans 80 % des requêtes que je vois passer devant mes yeux, il est possible de transformer la sous-requête en jointure à l'aide de contorsions assez délicates. Mais pour 20 % d'entre elles, il n'y a pas de solution. Tel est par exemple le cas des prédicats MATCH et UNIQUE. Tel est aussi le cas de la plupart des sous-requêtes implantées dans la clause from et contenant un opérateur d'agrégat statistique.

Les sous-requêtes facilitent la vie du développeur, s'écrivent plus facilement que des jointures dans bien des cas. Elles ont un revers, elles sont souvent moins performantes que des jointures…

L'idée fallacieuse qui consiste à dire que l'on peut s'en passer si l'on dispose de tables temporaires est fausse. En effet, entre l'instanciation d'une table temporaire et la requête suivante, les données peuvent changer et fausser le résultat. Il en résulterait des erreurs, à moins d'effectuer son code dans une transaction !
En effet il ne faut pas oublier qu'une requête est une transaction implicite…

C'est aussi assez difficile de traduire en code des sous-requêtes corrélées, car dans ce cas, l'exécution de la sous-requête est renouvelée pour chaque ligne de la requête principale… Le travail devient vite complexe et le temps d'exécution sur le poste client devient incommensurablement long !

5. À quoi servent les opérations ensemblistes ?

Image non disponible

Les opérations ensemblistes du SQL sont les suivantes :

  • l'union (UNION) :
  • l'intersection (INTERSECT) :
  • la différence (EXCEPT).

5-1. Ça ressemble à quoi ?

Si vous vous souvenez des patates ou diagrammes de Venn, alors vous pouvez les matérialiser facilement.
Dans le cas contraire, vous pouvez lire à ce sujet :
Les opérateurs ensemblistes

5-2. Est-il possible de s'en passer ?

Je vais vous surprendre… La réponse est indubitablement OUI !

L'intersection, comme la différence peuvent être réalisées par des constructions équivalentes à base de jointures ou de sous-requêtes. Voir :
Considérations diverses sur les opérations ensemblistes du SQL

Mais qu'en est-il pour l'union ?
Il est possible de la réaliser à deux conditions :

  • que le SGBDR possède une fonction ou structure de substitution des marqueurs NULL (COALESCE, CASE… ou équivalent) ;
  • que le SGBDR implémente le FULL OUTER JOIN.

C'est assez rare qu'il y ait ces deux éléments sans l'opérateur UNION. De plus, le cout de calcul de cet équivalent est assez élevé du fait de la présence des fonctions de transformation.

Voici une correspondance entre une union classique de deux tables (les données sont celles du jeu suivant :
Ensembles_exemples ) :

 
Sélectionnez
SELECT OBJ_NOM AS NOM, OBJ_PRIX AS PRIX
FROM   T_OBJET
UNION 
SELECT MAC_NOM AS NOM, MAC_PRIX AS PRIX 
FROM   T_MACHINE
ORDER  BY NOM, PRIX
 
Sélectionnez
NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0
 
Sélectionnez
-- union à base de jointure avec utilisation de COALESCE
SELECT COALESCE(OBJ_NOM, MAC_NOM) AS NOM,
       COALESCE(OBJ_PRIX, MAC_PRIX) AS PRIX
FROM   T_OBJET O
       FULL OUTER JOIN T_MACHINE M
            ON O.OBJ_NOM = M.MAC_NOM
               AND O.OBJ_PRIX = M.MAC_PRIX
ORDER  BY NOM, PRIX
 
Sélectionnez
NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0
 
Sélectionnez
-- union à base de jointure avec utilisation de CASE
SELECT CASE 
          WHEN OBJ_NOM IS NULL
               THEN MAC_NOM
               ELSE OBJ_NOM
       END AS NOM,
       CASE
          WHEN OBJ_PRIX IS NULL
               THEN MAC_PRIX
               ELSE OBJ_PRIX
       END AS PRIX
FROM   T_OBJET O
       FULL OUTER JOIN T_MACHINE M
            ON O.OBJ_NOM = M.MAC_NOM
               AND O.OBJ_PRIX = M.MAC_PRIX
ORDER  BY NOM, PRIX
 
Sélectionnez
NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0

Sans ces éléments, point de salut pour faire l'union…

6. À quoi servent les procédures stockées ?

Une procédure stockée n'est ni plus ni moins qu'un bout de code qui s'exécute directement sur le serveur. Elle peut réaliser les mêmes traitements que vous réaliseriez par du code client. Disons donc tout de suite que l'on peut parfaitement s'en priver…
Mais alors quel est l'intérêt d'une procédure stockée ?

  • D'abord la centralisation… Que votre code client soit un client lourd ou un client léger, c'est le même appel qui pourra être fait.
  • Ensuite la sécurité… votre code peut être transactionné dans un environnement hyper sécurisé ce qui est rarement le cas du poste client.
  • Enfin la rapidité… qui mieux que votre SGBDR est capable de traiter des données situées dans des tables ? Même si l'on utilise un code client hyper pointu (C++, Delphi…) les couts induits par les allers et retours des données sur le réseau resteront toujours pénalisants, sans parler qu'en matière de traitement de données tabulaires, votre SGBDR fera toujours mieux avec son optimiseur que votre pauvre code fut-il optimisé.

Autrement dit, une procédure stockée est l'endroit idéal pour piloter les transactions.

Un exemple concret…

Soit une table des clients et des adresses des clients (pour MS SQL Server). On conserve toutes les adresses des clients afin de gérer leur obsolescence. Notez que l'on utilise un auto-incrément (IDENTITY)…

 
Sélectionnez
CREATE TABLE T_CLIENT
(CLI_ID  INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_NOM VARCHAR(32))
 
Sélectionnez
CREATE TABLE T_ADRESSE
(ADR_ID    INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_ID    INTEGER FOREIGN KEY REFERENCES T_CLIENT (CLI_ID),
 ADR_VOIE  VARCHAR(64) NOT NULL,
 ADR_CP    CHAR(5) NOT NULL,
 ADR_VILLE VARCHAR(32) NOT NULL)

Ce que l'on désire, c'est être assuré qu'en insérant un nouveau client, on y insère aussi l'adresse. Or nous savons qu'il n'est pas possible d'insérer simultanément dans deux tables différentes avec un ordre SQL basique. Ce problème peut aisément être contourné par une procédure stockée transactionnée…

 
Sélectionnez
CREATE PROCEDURE SP_INS_CLIADR 
   @CLI_NOM   VARCHAR(32),
   @ADR_VOIE  VARCHAR(64),
   @ADR_CP    CHAR(5),
   @ADR_VILLE VARCHAR(32)
AS

-- variable pour récupérer l'identifiant auto-inséré
DECLARE @CLI_ID INTEGER

-- début de la transaction
BEGIN TRANSACTION 

-- insertion dans T_CLIENT
INSERT INTO T_CLIENT (CLI_NOM) 
       VALUES (@CLI_NOM)
IF @@ERROR <> 0
GOTO LBL_ERREUR

-- récupération du dernier auto-incrément inséré
SET @CLI_ID = @@IDENTITY

-- insertion dans T_ADRESSE
INSERT INTO T_ADRESSE (CLI_ID, ADR_VOIE, ADR_CP, ADR_VILLE)
       VALUES (@CLI_ID, @ADR_VOIE, @ADR_CP, @ADR_VILLE)
IF @@ERROR <> 0
GOTO LBL_ERREUR

-- validation
COMMIT
RETURN

-- annulation si l'une des deux requêtes d'insertion a provoqué une erreur
LBL_ERREUR:
ROLLBACK

Dès lors, l'appel de cette procédure avec de bons paramètres va ajouter les lignes dans les deux tables simultanément :

 
Sélectionnez
SP_INS_CLIADR 'DUPONT', '25 chemin des vendanges', '84190', 'BEAUMES DE VENISE'
 
Sélectionnez
SELECT * 
FROM T_CLIENT
 
Sélectionnez
SELECT * 
FROM T_ADRESSE
 
Sélectionnez
CLI_ID      CLI_NOM 
----------- --------
2           DUPONT
 
Sélectionnez
ADR_ID      CLI_ID      ADR_VOIE                                ADR_CP ADR_VILLE
----------- ----------- --------------------------------------- ------ -------------------
2           2           25 chemin des vendanges                 84190  BEAUMES DE VENISE

CQFD…

En revanche, une procédure stockée ne doit jamais s'occuper de faire de la lecture seule (une simple requête même complexe suffit en général) ou de la présentation de données (votre code client sera toujours plus apte que votre SGBDR pour tous les aspects « cosmétiques » !).

La norme SQL n'a quasiment rien défini sur le sujet. Ou plutôt devrais-je dire que la faiblesse de la norme en matière de procédure stockée est telle qu'à part la manipulation des curseurs, chaque éditeur de SGBDR a fabriqué un langage procédural spécifique à son serveur.
Pour Oracle, ce langage c'est PL/SQL (Programming Language / SQL), pour Sybase et MS SQL Server, c'est Transact SQL, pour InterBase, c'est ISQL (Interactive SQL), etc.

Un exemple du langage Transact SQL et de ses possibilités est visible ici. Il s'agit du Transact SQL de Microsoft SQL Server 7.

7. À quoi servent les UDF ?

Image non disponible

User Define Function / Fonctions utilisateurs

Les UDF (USer Define Function ou fonction utilisateur) sont un récent ajout de SQL puisqu'ils viennent de la version 3 (1999) de la norme. Ce sont des fonctions que tout utilisateur peut coder dans sa base de données et qui peuvent être appelées dans des requêtes SQL.

Ce concept existait déjà dans certains SGBDR comme InterBase. Les fonctions de InterBase doivent être réalisées dans un langage extérieur et sont « branchées » au serveur par exemple par le biais de DLL sous environnement MS Windows.

Dans la norme SQL 3, ces fonctions peuvent être réalisées soit en SQL, soit dans un langage externe reconnu (Ada, C, Fortran, Cobol, MUMPS, Pascal, PL1). En pratique cela pourra être fait sur d'autres langages, voire le langage procédural du serveur.

7-1. Quel intérêt ?

L'intérêt des UDF réside dans la possibilité d'ajout de fonctions puissantes ou non encore réalisées dans celles fournies par l'éditeur de votre SGBDR. Autrement dit, ajouter les manques du dialecte SQL de votre SGBDR.
Il y a un autre intérêt moins évident. Si vous devez réaliser des applications multiserveurs, c'est-à-dire des applications susceptibles de tourner sur différents SGBDR, et si les SGBDR cibles implémentent les UDF, alors il est possible de définir dans chaque serveur une bibliothèque d'UDF qui permettra d'uniformiser toutes les requêtes qui pourront s'adresser indifféremment à l'un ou l'autre des SGBDR cibles !

7-2. Un exemple ?

Pour la peine je vous en communique trois !

Le premier exemple donne la constante PI utilisable dans n'importe quelle requête :

 
Sélectionnez
CREATE FUNCTION PI()
LANGUAGE SQL
RETURNS DECIMAL(16,15)
CONTAINS SQL
RETURN 3.1415926535 89793
 
Sélectionnez
SELECT DIAMETRE * PI()
FROM   T_CERCLE

Le second exemple montre une fonction de conversion des francs en euros :

 
Sélectionnez
CREATE function franc_euros (VALEUR FLOAT)
LANGUAGE SQL
RETURNS DECIMAL (16, 2)
RETURNS NULL ON NULL 
DECLARE VALEUROS DECIMAL (16, 2)
SET VALEUROS = CAST(VALEUR as decimal(16,2) ) / 6.55957
RETURN CAST(VALEUROS as FLOAT)
 
Sélectionnez
SELECT franc_euros(MONTANT_TTC)
FROM   T_FACTURE

Le troisième montre comment on peut utiliser un langage externe pour réaliser une UDF. L'exemple de routine externe est en C et compte les mots d'un CLOB (Character Large OBject) :

 
Sélectionnez
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN wordcount(
   SQLUDF_CLOB     *in1,
   SQLUDF_INTEGER  *out,
   SQLUDF_NULLIND  *in1null,
   SQLUDF_NULLIND  *outnull,
   SQLUDF_TRAIL_ARGS) {

   SQLUDF_INTEGER count = 0;
   SQLUDF_INTEGER ind;
   SQLUDF_SMALLINT blank = 0;

   for (ind = 0; ind < in1->length; ind++) {
      if (blank == 0 && in1->data[ind] != ' ') {
         blank = 1;
         count++;
      } else if (blank == 1 && in1->data[ind] == ' ') {
         blank = 0;
      } /* endif */
   } /* endfor */

   *out = count;
   *outnull = 0;
}
 
Sélectionnez
CREATE FUNCTION WORD_COUNT (MyText CLOB)
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NO SQL
EXTERNAL NAME "UDFbibC/Wordcount"
RETURNS INTEGER
DECLARE N_WORDS INTEGER
SET N_WORDS = Wordcount(MyText)
RETURN (N_WORDS)

À ce stade certaines parties de la déclaration de la fonction sont spécifiques au SGBDR, en particulier la déclaration EXTERNAL NAME et l'appel de la fonction.

Certains SGBDR comme InterBase implémentent depuis longtemps des UDF notamment sous forme de modules externes que l'on peut écrire dans le langage de son choix, comme C ou Delphi, à condition que ces fonctions soient placées dans une bibliothèque sous forme de DLL ou équivalent (dépend de l'OS).

Exemple d'UDF externes pour InterBase réalisées sous Delphi :

 
Sélectionnez
//fichier projet delphi "UdfIBsqlPro.dpr"

library UDFibSQLpro;

uses
  SysUtils,
 Classes,
 UudfIB in 'UudfIB.pas';

{$R *.res}

// function flip (s : pchar; pivot : integer) : pchar;
// function reverse ( s : pchar) : pchar;

begin

end.
 
Sélectionnez
// fichier d'unité delphi "UudfIB.pas"

unit UudfIB;

interface

Uses
  IBexternals, IBheader, IBIntf;

// intervertit le début et la fin d'une chaine 
// par rapport au caractère pivot
function flip (s : pchar; pivot : integer) : pchar; cdecl;
// inverse les lettres d'un mot
function reverse ( s : pchar) : pchar; cdecl;

Exports
  flip, reverse;

implementation

function flip (s : pchar; pivot : integer) : pchar; cdecl;
var
  sd : string;
  sout : string;
begin
if length(s) = 0
then
begin
  flip := s;
  exit;
end;
sd := string(s);
if pivot >= length(sd)
then
begin
  flip := s;
  exit;
end;
sout := copy(sd, 1, pivot) + copy(sd, pivot+1, length(sd) - pivot);
flip := PChar(sd);
end;

function reverse ( s : pchar) : pchar; cdecl;
var
  sd : string;
  sout : string;
  i : integer;
begin
sd := string(s);
if length(sd) = 0
then
begin
  sout := s;
  exit;
end;
sout := '';
for i :=1 to length(sd)
do
  sout := sout + sd[i];
reverse := PChar(sout);
end;

Initialization
  IsMultithread := true;
  CheckIBloaded;

end.
 
Sélectionnez
/-- liaison entre les UDF et les modules externes

DECLARE EXTERNAL FUNCTION FLIP
   CSTRING(256), DOUBLE PRECISION
   RETURNS CSTRING(256)
   ENTRY_POINT "flip"
   MODULE_NAME "C:\IB\UDF\UdfIBsqlPro.dll"
DECLARE EXTERNAL FUNCTION REVERSE
   CSTRING(256)
   RETURNS CSTRING(256)
   ENTRY_POINT "reverse"
   MODULE_NAME "C:\IB\UDF\UdfIBsqlPro.dll"

COMMIT
 
Sélectionnez
-- exemple d'utilisation

SELECT FLIP(COL1, 3), REVERSE(COL2)
FROM TEST

Exemple d'UDF pour MS SQL Server 2000 :

 
Sélectionnez
-- Fred BROUARD - 2002-10-11 
-- extrait d'une chaine de caractères uniquement les chiffres sans aucun espace

CREATE  FUNCTION FN_FORMATE_CHIFFRE_SEUL (@VALUE VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
-- effets de bord : NULL
   IF @VALUE IS NULL
   RETURN @VALUE
-- effet de bord : chaine vide
   IF @VALUE = ''
   RETURN @VALUE
-- cas courant
   DECLARE @NEWVALUE VARCHAR(8000)
   SET @NEWVALUE = ''
   DECLARE @I INTEGER
   SET @I = 1
   WHILE @I <= LEN(@VALUE)
   BEGIN
      IF SUBSTRING(@VALUE, @I, 1) BETWEEN '0' AND '9'
      SET @NEWVALUE = @NEWVALUE + SUBSTRING(@VALUE, @I, 1)
      SET @I = @I + 1
   END 
   RETURN @NEWVALUE
END
 
Sélectionnez
-- Fred BROUARD - 2002-10-11 
-- extrait d'une chaine de caractères uniquement des lettres majuscules non diacritiques,
-- des chiffres et des espaces non doublonnés (conversion des caractères diacritiques)

CREATE  FUNCTION FN_FORMATE_MAJ_SANS_DIAC (@VALUE VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
-- effets de bord : NULL
   IF @VALUE IS NULL
   RETURN @VALUE
-- effet de bord : chaine vide
   IF @VALUE = ''
   RETURN @VALUE
-- cas courant
   DECLARE @NEWVALUE VARCHAR(8000)
   SET @NEWVALUE = ''
   DECLARE @I INTEGER
   SET @I = 1
   DECLARE @C CHAR(1)
   DECLARE @LETTRE BIT
   SET @VALUE = UPPER(@VALUE)
   WHILE @I <= LEN(@VALUE)
   BEGIN
      SET @LETTRE = 0
      SET @C = SUBSTRING(@VALUE, @I, 1)
      IF  CAST(@C AS VARBINARY(32)) BETWEEN CAST('0' AS VARBINARY(32)) 
                                        AND CAST('9' AS VARBINARY(32))
       OR CAST(@C AS VARBINARY(32)) BETWEEN CAST('A' AS VARBINARY(32))
                                        AND CAST('Z' AS VARBINARY(32))
      BEGIN 
          SET @NEWVALUE = @NEWVALUE + SUBSTRING(@VALUE, @I, 1)
          SET @LETTRE = 1
      END
      ELSE
      BEGIN
         IF @C='à' OR @C='À' OR @C='â' OR @C='Â' OR @C='ä' OR @C='Ä' OR @C='Á' 
         OR @C='á' OR @C='Ã' OR @C='ã' OR @C='å' OR @C='Å'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'A'
            SET @LETTRE = 1
         END
         IF @C='æ' OR @C='Æ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'AE'
            SET @LETTRE = 1
         END
         IF @C='Ç' OR @C='ç'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'C'
            SET @LETTRE = 1
         END
         IF @C='é' OR @C='É' OR @C='è' OR @C='È' OR @C='ê' OR @C='Ê' OR @C='ë' OR @C='Ë'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'E'
            SET @LETTRE = 1
         END
         IF @C='î' OR @C='Î' OR @C='ï' OR @C='Ï' OR @C='ì' OR @C='Ì' OR @C='í' OR @C='Í'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'I'
            SET @LETTRE = 1
         END
         IF @C='ñ' OR @C='Ñ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'N'
            SET @LETTRE = 1
         END
         IF @C='ô' OR @C='Ô' OR @C='ö' OR @C='Ö' OR @C='ò' OR @C='ó'
         OR @C='Ò' OR @C='Ó' OR @C='Õ' OR @C='õ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'O'
            SET @LETTRE = 1
         END
         IF @C='œ' OR @C='Œ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'OE'
            SET @LETTRE = 1
         END
         IF @C='ß'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'SS'
            SET @LETTRE = 1
         END
         IF @C='Ù' OR @C='Ú' OR @C='Û' OR @C='Ü' OR @C='ù' OR @C='ú' OR @C='û' OR @C='ü'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'U'
            SET @LETTRE = 1
         END
         IF @C='Ý' OR @C='ý' OR @C='ÿ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'Y'
            SET @LETTRE = 1
         END
         IF @LETTRE = 0
            SET @NEWVALUE = @NEWVALUE + ' '
      END
      SET @I = @I + 1
   END 
-- dédoublonnage des espaces parasites
   SET @VALUE = @NEWVALUE
   SET @NEWVALUE = ''
   SET @I = 1
   SET @LETTRE = 0
   WHILE @I <= LEN(@VALUE)
   BEGIN
      SET @C = SUBSTRING(@VALUE, @I, 1)
      IF  CAST(@C AS VARBINARY(32)) = CAST(' ' AS VARBINARY(32))
      BEGIN
         IF @LETTRE = 1
            SET @NEWVALUE = @NEWVALUE + @C
         SET @LETTRE = 0
      END
      ELSE
      BEGIN
         SET @NEWVALUE = @NEWVALUE + @C 
         SET @LETTRE = 1 
      END
      SET @I = @I + 1 
   END
   RETURN LTRIM(@NEWVALUE)
END
 
Sélectionnez
/* obtention de la liste des colonnes d'une table */

-- F. BROUARD - 2002-10-10

CREATE FUNCTION FN_LIST_COLUMNS (@TABLE_NAME VARCHAR(128)) 
RETURNS VARCHAR (8000)
AS
BEGIN
   DECLARE @LIST_COLS VARCHAR(8000)
   SET @LIST_COLS ='('
   SELECT @LIST_COLS = @LIST_COLS + COLUMN_NAME + ', '
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = @TABLE_NAME
   SET @LIST_COLS = SUBSTRING(@LIST_COLS, 1, LEN(@LIST_COLS)-1) + ')'
   RETURN(@LIST_COLS)
END

7-3. Peut-on s'en passer ? Faire autrement ?

Bien entendu… On peut réaliser quelques-unes des UDF dans des vues par exemple ou des procédures stockées. On peut encore faire un traitement des données reçues sur le poste client. Mais dans ce dernier cas, les performances ne sont pas très intéressantes.
Autrement dit les UDF ne sont pas absolument nécessaires, mais bien utiles !

8. La journalisation

ou comment sont réalisées les transactions par le serveur

La journalisation est le mécanisme de base permettant le transactionnel et assurant l'intégrité des données de la base.

8-1. À quoi ça sert ?

Cela sert à enregistrer toutes les manipulations de données qui sont lancées par des requêtes SQL sur le serveur, afin de les exécuter de la manière la plus intègre possible afin que tout problème survenant lors d'une transaction n'induise pas une « désintégration » de la base…
Le journal capte toutes les demandes et assure un mécanisme de reprise automatique dans le cas d'une panne, mais aussi gère les ROLLBACK et COMMIT.
Aucune base de données relationnelle gérant des transactions ne peut se passer de ce type de mécanisme.

Attention : une erreur fréquente est de croire qu'à l'aide du journal, on peut suivre ce que tel ou tel utilisateur a pu faire sur la base, ou bien quelles ont été les modifications survenues dans telle ou telle table. La journalisation est en principe un mécanisme strictement interne au SGBDR et donc réservé à son seul usage. Il n'est donc pas lisible pour un utilisateur, fut-il administrateur de la base de données ou du serveur.

8-2. Comment ça marche ?

Je n'entrerai pas dans les détails de l'implémentation de la journalisation de tel ou tel éditeur. Je vais simplement vous expliquer comment cela marche de manière générique…

Lorsqu'une requête du DML (Data Manipulation Language) c'est-à-dire un ordre SELECT, INSERT, UPDATE ou DELETE, est envoyée au serveur, le journal écrit dans son fichier les éléments relatifs à quel utilisateur se rapporte cette demande ainsi que quelques paramètres comme la date et l'heure de la demande. Le journal inscrit la requête telle qu'il la trouve ainsi qu'un marqueur pour signaler le début de la transaction (n'oubliez pas que toute requête, la plus simple soit-elle est une transaction à part entière).
Ces informations pourraient se présenter comme suit :

 
Sélectionnez
REQUEST FOR :
USER       = SQLpro
CONNECTION = 0x001252
SERVER     = SRV_PROD
DATABASE   = DB_CLIENT
QUERY IS :
   UPDATE T_PRIX
   SET PRX_PRIX = PRX_PRIX * 1.1
END QUERY
START      = 2002-09-11 13:24:55.235
BEGIN TRANSACTION 056512300651324568456521

Le SGBDR signale au client que la requête a été prise en compte et lui demande d'attendre le retour d'exécution.

Nous supposons que la table T_PRIX contienne les données suivantes :

 
Sélectionnez
CREATE TABLE T_PRIX
(PRX_ID INTEGER NOT NULL PRIMARY KEY,
 PRX_PRIX FLOAT NOT NULL)
 
Sélectionnez
INSERT INTO T_PRIX VALUES (1, 254.15)
INSERT INTO T_PRIX VALUES (2, 98541.24)
INSERT INTO T_PRIX VALUES (3, 8741.99)

Nous allons maintenant interrompre le courant électrique de notre serveur à tout instant pour voir comment se comporte le SGBDR…

L'interruption intervient juste après l'écriture du marqueur signalant le début de la transaction

 
Sélectionnez
BEGIN TRANSACTION 056512300651324568456521

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Il interdit à tout utilisateur de lui envoyer des requêtes tant qu'il n'a pas retrouvé son point d'équilibre (intégrité des données)…

Le serveur inscrit dans le journal les données concernées par la modification. Avant la fin de ce processus, le courant est coupé…

 
Sélectionnez
PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
2, 98541.24

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Si les données concernées par la modification n'ont pas été toutes écrites, il supprime celles déjà présentes et recommence son travail…

Le serveur recommence à inscrire dans le journal les données concernées par la modification. Puis il calcule les mises à jour et les inscrit dans le journal… Avant la fin de ce processus, le courant est coupé…

 
Sélectionnez
PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Si les données concernées par la modification n'ont pas été toutes calculées et inscrites, il supprime celles déjà présentes et recommence son travail…

Le serveur recommence à inscrire dans le journal les données calculées pour l'UPDATE. Il commence à recopier ces informations dans la table. Avant la fin de ce processus, le courant est coupé…

 
Sélectionnez
PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10
3, 9615,10
END FUTURE INFORMATION

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Si les données concernées par la modification n'ont pas été toutes répercutées dans la table, il recommence à la première valeur…

Le serveur a fini de répercuter les données calculées pour l'UPDATE dans la table et inscrit une marque de fin de transaction.

 
Sélectionnez
PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10
3, 9615,10
END FUTURE INFORMATION
TRANSACTION COMPLETE
WORK COMMITED

Et voilà ! Rien de plus simple en apparence… Rajoutez-y la gestion des verrous et une concurrence de traitement… et donnez m'en des nouvelles !!!

8-3. Peut-on s'en passer ? Faire autrement ?

On peut, bien entendu s'amuser à réinventer la roue… Implémenter une journalisation sur une base qui en est dépourvue n'a rien d'impossible. En revanche la complexité d'un tel programme en multiutilisateur est telle qu'il y a fort à parier que vous mettrez quelques années à en réaliser un performant… D'ailleurs, sachez que la gestion des transactions est en cours de développement pour le SGBD MySQL, depuis… combien de temps déjà ? Oui, oui, je sais, ce sera dans la future version…
Mais que je vous rassure, la plupart des véritables SGBDR font cela depuis… combien de temps déjà ? Ah oui, deux décennies !

9. MySQL est-il vraiment rapide ?

Vous vous doutez bien que si j'ai écrit ce titre racoleur, c'est parce que je possède quelques arguments pour vous convaincre que sa pseudorapidité est un leurre !

Oui, MySQL est rapide. En lecture seulement et encore je vais y mettre un bémol… Comme il ne gère pas les transactions, il conviendrait de comparer ce qui est comparable. Dans le test « crash me » MySQL a été comparé à Oracle, SQL Server, DB2, InterBase, etc., c'est-à-dire tout un tas de SGBDR qui travaillent avec des transactions et font donc de la journalisation contrairement à MySQL qui ne fait rien de tout cela ! Autrement dit, ce test pénalise d'emblée tous les SGBDR qui ont été inscrits en comparaison. Notons que les seuls SGBDR qui ne pratiquent pas le transactionnel comme Paradox, FoxPro ou dBase ont été soigneusement écartés… Curieux non ? On y a pris soin quand même d'y laisser Access, le plus mauvais et le moins performant des SGBDR fonctionnant en mode fichiers.
Le jeu de test a été écrit pour MySQL, par les auteurs de MySQL. Que dirait-on si Oracle ou Microsoft faisait son propre jeu de données et établissait ses propres requêtes afin de se comparer à d'autres SGBDR ? Qu'il s'arrangerait pour arriver vainqueur non ?

Or les rares tests qui ont été effectués par des personnes ou des organismes indépendants et en toute sérénité montrent que MySQL n'est pas rapide, loin s'en faut, surtout en contexte de concurrence. Il arrive même à MySQL de « tomber » assez rapidement dès que quelques utilisateurs se pressent pour lancer leurs requêtes.

Voici les éléments d'un test comparatif effectué entre PostGreSQL et MySQL. Il montre clairement que MySQL est en dessous des performances annoncées et pratiquement incapable d'une montée en charge correcte.
Vous trouverez le test et ses conditions dans l'article de Tim Perdue, publié sur PHPbuilder :
http://www.phpbuilder.com/columns/tim20001112.php3

À noter que la traduction qui a été faite par l'équipe de phpteam en français donne de fausses indications en prétendant que MySQL est plus rapide que PostGreSQL ce qui, de toute évidence est faux, tant à la lecture des graphiques qu'à la lecture du texte. Peut-être doit-on y voir l'origine de la rumeur sur la pseudorapidité de MySQL ?

Je ne donnerai qu'un seul extrait de cette étude, le plus dramatique pour MySQL. Voici un graphique qui résume la situation :

Image non disponible

Ce test est pratiqué sur une page HTML qui requiert 16 requêtes sur une douzaine de tables. De multiples clients attaquent cette page. Pour épicer un peu le problème, 25 % des transactions (uniquement sur PostGreSQL puisque MySQL ne pratique pas la chose) sont annulées par ROLLBACK.
À 5 clients simultanés MySQL est incapable de donner une seule page entière en moins d'une seconde, tandis que PostGreSQL en sert presque 2… Mais là où le bât blesse, c'est qu'à plus de 20 utilisateurs simultanés MySQL n'existe plus ! En revanche, PostGreSQL adapte les ressources du système et sert les pages demandées de plus en plus vite… incroyable non ?

Aujourd'hui aux USA, un mouvement commence à s'emparer du NET et concerne le free SGBDR. Les déçus de MySQL se comptent en nombre et passent pour une grande majorité à de vrai SGBDR comme PostGreSQL, FireBird ou d'autres systèmes moins connus comme Ocelot ou SAPdb. Ce mouvement a déjà atteint le marché allemand.

Un tel mouvement commence à s'amorcer aussi en France. Les sociétés qui gravitent autour de ce marché présentent des ressources notamment sur PostGreSQL en français, alors que ce dernier était encore confidentiel dans la langue de Molière. C'est ainsi que l'éditeur Campus Press, a traduit un ouvrage très intéressant sur PostGreSQL, dû à la plume d'auteurs autrichiens !
Le seul inconvénient de PostGreSQL est qu'il n'offre pas de procédures stockées. Mais il est pourvu des mécanismes d'intégrité référentielle, des fonctions utilisateurs, de déclencheurs, ce qui en fait un des meilleurs choix dans le cadre d'une architecture n-tiers dans laquelle les processus métier sont déportés vers un serveur d'objet ou d'application.

Nul doute que MySQL a vécu et que PostGreSQL assure désormais la relève !

Cependant MySQL reste un bon choix par sa simplicité pour des sites documentaires dans lesquels un seul utilisateur va aller modifier les données et dont le nombre d'utilisateurs simultanés ne dépasse pas la dizaine.

***

À l'heure où j'écris ces lignes un nouvel article vient semer le trouble en présentant MySQL et ses transactions (version bêta) aussi rapides qu'Oracle 9i et bien plus véloce que MS SQL Server et Sybase ASE http://www.eweek.com/article2/0,3959,293,00.asp le tout sur plateforme Windows 2000 server. Néanmoins on ne sait pas quel niveau d'isolation a été pris en compte pour chaque SGBDR. Par défaut celui d'Oracle est maximum et celui de MS SQL Server est « READ COMMITED ». Notons en outre que la connexion JDBC n'est quand même pas le standard qu'adopte la majorité des développeurs SGBDR aujourd'hui !

En conclusion, un article et un benchmark relativement partiel, mais une affaire à suivre !

10. Quelques remarques de…

DrQ :

Pour ce qui est des résultats de MySQL, ça ne correspond pas exactement à ce que j'avais vu comme tests. MySQL était plus rapide que PostgreSQL avec un petit nombre d'utilisateurs (<10). Bien sûr pour des requêtes simples.

SQLpro :

Les tests comparatifs disponibles jusqu'ici étaient un peu anciens. La version actuelle de PostGreSQL a été nettement améliorée question performances. Cependant, c'est sous Linux que PostGreSQL va le plus vite, car il n'a pas besoin de l'émulation CygWin.

Olivier Nepomiachty :

je ne connais pas PostGreSQL.
La migration est-elle facile ?
Faut-il réécrire le code SQL ?
La migration des tables se fait-elle sans soucis ?

SQLpro :

PostGreSQL est assez facile à aborder si l'on connait SQL et les bases de données. L'excellent bouquin de Campus Press, peut servir de base à un bon apprentissage. De plus le langage procédural de PostGreSQL (PG/SQL) est très proche du Pascal donc de Delphi
PostGreSQL est assez normatif comme MySQL, à mon avis une majorité de requêtes ne devraient pas avoir besoin d'être réécrites. Le reste devrait subir de très légères modifications.
À quelques exceptions près comme le type array qui n'existe pas sous PostGreSQL, il doit être possible de passer de l'un à l'autre. Le mieux étant de disposer d'un User Case comme Power Designor ou autre afin d'effectuer un reverse engineering pour reconstruire une base PostGreSQL à partir d'une base MySQL.

Hachesse :

Il t'a fait quoi MySQL pour que tu lui en veuilles à ce point ? Il t'a mordu ?

SQLpro :

Non, et puis de toute façon je suis vacciné ;-) !!! Simplement je regrette que des produits tout aussi souples et plus contraints que MySQL aient quasiment disparu. Je ne citerais que Paradox dont le format, malgré qu'il soit un SGBDR « fichier » comprenait l'intégrité référentielle et un ersatz de transactionnel (rollback limité à 255 lignes, tables contenant les valeurs avant modification ou suppression).
Mais pour moi, un SGBDR doit au moins posséder l'intégrité référentielle de base, et si l'on veut monter en charge, le transactionnel.

RDM :

Quelques remarques

  • Sous-requêtes : tu n'expliques pas pourquoi une sous-requête peut être plus lente qu'une jointure, c'est-à-dire que pour chaque enregistrement de la requête maitre on exécute la requête détail. C’est un point très important qu'il faut rappeler. C'est pour ça que l'on évite le plus souvent ce genre de requête bien que dans certains cas comme tu le soulignes, il n'y a pas d'autre choix.
  • Tu es clairement attaché à mettre un maximum de choses au niveau du SGBD, alors que j'ai tendance à avoir une approche inverse. Les deux approches sont défendables selon les architectures applicatives que l'on emploie. Je ne vais donc pas lancer le débat là-dessus.
  • Concernant ta conclusion je suis d'accord. Ça fait déjà 3 ans que j'avais remarqué la différence MySQL/PostgreSQL et c'est à ce moment que MySQL était déjà tombé aux oubliettes pour moi. Le seul fait qu'il ne gère pas la concurrence et les transactions est de toute façon pénalisant. Pour le reste (intégrité, stored proc, triggers…) ça peut être moins gênant (avec une architecture Client/serveur 3e génération), mais bon on tombe dans le point précédent

SQLpro :

Il n'est pas évident de dire qu'une sous-requête va être plus lente qu'une jointure. Si la jointure est « naturelle », alors c'est probable, dans tous les autres cas, le phénomène a bien des chances d'être insensible du fait de l'optimiseur. Mais il est vrai qu'une jointure est plus propre qu'une requête. La transformation n'est pas toujours possible hélas !
À mon sens, il ne faut pas « mettre un maximum de choses » côté serveur… Mais le strict nécessaire à mon avis réside dans le respect de l'intégrité des données. Un trigger permettant de faire du formatage de données ne présente pas d'intérêt. En revanche s'il étend l'intégrité relationnelle il est indispensable. Il en est de même des transactions et des procédures stockées. La tendance à tout mettre du côté serveur n'est pas la bonne. D'un autre côté, le client léger n'a pas de réelles possibilités pour traiter les données alors entre l'ajout d'un serveur d'objet (donc une machine de plus) ou la réalisation systématique des traitements en procédures stockées c'est une question de stratégie et de cout qui doit être évaluée pour chaque développement.
Attention cependant aux effets de mode… Les SGBDR existent tels quels depuis maintenant 20 ans… et sont assez performants. L'architecture 3 tiers depuis quelques années. Elle commence à devenir mature avec les composants CORBA, la technologie MIDAS et les nouveaux objets COM dérivés de l'architecture .net de MS. En ce qui concerne le C/S de 3e génération, je dirais… prudence (qui, comme chacun le sait, est mère de sûreté) même si certaines technologies comme XML et en particulier SOAP et les web services, présentent a priori des innovations intelligentes. Mais tout le monde ne développe pas pour le web !

Henry Cesbron Lavau :

À propos du paragraphe 1.7 : […] L'idée de manipuler des transactions depuis un code client (VB, Delphi, Java, C++…)
Pourquoi, depuis quand c'est le langage qui transacte ? C’est une affaire de composants, pas de langage.

SQLpro :

Tu as entièrement raison, mais j'ai voulu faire simple en confondant l'envoi de l'ordre de pilotage de la transaction et la transaction elle-même. Si c'est bien le SGBDR qui transactionne, encore faut-il lui donner les ordres BEGIN, COMMIT / ROLLBACK [TRANSACTION] depuis un code quelconque, qui peut être le code client. Or le client peut parfaitement décider d'aller boire son café entre le BEGIN et le COMMIT, ou pire interrompre son PC !

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   


Histoire hélas véridique due à un bug du service informatique de réservation !!!

Copyright © 2004 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.