Voici quelques définitions en usage dans l'univers des SGBDR et du SQL

SQLPro

Deux grandes techniques de bases de données s'affrontent : celle à base de fichiers plats structurés nécessitant un moteur sur chaque poste (middleware ou database engine) et celle à base de serveur de données. Nous allons décrire ces différents modèles et dans quelle condition il convient de passer de l'un à l'autre… ♪

- Titre : Voici quelques définitions en usage dans l'univers des SGBDR et du SQL

- Auteur : SQLPro

- Parution : 23 janvier 2003

- Licence : Copyright ® 2003 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.

Sommaire

- 1. Introduction

- 2. Le mode de verrouillage

- 2-1. Verrouillage pessimiste

- 2-2. Verrouillage optimiste

- 2-3. Discussion sur ces différents modes de verrouillage

- 3. Le SQL en C/S

- 3-1. Exécution du SQL

- 3-2. Le transactionnel

- 4. Les triggers, spécialités du C/S

- 5. Les procédures stockées, régals du C/S

- 6. Le journal, particularité des SGBD C/S

- 7. Conclusion

1 - Introduction

À quel moment faut-il passer d'un SGBD micro de type serveur de fichier comme dBase, Paradox, FoxPro ou Access, à un poids lourd du client/serveur comme Oracle, Sybase, SQL Server, Informix ou DB2 ?

Nous supposons que le lecteur du présent article maîtrise un tantinet le monde des bases de données. Nous supposons qu'il sait ce qu'est une requête (même s'il n'en a jamais écrit), ce qu'est un accès concurrentiel aux données (deux utilisateurs voulant modifier la même donnée par exemple), et qu'il est familiarisé avec la notion de table, colonne (champ) et enregistrement. De plus, nous exigeons qu'il sache le minimum au sujet des systèmes d'exploitation, à savoir ce qu'est un fichier, un octet, un poste client, un serveur et un réseau…

Posons alors la question suivante :
Quelle différence y a-t-il entre un SGBD à base de fichiers et un SGBD en C/S ?

En fait, il existe deux différences fondamentales entre les deux systèmes. L'ensemble des techniques du C/S est dédié à minimiser le trafic des données sur un réseau et assurer une plus grande intégrité lors du traitement des données…

Pour y parvenir, le C/S utilise plusieurs techniques particulières, l'une concerne le mode de verrouillage lors des concurrences d'accès, d'autres les modes de traitement des données. Nous allons analyser l'ensemble de ces techniques et prendre quelques exemples concrets afin de rendre tangibles nos propos.

Notons d'ores et déjà une différence fondamentale : alors que dans un système à base de fichiers, c'est chaque poste de travail qui traite localement les données, dans un SGBD C/S, tous les traitements sont, en principe, effectués sur le serveur par le biais de requêtes.

2 - Le mode de verrouillage

Le mode de verrouillage est une fonction essentielle de la base de données. À ce jour, il n'existe que deux modes distincts : le verrouillage « optimiste » et le verrouillage « pessimiste ».

Dans la vie courante il est possible que plusieurs personnes lisent, regardent, écoutent, la même chose en même temps (journaux, radio, télévision…) mais il est impossible que plusieurs personnes créent la même information simultanément. Par exemple il est impossible à deux écrivains de tenir le même stylo afin d'écrire le même roman, comme il est impossible à deux speakers de parler en même temps des mêmes choses sans que cela ne devienne cacophonique.
Bref, l'écriture de l'information est un acte solitaire, mais sa lecture peut être accomplie par de nombreuses personnes simultanément.

Dans l'univers des bases de données, le problème est le même.
Il s'agit donc de prévoir un mécanisme capable de gérer un accès simultané en lecture à tous les utilisateurs, et d'empêcher plusieurs utilisateurs de créer, de modifier ou de supprimer la même donnée.

Pour résoudre ce problème, les chercheurs en algorithmique ont inventé les modes de verrouillage pessimiste et optimiste que nous allons maintenant détailler…

2-1 - Verrouillage pessimiste

Lorsqu'un utilisateur désire modifier une donnée, le SGBD tente de poser un verrou en écriture. Soit l'opération est effective (et donc le verrou est posé), soit l'opération échoue parce qu'un autre utilisateur a déjà posé un verrou. Si l'opération est positive, pendant toute la durée de la modification, le verrou est actif pour cet utilisateur jusqu'à ce que l'utilisateur valide ses modifications ou les annule. Dans ces deux cas, le verrou est libéré.

2-2 - Verrouillage optimiste

Lorsqu'un utilisateur désire modifier une donnée, le SGBD relève le numéro de version de l'enregistrement et ne pose aucun verrou. Plusieurs utilisateurs peuvent faire de même sans aucune limite. Le premier utilisateur qui valide ses modifications, incrémente le numéro de version de l'enregistrement. Tous les autres se voient refuser leur modification, car le numéro de version qu'ils ont relevé au début de la session de modification des données n'est pas le même que celui qu'ils peuvent lire à la fin de cette session.

2-3 - Discussion sur ces différents modes de verrouillage

Lorsque l'on utilise la technique du verrouillage pessimiste, il est bon de ne pas « jeter » l'utilisateur dès qu'il tente de poser un verrou pour modifier les données. Dans ce cas, le SGBD effectue une boucle d'attente en tentant de poser le verrou pendant n secondes. S'il échoue, il en informe l'utilisateur et lui recommande de renouveler sa tentative un peu plus tard…
C'est comme cela que fonctionne la quasi-totalité des SGBD à base de fichiers (dBase, Paradox, FoxPro, Access…).

Il semble a priori évident que le mode de verrouillage pessimiste devrait être préféré dans tous les cas… C'est cependant oublier le principe de base du C/S : minimiser la charge du réseau…

Or effectuer une boucle d'attente qui interroge un fichier, monopolise dramatiquement le réseau. C'est pourquoi la quasi-totalité des SGBD C/S fonctionne en verrouillage optimiste, car dans ce mode, l'échange de données entre la base et le poste client est rapide et bref…

De plus si nous prenons en compte l'intégrité référentielle et que la structure de la base permet de modifier des clefs existantes en répercutant leurs valeurs dans l'ensemble des tables filles, alors dans le cas d'un SGBD fichier ont peut arriver à obtenir un trafic réseau intense tandis que dans le cas du client serveur, l'opération sera menée à bien au sein du SGBD donc sans aucune influence sur la charge du réseau.

3 - Le SQL en C/S

Il n'y a aucune différence entre un SQL disponible pour un SGBD de type fichier et un SGBD de type C/S : les ordres SQL sont aussi pauvres ou aussi riches en fonction de la qualité de l'éditeur du SGBD… En revanche il existe deux différences fondamentales en matière d'exécution des ordres SQL et de contrôle des transactions…

3-1 - Exécution du SQL

En revanche, il existe une différence fondamentale en matière d'exécution de ces requêtes. En fait dans le cadre d'un SGBD de type fichier, celui-ci exécute toujours la requête de manière locale tandis que dans un SGBD C/S ce dernier exécute la requête sur le serveur.
La différence est fondamentale en matière de congestion du trafic réseau… et il ne faut pas oublier qu'à la base, que se soit en fichier ou en C/S, tous les accès aux données se font par le biais de requêtes…

Pour comprendre la différence, voici un exemple simple. Une table de nom CLIENT contient 50 000 enregistrements de 200 octets chacun. La requête est la suivante :

SELECT * FROM CLIENT WHERE (CLI_NAME like ‘%CLINTON%')

Dans un SGBD de type fichier, voici le flot de données : (1)

- le PC rapatrie depuis le serveur le fichier contenant la table CLIENT (soit au minimum 50 000 x 200 octets (sans compter les index et le reste) ;

- le PC traite localement la requête ;

- le PC affiche le résultat (soit par exemple 5 lignes de 200 octets).

Dans un SGBD de type C/S, voici le flot de données :

- le PC envoie au serveur le fichier texte de la requête (soit environ 50 octets) ;

- le serveur exécute la requête sur la table CLIENT ;

- le serveur renvoie au PC les données répondant à la requête (soit nos 5 lignes de 200 octets) ;

- le PC affiche le résultat.

Bilan de ces opérations :
SGBD fichier : environ 10 001 000 octets ont été véhiculés sur le réseau ;
SGBD C/S : environ 1050 octets ont été véhiculés sur le réseau.

Dans ce cas, le rapport est de près de 1/10000 en faveur du C/S…

3-2 - Le transactionnel

De plus les SGBD C/S disposent d'un mécanisme qui n'est généralement pas disponible dans les SGBD fichier, le transactionnel.

Le transactionnel permet de regrouper de multiples commandes SQL comme s'il s'agissait d'une instruction de type atomique, c'est-à-dire s'exécutant en totalité ou aucunement (comme la division de deux nombres sur le microprocesseur de l'ordinateur).
Cela suppose de donner un point de départ à la transaction un point d'arrivée et de contrôler si l'ensemble des commandes SQL ont été réalisées avec succès. Dans le cas où l'une quelconque des commandes SQL n'a pas été traitée correctement, alors le point final de la transaction permet de revenir en arrière sur l'ensemble des opérations déjà effectuées. La transaction se présente alors comme une seule instruction de type ‘tout ou rien'.
Cela n'est possible que sur des serveurs capables d'enregistrer l'ensemble des insertions, modifications et suppressions effectuées sur les enregistrements des tables d'une base (historisation des modifications).

Un exemple va nous montrer comment se présente une session de transaction.
Supposons que nous disposons d'une base de données centralisée sur un serveur SQL en C/S permettant de collecter les données des clients et des commandes. Supposons aussi que les commerciaux sont dotés de PC portables et que, chaque semaine, ils viennent au siège de la société afin de remplir la base centrale des nouveaux clients et des commandes qu'ils ont saisies sur leur portable.
Pour réaliser ce recollement d'information, l'informaticien a réalisé la transaction suivante :

BEGIN TRANSACTION
INSERT INTO CENTRAL_CLIENT (NO_CLI, NOM_CLI, ADRESSE_CLI)
SELECT PC.NO_CLI, PC.NOM_CLI, PC.ADRESSE_CLI
FROM PORTABLE_CLIENT PC
INSERT INTO CENTRAL_COMMANDE (NO_CLI, NO_COM, MONTANT_COM)
SELECT PC.NO_CLI, PC.NO_COM, PC.MONTANT_COM
FROM PORTABLE_COMMANDE PC
IF ERROR
THEN
ROLLBACK
ELSE
COMMIT
ENDIF

S'il n'avait fait qu'enchaîner les deux requêtes, il aurait été possible que les clients soient insérés sans que les commandes le soient…
Pire, le système aurait pu tenter d'insérer les commandes en n'ayant pas inséré les clients.
De plus, les deux requêtes auraient pu être interverties… Mais là tous les bons SGBD, qu'ils soient C/S ou fichier, savent gérer les intégrités référentielles et auraient empêché la moindre insertion dans la table des commandes sans avoir la référence du client préalablement insérée dans la table des clients…

4 - Les triggers, spécialités du C/S

Tout le monde connaît maintenant la programmation événementielle. Son principe est simple : faire correspondre à un événement (l'ouverture d'un fichier, l'appui sur un bouton, l'arrivée d'une valeur dans un champ), le déclenchement d'une séquence de code.

Les triggers sont à la base de données ce qu'est la programmation événementielle à un environnement graphique : ils font correspondre aux événements d'un SGBD du code que tout développeur est capable d'écrire.

Prenons un exemple afin de décrire plus en avant nos propos. Supposons que nous avons une table CLIENT (parent) liée à 3 tables filles (FACT, LIGNFACT, BONLIV) et que nous voudrions l'effacement des données dans les tables filles lorsque l'utilisateur demande la suppression d'un enregistrement dans la table CLIENT.
Bien entendu, il est possible d'écrire un tel traitement dans la plupart des langages de développement possédant des outils d'accès aux bases de données, mais dans ce cas, le code s'exécute sur le poste client, et il ne faut pas oublier de l'appeler dans tous les éléments de code qui effectuent la suppression dans la table mère…

Dans une base de données C/S, avec un trigger, le traitement s'effectue directement sur le serveur et il ne nécessite aucun appel à aucune fonction d'aucune sorte. C'est en effet la base elle-même qui va se rendre compte que l'événement de suppression de la table client nécessite un traitement et va l'exécuter.

Dans un tel exemple, le code pourrait être le suivant :

CREATE TRIGGER DELETE_CASCADE_CLIENT (CLI_ID_CLI INTEGER) BOOLEAN
BEFORE DELETE
DELETE FROM FACT WHERE (ID_CLI = CLI_ID_CLI)
DELETE FROM LIGNFACT WHERE (ID_CLI = CLI_ID_CLI)
DELETE FROM BONLIV WHERE (ID_CLI = CLI_ID_CLI)
IF ERROR
THEN
RETURN FALSE
ENDIF
RETURN TRUE

Ce trigger comportant différentes commandes SQL devra être appelé au sein d'une transaction plus globale incluant la suppression de l'enregistrement dans la table mère. Les commandes SQL de ce trigger réclament la suppression dans les tables FACT, LIGNFACT et BONLIV de tous les enregistrements faisant référence à l'identifiant du client transmis en paramètre. Si au moins, une des requêtes échoue, le trigger renvoie False à la procédure l'ayant appelée et l'ensemble des requêtes sera annulé. Sinon, si tout va bien, le trigger renvoie True et l'ensemble des modifications sera rendu effectif par une validation (COMMIT).

Ce trigger est stocké au sein même de la définition de la base de données et s'exécute, sur le serveur, à chaque fois qu'un ordre de suppression arrive dans la table CLIENT. Il n'y a donc aucun trafic sur le réseau pour effectuer ce traitement…

5 - Les procédures stockées, régals du C/S

En complément aux triggers, les procédures stockées permettent de stocker un traitement (opérant en général uniquement sur les données de la base) au sein même de la base de données, qui s'exécutera, par appel de la procédure au moment voulu, sur le serveur.

Exemple : une application réclame un traitement de modification des prix de vente des produits en fonction de la variation d'un certain nombre d'indices boursiers. Cette opération ne peut être déclenchée que manuellement par le responsable du service commercial…
Le code pourrait en être le suivant :

CREATE PROCEDURE AUGMENTATION_PRIX
VAR
INDICE REAL
ENDVAR
; calcul du nouvel indice d'augmentation
; moyenne de la différence entre les anciens et les nouveaux indices
SELECT AVG(DERNIER_INDICE - INDICE_PRECEDENT) AS :INDICE
FROM INDICE_BOURSE
; teste s'il s'agit bien d'une augmentation et non d'une diminution
; autrement dit un indice positif et non négatif
IF INDICE < 0
THEN
RETURN
ENDIF
; effectue l'augmentation
UPDATE PRODUITS
SET PRIX_VENTE_HT = (PRIX_VENTE_HT * (1 + (:INDICE)))
IF ERROR
THEN
RETURN FALSE
ENDIF
RETURN TRUE

Dès lors cette procédure peut être appelée n'importe quand, par n'importe quel poste client… Elle s'exécutera sur le serveur et ne nécessitera pratiquement aucun trafic réseau.
On peut aussi imaginer qu'elle soit couplée avec un trigger ou encore exécutée à une heure différée, par exemple la nuit afin de ne pas ralentir les accès aux données plus fréquents en général dans la journée…

6 - Le journal, particularité des SGBD C/S

En plus des triggers et des procédures stockées, la plupart des SGBD C/S proposent d'historiser la « vie » des données, dans ce que l'on appelle un journal des transactions.
Celui-ci conserve une trace temporelle de toutes les insertions, suppressions, modifications intervenues dans la base de données depuis son origine.
Ce journal permet de revenir en arrière de manière partielle sur les données. L'intérêt essentiel réside dans le fait de pouvoir, en cas de crash, revenir à un état des données satisfaisant.

Reprenons l'exemple de transaction vu au paragraphe 2.2.
Pour assurer correctement la transaction constituée des 2 requêtes de mise à jour, le SGBD effectue en séquence les opérations suivantes :

1. Écrit dans le journal une étiquette permettant de repérer le début de la transaction (point de synchronisation) ;

2. Requêtes 1 : copie les données des enregistrements avant modification dans le journal ;

3. Requêtes 1 : copie les données des enregistrements après modification dans le journal ;

4. Requêtes 2 : copie les données des enregistrements avant modification dans le journal ;

5. Requêtes 2 : copie les données des enregistrements après modification dans le journal ;

6. Écrit dans le journal une étiquette indiquant que la transaction a été un succès ou un échec ;

7. Répercute les modifications des données dans les tables ;

8. Écrit dans le journal une étiquette indiquant la fin de la transaction.

De cette manière le système est capable en cas de panne survenant au cours d'une transaction de revenir dans un état stable et cohérent des données, afin d'assurer l'intégrité et donc l'atomicité de la transaction.
Pour revenir à cet état stable, le système lit le journal en débutant par la fin, et reprend toutes les transactions qui ont été un succès, sans que la mise à jour physique des données ait été effectuée. (2)(3)

7 - Conclusion

Peut-on accéder aux données d'une base en s'affranchissant des requêtes ?

Un des points négatifs des systèmes C/S est que tous les traitements portant sur les données de la base doivent être, à un moment ou un autre, effectués par des requêtes. En revanche dans les systèmes à base de fichiers il est souvent possible d'accéder directement aux données en s'affranchissant des requêtes, et de procéder à un traitement en utilisant par exemple une lecture séquentielle des enregistrements d'une table.
En général les SGBD de type fichiers proposent des outils (L4G, composants…) permettant de réaliser ce genre de traitement en n'ayant à aucun moment recours aux requêtes. Dans ce cas il est souhaitable de bien connaître le format interne de la base et ses particularités .
Ce type d'accès s'avère intéressant et très souple dans le cas de bases de données de faible volume. En revanche il ne permet pas la mise à jour en une seule requête ou transaction de plusieurs tables… Autre avantage : il est indépendant de la qualité du SQL fourni. Dans le cas de « middleware » comme le moteur BDE d'Inprise (ex Borland) il permet d'unifier les traitements quelles que soient la base de données sous-jacente et sa plate-forme. On peut ainsi, théoriquement, changer de SGBD sans avoir à réécrire une seule ligne de code de l'application…
Dans le cas des SGBD C/S il est quand même possible d'effectuer des traitements locaux dans le langage de programmation en utilisant la technique des « cursor » (curseur) : le curseur est un ordre SQL qui permet de tamponner les données d'une table en mémoire et d'accéder aux données par le biais de variables pour y effectuer un traitement.

Est-il possible de contourner le mode de verrouillage optimiste, afin de le rendre pessimiste ?

Bien entendu oui. Mais le faire systématiquement serait une hérésie, notamment dans le cas d'une base de données dont le volume des transactions et le taux de mise à jour des données serait très important : des blocages intempestifs s'y produiraient fréquemment et pourraient même conduire à la paralysie du système tout entier.
Cependant réaliser un verrouillage pessimiste est d'une grande simplicité : il suffit de créer au sein de la base de données une table de sémaphore contenant le nom de l'utilisateur, le nom de la table, les références à l'enregistrement à verrouiller (par exemple son ‘ROW_ID' si le serveur est capable de le fournir). Avant toute mise à jour ou suppression il suffit de consulter la table de sémaphore, et si l'enregistrement n'y est pas référencé de l'y insérer, de procéder à la modification, puis de le supprimer de la table de sémaphore. Néanmoins il faut se prémunir de plusieurs petits problèmes sous-jacents : par exemple comment supprimer les verrous devenus obsolètes ? Cela peut arriver, notamment lorsqu'un utilisateur a commencé une modification et que son poste client a été victime d'une panne matérielle le coupant du réseau…

Le SQL est-il un langage normalisé ?

Oui, bien entendu. Il existe une norme de définition du langage SQL (SQL 2 : ISO / ANSI 92). Mais chaque éditeur est libre de se reposer ou non sur la norme. Il en résulte une forte incompatibilité entre les différents éditeurs de SGBD, que l'on peut éventuellement contourner soit en se reposant uniquement sur les éléments communs aux principaux éditeurs (très limité), soit en utilisant un middleware normatif qui dans ce cas permettra d'écrire toutes les requêtes à l'aide du SQL local et assurera sa traduction dans le SQL spécifique au SGBD.
Un des autres avantages de certains middlewares est de proposer d'effectuer des requêtes hétérogènes, c'est-à-dire entre différentes bases de données, de différents SGBD, pouvant même tourner sous différentes plates-formes système. C'est en particulier le cas du moteur BDE d'Inprise (ex Borland).

Quel langage est utilisé pour écrire des procédures stockées et des triggers ?

Malheureusement il n'existe aucun langage commun entre les différents éditeurs. De plus l'évolution des SGBDR vers les technologies de l'objet et du web font évoluer de manière importante les structures des langages des différents éditeurs d'une version à l'autre.
Par exemple Oracle porte désormais son fameux langage PL/SQL dans le monde de l'objet et le dote de modules spécialisés (bibliothèques de fonctions) permettant par exemple d'accéder aux techniques du web ou de faire du pilotage SIG (Systèmes d'Informations Géographiques).
La norme SQL 3 tente de résoudre un certain nombre de ces problèmes.

Quel type de machine est nécessaire pour faire fonctionner un SGBDR C/S ?

Il est nécessaire de se munir d'un serveur puissant, avec un processeur plutôt spécialisé dans le traitement des données de base (texte et nombre) que le graphique ou le son…
La quantité de mémoire nécessaire est évidemment bien plus importante que dans le cadre d'un SGBD fichier (par exemple sous système d'exploitation NT, un SGBDR en C/S nécessite au minimum 128 Mo de RAM même avec un nombre très restreint d'utilisateurs). Dans les deux cas, il faut avoir des disques à accès très rapide (SCSI UW par exemple) et ne pas hésiter à investir dans un contrôleur RAID éventuellement en « hot plug ».

Voici un tableau qui pourra vous aider dans votre choix de passage de l'un à l'autre :

SGBD Fichiers

SGBD Client/Serveur

C/S + moniteur transactionnel, répartition, synchronisation, clustering…

Vitesse du réseau

Rapide de préférence (100 Mo/s)

Normal (10 Mo/s)

Normal ou rapide

Volume des données

Faible (10 - 300 Mo)

Important (250 Mo - 2 Go)

Très importants (500 Mo - 4 To)

Nombres d'utilisateurs

Faible (1 à 25)

Moyen (5 à 250)

Important (50 à 5 000)

Contraintes d'intégrité

Faibles

Fortes

Fortes

Volume des transactions, requêtes

Faible

Moyen

Fort

Tolérance aux pannes

Inexistante

Forte

Très forte

Coût d'achat, déploiement

Faible, inexistants

Importants

Très importants

Coûts d'exploitation

Inexistants

Importants

Très importants

SGBD Fichiers

SGBD Client/Serveur

dBase

Informix

FoxPro

Oracle

Paradox

Sybase

Access

SQL Server

MySQL

DB2

Approach

InterBase

4 D

PostGreSQL

(1)Le reste dépend de comment est constituée la base de données. Dans des SGBD de type fichiers intelligemment pensés, comme Paradox, le découpage de la base en de multiples petits fichiers (données - .DB, mémo - .MB, clef - .PX, index secondaires - .Xnn/.Ynn) minimise le trafic réseau. Ce n'est pas le cas de SGBD mal pensés comme Access qui ne comporte en tout et pour tout qu'un seul monstrueux fichier par base de données et par conséquent nécessite de véhiculer la totalité du fichier sur le réseau pour chaque requête sur chaque poste client…

(2)En particulier les traitements seront grandement facilités si le format des tables repose sur un principe navigationnel, c'est-à-dire que les enregistrements sont toujours présentés dans le même ordre logique, ce qui est le cas par exemple du format de table Paradox.

(3)Un « middleware » en matière de SGBD est un moteur relationnel (SQL ou non) assurant l'interface et le dialogue entre les clients et le serveur. En principe il fournit les services suivants :
- intégration d'un ou plusieurs protocoles de communication (réseau) ;
- standardisation des accès aux données ;
- standardisation du format des données quels que soient le SGBD et la plate-forme sur laquelle tourne le serveur.