A quoi ça sert ???

A 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 inutile.
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 annnées pendant lesquelles les bases de données relationnelles n'ont cessés de progresser.

Est-il réellement possible de se passer de tels élément ?
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

Site personnelSite personnel

Liens sociaux

Viadeo Twitter Google Bookmarks ! Facebook Digg del.icio.us Yahoo MyWeb Blinklist Netvouz Reddit Simpy StumbleUpon Bookmarks 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. A quoi servent les transactions ?

Image non disponible

Les transactions sont la clef 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 rapide de la chose, on peut lire l'article de Henri Cesbron Lavau sur le sujet à l'URL:
http://www.developpez.com/hcesbronlavau/Transactions.htm

La norme à 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 grec é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 concourrant. 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 clef 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 place 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 simple 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 ou deux processus concurrent effectue 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 t-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 executée dans un contexte de concurence 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é servit ! C'est encore pire, vous allez entraîner la faillite de votre compagnie en continuant comme ça !

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

1.2. Pourquoi pas des verrous ?

Alors certains dirons, "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 parlé du "verrou mortel, "dealock" en anglais, aussi apellé é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 diagramne du parallélisme des tâches afin de débusquer les points de concurrence sujet à 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 à 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ême 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 une 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 auto validée ou auto annulé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 ais 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'ou 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 si 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 lorsque'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 successive 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 temp 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 si il y en à 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 à 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

11 places ont été volatilisé 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 à mis en place le niveau d'isolation des transactions.

Le tableau ci dessous résume les différents niveaux d'isolation praticables et les anomalies qu'elle 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 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 terme 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 clef (primaire ou étrangères), INSERT : SERIALIZABLE
  • UPDATE sur valeurs courantes, DELETE filtrés sur CLEFS : 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 "base de données documentaires" ou 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 vérouillé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évue l'artillerie lourde : poste sur onduleur on line ou réseau électrique sur alimentation secourue, OS hyper stable, anti virus, 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 carte, 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 terme de sécurité et d'intégrité) soit dans des objets métiers appelés par une 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écessite la pose simultané 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
vérouillé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
vérouillée par T1

A 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 verrrou 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 sujet à l'interblocage et le seul remède est en général de "tuer" un utilisateur (enfin, sa connexion !).

2. A 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 voit le lien logique entre les deux lignes brisée. 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érences aux client 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érence le client supprimé
  • que la référence du client, si elle est modifié, 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 à 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 supression intervient alors que le client est référencé par une commande, la suppression est avortée
 
Sélectionnez

ON DELETE CASCADE
Si une supression 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 clef 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 clef du client intervient alors que ce client est référencé par une commande, la ou les commandes référencée pour ce client voit leur clef é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 clef 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 mise à 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 supression intervient alors que le client est référencé par une commande, les lignes des tables filles référencées par ce client voit la valeur de la clef passer à la valeur par défaut définie lors de la création de la table.
 
Sélectionnez

ON DELETE SET NULL
Si une supression intervient alors que le client est référencé par une commande, la ou les commandes référencées pour ce client voit la valeur de la clef étrangère passer à NULL.
 
Sélectionnez

ON UPDATE SET DEFAULT
Si une mise a jour de la clef 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 voit la valeur de la clef 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 clef 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 voit la valeur de la clef é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 traffic 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 connaître le chiffre d'affaire 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 performant que la norme l'impose en matière de gestion de l'intégrité référentielle. En effet, certains comme ORACLE ou InterBase on 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'une 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 phalacieuse 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...

A 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. A quoi servent les déclencheurs (Triggers) ?

Image non disponible

S'il avait 2 choses à retenir pour constituer un SGBDR au sens relationnel du terme, se 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'évenement, c'est à dire sa remontée depuis le plus grand conteneur jusqu'à l'objet cible ! - au cas ou vous l'aurez oublié, celui qui a créé ObjectPal à 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 pseudo tables 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 tout les prospects dans la table T_CLIENT avec le statut 'P'. Oui, mais, au passage il veut leur donner une clef comptatible avec le CLI_ID et reformater le nom en majuscule...

Or un simple ordre d'insertion basique du SQL va violer la contrainte d'unicité de la clef et rejetter 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'elle 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. A 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 toutes autres tables, alors il aurait fallut adpater votre code et le modifier pour chaque cas, alors que dans le cas d'un trigger, ce code est générique quelque soit l'insertion que vous voulez faire et d'où qu'elle provienne !

Les cas d'utilisation des triggers sont assez large :

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

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

  • les relations 1:n ou n est une limite fixe (par exemple 3)
  • les anti relations (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 3 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 ligne 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 ligne 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 que l'on lie la table NEW à la table des prets 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 à tois, 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. A 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 servait à rien !

4.1. C'est quoi une sous requête ?

Une sous requête est une requête situé à 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 voit 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 pas 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, elle sont souvent moins performantes que des jointures...

L'idée phallacieuse 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. A 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. Ca 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és par des constructions équivalentes à base de jointures ou de sous requêtes. Voir :
Considération 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 coût 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. A quoi servent les procédure 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 coûts induits par les aller et retour des données sur le réseau resterons toujours pénalisant, sans parler qu'en matière de traitement de données tabulaire, 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 obsolescense. 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 contourner 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'un des deux requêtes d'insertion à 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'a part la manipulation des curseur, 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. A 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 celle 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 susceptible de tourner sur différents SGBDR, et si les SGBDR cible implémente 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érement à l'un ou l'autre des SGBDR cible !

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)

A 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 externe 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 chaîne 
// 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 lettre majuscules non diacritqiues,
-- 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'un 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. A 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 survenue dans telle ou telle table. La journalisation est en principe un mécanismes strictement interne au SGBDR et donc réservé à son seul usage. Il n'est donc pas lisible pour un utilisateur, fût-il administrateur de la base de données ou du serveur.

8.2. Comment ça marche ?

Je n'entrerais 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 relatif à 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 modifications 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 mise à 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 modifications n'ont pas été toutes calculées et inscrite, 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 modifications 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 multi utilisateur 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 pseudo rapidité 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é inscrit 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 tout 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

A 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 pseudo rapidité de MySQL ?

Je ne donnerais 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.
A 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à ou le bât blesse, c'est qu'a plus de 20 utilisateurs simultané 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ésente 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 plumes 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 archtecture n-tiers dans laquelle les procesus métiers 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é ne dépasse pas la dizaine.

***

A l'heure ou j'écris ces lignes un nouvel article vient semer le trouble en présentant MySQL et ses transactions (version béta) aussi rapide 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). Biensur pour des requêtes simples.

SQLpro :

Les test comparatifs disponibles jusqi'ici était un peu ancien. 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 priche 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.
A 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'as fait quoi MySQL pour que tu lui en veuille a ce point? Il t'as mourdu?

SQLpro :

Non, et puis de toute façon je suis vacciné ;-) !!! Simplement je regrette que des produits tout aussi souple et plus contraints que MySQL aient quasiment disparus. Je ne citerais que Paradox dont le format, malgré qu'il soit un SGBDR "fichier" comprenanit 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 etre plus lente qu'une jointure, c'est à dire que pour chque enregistrement de la requête maitre on excute la requete détail. c'est un point très important qu'il faut rappeler. C'est pur ca 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é a mettre un maximum de chose au niveau du SGBD, alors que j'ai tendance à avoir une approche inverse. les 2 approches sont défendables selon les architectures applicatives que l'on emploie. Je ne vais donc pas lancer le débat la dessus.
  • Concernant ta conclusion je suis d'accord. ca fait deja 3 ans que j'avais remarqué la différénce MySQL/PostgreSQL et c'est à ce moment que MySQL était déjà tombé au oubliette pour moi. Le seul fait qu'il ne gère pas la concurrence et les transactions et de toutes facon pénalisant. pour le reste (intégrité, stored proc, triggers, ...) ca peut etre moins génant (avec une architecture Client/serveur 3eme genration) 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 à 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 !
A mon sens, il ne faut pas "mettre un maximum de chose" 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élles 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 coût 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'archictecture 3 tiers depuis quelques années. Elle commence a devenir mature avec les composants CORBA, la technologie MIDAS et les nouveaux objet COM dérivés de l'architecture .net de MS. En ce qui concerne le C/S de 3eme 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 à priori des innovations intelligentes. Mais tout le monde ne développe pas pour le web !

Henry Cesbron Lavau :

A 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 onner 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 !

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF


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 et 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.