Bases de données - e

Développement d’applications
Problèmes relatifs aux BD
Problèmes BD
• Ensemble de problèmes couramment rencontrés lors
du développement d’applications de bases de données
• Il est nécessaire :
– de comprendre les mécanismes au cœur de ces problèmes
– de savoir détecter en pratique les situations pouvant
entraîner ces problèmes
– de connaître les outils appropriés fournis par les SGBD
– de savoir tirer le meilleur parti du SGBD pour les résoudre
Bases de données - Yann Loyer
2
Exemple de situation
• Développement d’une application pour la
gestion de réservations de places de trains:
– les clients effectuent des réservations
– les employés gèrent les informations sur les trains
Résa(client,date,num_train,num_place)
Train(num_train,départ,arrivée,hd,ha,distance,
nb_places_libres, nb_places_total)
Bases de données - Yann Loyer
3
Problème 1 : intégrité
• Une distance ne peut être négative
• Pour un train donné, le nombre de réservations
ajouté au nombre de places libres doit être égal
au nombre total de places
• Un numéro de train doit être unique
 il faut empêcher les données absurdes ou
incohérentes !
Bases de données - Yann Loyer
4
Problème 2 : indépendance des niveaux
• Une fois l’application développée, on s’aperçoit
qu’il faut réorganiser les données
 on souhaite éviter de devoir réécrire l’application
à chaque modification du schéma ou à chaque
réorganisation des données en mémoire!
Bases de données - Yann Loyer
5
Problème 3 : confidentialité
• Un client ne doit pas pouvoir modifier
l’horaire d’un train ou les réservations
d’autres clients
• Un employé doit pouvoir le faire
 n’importe qui ne doit pas pouvoir faire
n’importe quoi !
Bases de données - Yann Loyer
6
Problème 4 : persistance
• Le client effectue sa réservation par
internet, puis se déconnecte :
– sa réservation ne doit pas disparaître, donc doit
être stockée
– Son numéro de carte bancaire doit disparaître
après le paiement, donc ne doit pas être stocké
Bases de données - Yann Loyer
7
Problème 5 : reprise sur panne
1. La demande de réservation et le numéro de
CB sont saisis par le client
2. Le paiement est effectué
3. Une panne de courant survient avant que la
réservation ne soit définitivement stockée
 le client paie une réservation imaginaire !
Bases de données - Yann Loyer
8
Problème 6 : concurrence
1. Il reste une place dans le Paris-Marseille
de 13h
2. Deux clients se connectent en parallèle et
tentent de réserver une place dans ce train
 les deux clients ne doivent pas avoir cette
même place !
Bases de données - Yann Loyer
9
Problème 7 : grandes quantités
• Quantités d’informations >>> mémoire vive
 accès disques : lenteur de l’application !
Bases de données - Yann Loyer
10
Problème 8 : répartition
• Pour effectuer une réservation Paris-Berlin,
votre application doit dialoguer avec
l’application allemande
 tous les problèmes précédents doivent être
gérer en coopération par les deux applications !
Bases de données - Yann Loyer
11
Liste des problèmes BD
1.
2.
3.
4.
5.
6.
7.
8.
Intégrité des données
Indépendance des niveaux
Confidentialité
Persistance
Reprise sur panne
Concurrence
Grandes quantités de données
BD distribuées
Bases de données - Yann Loyer
12
Intégrité des données
• Problème :
– données absurdes
– données incohérentes entre elles
• Objectifs :
– Autoriser uniquements des données intègres
– Restreindre les valeurs autorisées dans les colonnes
• Outils :
– définition de contraintes d’intégrité
– détection et rejet automatiques des mises à jour violant les
contraintes
Bases de données - Yann Loyer
13
Contraintes d’intégrité
• Le SGBD doit permettre à l'utilisateur de définir des
règles (ou contraintes) sur les données.
• Ces contraintes d’intégrité sont spécifiées lors de la
définition du schéma de la BD.
• Elles constituent des assertions qui doivent être
satisfaites à tout moment par le contenu de la base.
• Toute mise à jour entraînant la non-satisfaction d’une
contrainte est rejetée.
• Une contrainte peut être désignée par un nom lors de sa
création
Bases de données - Yann Loyer
14
Types de contraintes courantes
•
•
•
•
•
•
Domaine de variation ou type, ex : l'attribut NOM est un texte (obligatoire)
Non nullité, ex : l'attribut NUM ne peut être nul
Plages de valeurs, ex : l’AGE est compris entre 0 et 150
Unicité, ex : l'attribut NUM est clé de la relation EMPLOYES
Dépendance fonctionnelle, ex : CODE_POSTAL -> VILLE
Dépendance référentielle, ex : tout employé de la base doit être affecté à un
département de la base
• Condition générale sur la relation, ex: la COMMISSION est au plus 2 fois le
SALAIRE
• Contrainte temporelle, ex : Le SALAIRE ne peut pas décroître
• Contrainte avec agrégat, ex : la moyenne des Salaires doit être supérieure à
9000
Bases de données - Yann Loyer
15
Déclaration des contraintes d'intégrité
CREATE TABLE
<nom de table>
( <nomatt1> TYPE [DEFAULT <expression>] <contrainte sur attribut1>
[,<nomatt2> TYPE [DEFAULT <expression>] <contrainte sur attribut2>] …
[<1ère contrainte sur relation> [,<2ème contrainte sur relation>] ...]
)
contrainte sur attribut =
[ NULL | NOT NULL [ CONSTRAINT <nom_contrainte1>] ]
| [ {UNIQUE | PRIMARY KEY} [ CONSTRAINT <nom_contrainte2>] ]
|
[ REFERENCES <relation> [(<attribut>)]
[ CONSTRAINT
<nom_contrainte3>] [ON DELETE CASCADE] ]
| [ CHECK ( <condition sur attribut> ) [ CONSTRAINT <nom_contrainte4>] ]
Bases de données - Yann Loyer
16
Déclaration des contraintes d'intégrité
CREATE TABLE <nom de table>
( <nomatt1> TYPE [DEFAULT <expression>] <contrainte sur attribut1>
[,<nomatt2> TYPE [DEFAULT <expression>] <contrainte sur attribut2>] …
[<1ère contrainte sur relation> [,<2ème contrainte sur relation>] ...]
)
contrainte sur relation =
[{UNIQUE | PRIMARY KEY (<attribut1> [,<attribut2>]…)}
[CONSTRAINT
<nom_contrainte2>]]
| [ FOREIGN KEY (<att1> [,<att2>]...) REFERENCES <relation> [(<att3> [,<att4>]... )]
[CONSTRAINT <nom_contrainte3>] [ON DELETE CASCADE]]
| [ CHECK ( <condition sur relation> ) [CONSTRAINT <nom_contrainte4>] ]
Bases de données - Yann Loyer
17
Exemples de contraintes d’intégrité
CREATE TABLE entraîneurs (
num_entraîneur integer
PRIMARY KEY,
nom_entraîneur varchar(20) NOT NULL,
email_entraîneur varchar(20) UNIQUE ,
tél_entraîneur
number(8) );
CREATE TABLE équipes (
num_équipe integer
PRIMARY KEY,
nom_équipe varchar(20) NOT NULL,
nb_inscrits
integer
CHECK(nb_inscrits >= 0) ,
entraîneur
integer
REFERENCES entraîneurs(num_entraîneur) ON DELETE CASCADE);
Bases de données - Yann Loyer
18
Confidentialité et indépendance
• La résolution des problèmes de confidentialité et
d’indépendance logique nécessitent l’utilisation de
vues
• Nous allons donc étudier le niveau des vues appelé
niveau externe
Bases de données - Yann Loyer
19
Niveau externe
Vision globale d’une BD
Programme
d’application 1
Niveau
externe
Niveau
Conceptuel
Niveau
Interne
vue 1
Programme
d’application n
…
vue n
BD conceptuelle
BD physique
Bases de données - Yann Loyer
21
Niveau externe
Appelé également niveau des vues
• Un groupe d’utilisateurs d’une base de données conceptuelle
peut avoir besoin :
–
–
•
d’une partie seulement des informations de la base de données,
et/ou
de ces informations structurées différemment
i.e. d’une BD conceptuelle différente de la base de départ, mais
dépendant de celle-ci :
1. Tout attribut figurant dans la nouvelle base est aussi dans la base
de départ ou dépend des attributs de celle-ci
2. Les instances de chaque table de la nouvelle base sont calculées à
partir de celle de la base de départ
Bases de données - Yann Loyer
22
Vues
• Toute base de données conceptuelle satisfaisant ces deux
conditions est une vue
Exemple : S = {E(emp,dep);M(dep,mgr);S(emp,sal)}
Vue V :
• tables = {EM(emp,mgr);BS(emp,sal)}
• Calcul des instances :
– emp,mgr(E
M) pour les instances de EM
– sal<1000 (S) pour les instances de BS
Bases de données - Yann Loyer
23
Vues
• Chaque table d’une vue est entièrement déterminée par
la donnée d’une expression nommée T = e, où T est une
table et e une expression relationnelle sur la base de
départ
– sch(T) = sch(e)
– Les instances de T sont calculées par e
Exemple : définition de la vue :
– EM = emp,mgr(E
– BS = sal<1000 (S)
M)
Bases de données - Yann Loyer
24
Définition de vue
• Tout ensemble d’expressions nommées de
la forme T = e sur le schéma S de BD est
une vue sur S
• Vue : BD conceptuelle plus abstraite
– les données de la vue sont construites à partir
de celles de la BD conceptuelle mais peuvent
ne pas être présentes dans celle-ci
– les données de la vue n’ont pas d’existence
indépendante de celles de la BD conceptuelle
Bases de données - Yann Loyer
25
Gestion des vues
•
Une vue devrait pouvoir être interrogée et mise
à jour comme n’importe quelle BD conceptuelle,
mais cela dépend de l’implémentation choisie :
1. Vues virtuelles
2. Vues matérialisées
Bases de données - Yann Loyer
26
Vues virtuelles
• Vue virtuelle
– les relations de la vue ne sont pas stockées
– seule sa définition est stockée
– le SGBD doit traduire les requêtes et mises à jour sur la vue en
requêtes et mises à jour sur la BD conceptuelle
• Traduction des requêtes
– Chaque table de la vue dans la requête est remplacée par
l’expression relationnelle qui la traduit (exemple : emp(BS)
remplacée par emp(sal<1000 (S)) )
• Traduction des mises à jour
– Problèmes dès que la vue est définie à partir de plusieurs tables
Bases de données - Yann Loyer
27
Vues matérialisées
• Stockées physiquement (ex: entrepôts de données)
• Les requêtes sont évaluées sur la vue
• Nécessité de propager les mises à jour effectuées
sur la base au niveau des vues pour le maintien de
la cohérence
Bases de données - Yann Loyer
28
Utilisation des vues
• Au niveau de l’utilisateur (indépendance logique) :
– Indépendance logique (protection des programmes
d’application contre les modifications du schéma)
– Exemple : le remplacement de E et M par la table
EDM(emp,dep,mgr) n’implique pas la réécriture des
programmes définis sur EM
• Au niveau du système (confidentialité) :
– Protection des données (exemple : BS)
Bases de données - Yann Loyer
29
Les vues en SQL
Création d'une vue
CREATE VIEW <nom de vue> [(att1[, att2] ...)]
AS <REQUÊTE>
Suppression d'une vue
DROP VIEW <nom de vue>
Bases de données - Yann Loyer
30
Confidentialité
• Principes :
– objectifs :
• protéger les données d’accès intempestifs
• autoriser certains accès
– outils :
• déclaration de l’utilisateur à la connexion
• catégories de pouvoir (mises à jour du schéma, mises à jour
des données, consultation)
• autorisations locales à chaque table
• vues
Bases de données - Yann Loyer
31
Confidentialité
Commande SQL pour donner des droits :
GRANT privilèges ON table (ou vue) TO user
[WITH GRANT OPTION]
avec :
– privilèges = ALL ou
{select,insert,update,delete,alter,index}
– table = train, ou
Félix.train (pour la table d’un autre utilisateur Félix)
– utilisateur = Thérèse, ou
PUBLIC (pour tous les utilisateurs)
– with grant option : possibilité de transmettre ces droits
Bases de données - Yann Loyer
32
Confidentialité
Commande SQL pour supprimer des droits :
REVOKE privilèges ON table (ou vue) FROM user
– Si l’option « with grant option » a été utilisée alors
les droits sont révoquées en cascade
Bases de données - Yann Loyer
33
Suite des problèmes BD
• La résolution des trois problèmes suivants (persistance,
reprise sur panne, concurrence) nécessite de manipuler
les données sous le contrôle de transactions
• Une transaction est une suite d’ordres SGBD tels que
tous sont exécutés ou aucun ne l’est
– début de transaction : premier ordre qui suit la connexion au
serveur ou la fin d’une transaction
– fin de transaction :
• annulation, ou
• confirmation (la transaction est alors dite validée)
Bases de données - Yann Loyer
34
Confirmations
• Explicites : commit
• Implicites (Oracle) :
–
–
–
–
commande de déconnexion en mode interactif
tout ordre de mise à jour du schéma (create,drop,alter…)
Commande « grant »
toute mise à jour des données en mode de confirmation
automatique (autocommit on)
• Effet : confirme toutes les mises à jour depuis le
début de la transactions (i.e. depuis la dernière
confirmation ou annulation)
Bases de données - Yann Loyer
35
Annulations
• Explicites : rollback
• Implicites : déconnexion anormale (autre
que « exit »)
• Effet : annule toutes les mises à jour depuis
le début de la transactions (i.e. depuis la
dernière confirmation ou annulation)
Bases de données - Yann Loyer
36
Remarques sur les transactions
• Au cours de la transaction, existence d’une « table
virtuelle locale » différente de la table visible par
tous jusqu’à confirmation des mises à jour
• Si un ordre échoue sans faire échouer le
programme (ex : insertion dans des colonnes
inconnues), alors ses éventuels effets sont annulés,
mais pas ceux des autres ordres de la transaction
Bases de données - Yann Loyer
37
Persistance
• Certaines mises à jour sont provisoires, d’autres
doivent persister
• En fin de session, une donnée persiste si et
seulement si :
– elle est dans une table
– une confirmation a eu lieu
• instruction de confirmation : commit
• instruction de annulation : rollback
• Une mise à jour persiste ssi elle fait partie d’une
« transaction validée »
Bases de données - Yann Loyer
38
Reprise sur panne
• Exemple de situation :
– données : Thérèse a un compte à la BF
Félix a un compte à la BF
– action : Thérèse doit verser 1000 euros à Félix
– contraintes : les virements internes préservent le
montant total de la BF
– programme implémentant l’action :
begin
compte(Thérèse) – = 1000;
compte(Félix) + = 1000;
end
Bases de données - Yann Loyer
39
Reprise sur panne
•
Exemple de situation (suite) :
–
déroulement :
1. compte(Thérèse) – = 1000;
2. panne
–
–
problème : la contrainte n’est plus satisfaite
solution : utilisation de transaction (atomicité du programme)
begin
compte(Thérèse) – = 1000;
compte(Félix) + = 1000;
commit;
Bases de données - Yann Loyer
40
Concurrence
Exemple de concurrence
delete T where a=1
update T set a=3 where b=2
Client 1
Client 2
serveur
T
A
1
B
2
Bases de données - Yann Loyer
État final ?
41
Concurrence
Exemple de situation de concurrence:
Réservations
Programme de réservation
Next_libre() = select min(place)
Client
place
from réservations
where client is not null
Pierre
1
= -1 si complet
NULL
2
Réservation(x) = si n=next_libre() != -1
alors update réservation
NULL
3
set client = x
where place = n;
Bases de données - Yann Loyer
42
Situation de concurrence
Temps
Agence 1
Agence 2
Nextlibre()
Nextlibre()
2
2
update réservation
set client = Félix
where place = 2;
update réservation
set client = Thérèse
where place = 2;
Bases de données - Yann Loyer
43
Situation de concurrence
Temps
Agence 1
Agence 2
Nextlibre()
2
Nextlibre()
2
Demande de verrou
Verrou accordé
update réservation
set client = Félix
where place = 2;
Verrou relaché
.
.
.
update réservation
set client = Thérèse
where place = 2;
attente
update réservation
set client = Thérèse where place = 2;
Bases de données - Yann Loyer
44
Situation de concurrence
Temps
Agence 1
Agence 2
Demande de verrou
Verrou accordé
Nextlibre()
2
Nextlibre()
2
update réservation
set client = Félix
where place = 2;
Verrou relaché
.
.
.
update réservation
set client = Thérèse
where place = 2;
attente
update réservation
set client = Thérèse where place = 2;
Bases de données - Yann Loyer
45
Situation de concurrence
Temps
Agence 1
Demande de verrou
Verrou accordé
Nextlibre()
2
update réservation
set client = Félix
where place = 2;
Verrou relaché
Agence 2
Demande de verrou
.
.
.
attente
Verrou accordé
Nextlibre()
3
update réservation
set client = Thérèse where place
= 3;
Bases de données - Yann Loyer
46
Contrôle de concurrence
• Types de verrous :
– sur relations (verrous X - exclusive)
– sur n-uplets (verrous RX – row exclusive)
• Incompatibilités :
– X/X ou X/RX : même table
– RX/RX : n-uplets communs au deux sélections
Bases de données - Yann Loyer
47
Demande de verrous
• Demandes de verrous :
– explicite (lock table T in exclusive mode)
– implicite (update, delete)
• Attribution de verrous
– Si incompatibilité sur les ressources à verrouiller avec
verrous déjà accordés , alors mise en attente
• Relâche de verrous:
– Validation (commit)
– Annulation (rollback)
Bases de données - Yann Loyer
48
Mises à jour concurrentes
1.
Demande de verrou sur les n-uplets satisfaisant la condition de
sélection (where)
2. S’il n’y a pas d’incompatibilité avec les verrous déjà posés,
alors obtention du verrou et exécution de la mise à jour sur la
« table virtuelle locale »
3. Sinon attente de la relâche des verrous incompatibles, puis
• obtention du verrou
• réévaluation de la condition de sélection sur les n-uplets
présélectionnés
• Exécution de la mise à jour sur cette nouvelle sélection
4. Relâche du verrou lors de la validation ou annulation
Bases de données - Yann Loyer
49
Remarques
• Verrouillage de ressources : gêne pour les
utilisateurs, donc à minimiser
• Éviter les demandes de verrous mutuellement
bloquants (deadlocks)
attente
client 1
client 2
attente
Le client 1 attend la relâche d’un verrou du client
2 et inversement
Bases de données - Yann Loyer
50