SQL – DML – Insert, Update, Delete, Truncate et les transactions

SQL – DML – Insert, Update, Delete, Truncate
et les transactions
Insérer des lignes, modifier des valeurs de colonnes et supprimer
des lignes
Les ordres de création de lignes, de mise à jour des valeurs de colonnes et de suppression de lignes
interviennent au niveau du CONTENU des tables.
Ces ordres mettent en œuvre
•
l’APPLICATION DES CONTRAINTES D’INTEGRITE spécifiés lors de la création des tables
•
les mécanismes de GESTION DE TRANSACTION afin de garantir l’intégrité des données lors
d’accès concurrent aux données.
1-Insérer des lignes dans une table, ordre INSERT INTO
L’ordre INSERT permet l’ajout de lignes dans une table.
A- Insérer une ligne complète à partir de valeurs fixes
Dans ce cas de requête d’insertion, les listes des valeurs sont fournies par des valeurs
constantes ou des expressions contenant des fonctions.
Ces listes de valeurs doivent correspondre à toutes les colonnes de la table, sinon une liste des
noms de colonnes correspondantes doit être précisée.
Syntaxe générale :
INSERT INTO nom_table [(col1, col2, col3, ...)]
VALUES (valeur1, valeur2, valeur3, …)
;
Par exemple : ajouter un membre
INSERT INTO member VALUES (1,'dupont','pierrE','bld
Gambetta' , 'cachan' , '20/10/1975',NULL);
B- Insérer plusieurs lignes à partir d’une requête
Dans ce cas de requête d’insertion, les listes des valeurs sont fournies par une requête SELECT
complète.
Ces listes de valeurs doivent correspondre à toutes les colonnes de la table, sinon une liste des
noms de colonnes correspondantes doit être précisée.
Syntaxe générale :
INSERT INTO nom_table [(col1, col2, col3, ...)]
SELECT nomCol1, nomCol2, nomCol3,...
FROM nom_table1
[WHERE conditions]
[GROUP BY colonnes]
[HAVING conditions]
[ORDER BY colonnes]
;
PATDEZ-2006- Insérer des lignes, modifier des valeurs de colonnes et supprimer des lignes
Page 1/6
SQL – DML – Insert, Update, Delete, Truncate
et les transactions
2-Mettre à jour des valeurs de colonnes, ordre UPDATE
L’ordre UPDATE permet la mise à jour de valeurs de colonnes dans UNE SEULE TABLE selon la
valeur d’une condition exprimée dans la clause WHERE.
ATTENTION :
UN ORDRE DE MISE A JOUR SANS CLAUSE ‘WHERE’ AFFECTE
TOUTE LES LIGNES DE LA TABLE.
Syntaxe générale :
UPDATE nom_table
SET col1 = valeur1 [,col2 = valeur2, col3 = valeur3,...]
[WHERE expression_condition]
;
La condition spécifiée peut être exprimée :
•
Par rapport aux colonnes de la table mise à jour
•
Par rapport à une requête imbriquée indépendante ou corrélée
Mettre à jour (corriger) le prénom d’un membre :
UPDATE membre
SET prenom = ‘john’
WHERE id_memb = 10 ;
Augmenter le tarif des activités (de TOUTES les activités) de 10% :
UPDATE activite
SET tarif = tarif * 1.1;
Augmenter de 10% le prix des oeuvres des artistes nés avant 1900 :
UPDATE t_oeuvre
SET prix = prix * 1.1
WHERE numartiste IN ( SELECT numartiste FROM t_artiste
WHERE anneeNaissance < 1900 )
;
3-Supprimer des lignes d’une table, ordre DELETE
L’ordre DELETE permet la suppression d’une ou plusieurs lignes d’une table selon la valeur
d’une condition.
PATDEZ-2006- Insérer des lignes, modifier des valeurs de colonnes et supprimer des lignes
Page 2/6
SQL – DML – Insert, Update, Delete, Truncate
et les transactions
ATTENTION :
UN ORDRE DE SUPPRESSION SANS CLAUSE ‘WHERE’ SUPPRIME
TOUTE LES LIGNES DE LA TABLE.
DELETE [FROM] nom_table
[WHERE expression_condition]
;
La condition spécifiée peut être exprimée :
•
Par rapport aux colonnes de la table cible de la suppression de lignes
•
Par rapport à une requête imbriquée indépendante ou corrélée
Supprimer les inscriptions du membre de numéro 10 :
DELETE inscrire
WHERE id_memb = 10 ;
4-Vider une table de son contenu, ordre TRUNCATE TABLE
L’ordre TRUNCATE TABLE permet la suppression de TOUTES LES LIGNES D’UNE TABLE sans
conservation de la trace de la suppression (SANS JOURNALISATION).
ATTENTION :
LES SUPPRESSIONS DE LIGNES NE SONT PAS JOURNALISEES
(un ordre ROLLBACK ne permet pas d’annuler cette suppression de lignes)
TRUNCATE TABLE nom_table
;
5-Validation ou abandon des mises à jour effectuées
Les mises à jour demandées au SGBD ne sont effectivement vraiment enregistrées dans le base de
données que lorsque demande leur validation explicitement.
A- Valider les mises à jour effectuées
Permet de confirmer la mise à jour des données dans la base de données (depuis le dernier ordre
COMMIT)
Syntaxe générale :
COMMIT [TRANSACTION];
B- Annuler les mises à jour effectuées
Permet d’annuler la mise à jour des données dans la base de données (depuis le dernier ordre
COMMIT).
Syntaxe générale :
ROLLBACK [TRANSACTION];
PATDEZ-2006- Insérer des lignes, modifier des valeurs de colonnes et supprimer des lignes
Page 3/6
SQL – DML – Insert, Update, Delete, Truncate
et les transactions
6-Gestion des transaction
Les requêtes SQL DML permettent la réalisation de tâches de mises à jour complexes sur les données des
tables.
Dans certains cas, ces mises à jour vont requérir l’exécution de plusieurs requêtes successives afin de
prendre en compte les mises à jour multi tables.
De plus, dans des environnement transactionnels, des mises à jour simultanées peuvent être requises par
différents utilisateurs ou différents applications. Des effets d’entrelacement dans l’exécution de ces lots
de requêtes peuvent apparaître.
A- Les mécanismes d’isolation des transactions : les verrous
Les lectures et écritures d’une transaction doivent être garanties par le SGBD : soit elles se déroulent
toutes correctement soit elles sont toutes abandonnées.
Voici quelques cas d’anomalies possibles qui est indispensable de gérer. T1 et T2 représentent 2
transactions se déroulant simultanément. R représente une opération de lecture, W une opération de mise
à jour.
Cas A- Lecture des données non validées (« dirty read »)
Une transaction (T2) va lire des données écrites par une autre transaction (T1) concurrente non encore
validée
T1
T2
R(A), W(A)
R(A), W(A), commit
R(B), W(B), abort
Cas B- Lecture des données non répétable(« non repeatable read »)
Une transaction (T1) est amenée à relire des données lues précédemment, ces données ayant été modifiées
depuis la lecture initiale par une transaction (T2)
T1
T2
R(A)
R(A), W(A), commit
R(A), W(A), commit
Cas C- Lecture fantômes (« phantom »)
Une transaction (T1) est amenée à relire un ensemble de lignes lues précédemment, des lignes ayant été
ajoutées par une transaction (T2)
T1
T2
R(A)
R(A), W(A), commit
R(A), W(A), commit
SET TRANSACTION ISOLATION LEVEL niveau_isolation
PATDEZ-2006- Insérer des lignes, modifier des valeurs de colonnes et supprimer des lignes
Page 4/6
SQL – DML – Insert, Update, Delete, Truncate
et les transactions
Les degrés d’isolation des transactions :
Niveau d’isolation
READ UNCOMMITED
Permet de lire une ligne même si elle a été
mises à jour par une transaction non validée
READ COMMITED
Empêche la lecture d’une donnée mise à
jour par une autre transaction
REPEATABLE READ
Empêche la mise à jour de données lues
SERIALIZABLE
Empêche toute mise à jour ou insertion si
une transaction est en cours
niveau
0
A
possible
B
possible
C
possible
1
impossible
possible
possible
2
impossible
impossible
possible
3
impossible
impossible
impossible
La pose de verrous sur les lignes lues (ou les pages physiques) va permettre aux autres transaction ayant
besoin de ces pages de savoir qu’elles doivent attendre pour garantir les données lues.
B- Le Deadlock, contrepartie de la pose des verrous
La gestion des transactions met en place des verrous (lock) de lectures de lignes (ou au niveau physique
bloc ou pages de la base) pour empêcher la lecture de lignes en train d’être mises à jour.
Ces verrous sont libérés sur le commit ou le rollback
Ces mécanismes de verrous peuvent néanmoins conduire à une situation de blocage, selon la
terminologie :
Deadlock - verrou mortel - étreinte fatale - interblocage
T1
T2
R(A)
Verrou(A)
R(B)
Verrou(B)
R(B)
Attente libération Verrou(B)
R(A)
Attente libération Verrou(A)
Cette situation de blocage est gérée par les SGBD. A la détection de ces blocages, évènement est
déclenché, celui-ci annulant l’une des 2 transactions en cours.
PATDEZ-2006- Insérer des lignes, modifier des valeurs de colonnes et supprimer des lignes
Page 5/6
SQL – DML – Insert, Update, Delete, Truncate
et les transactions
7-Mécanismes de ‘journalisation’
A- Les journaux pour garantir la reprise après incident
La journalisation regroupe les mécanismes utilisés par les SGBD pour garantir les données qu’ils gèrent.
Un journal central va contenir toutes les mises à jour .
Des journaux de transaction (Quick Before Look, par exemple) vont contenir les images des lignes en
cours de mise à jour (image avant et images après).
En cas d’incident (coupure de courant, défaillance disque, etc.), le SGBDR est capable de vérifier ses
journaux, de détecter les transactions qui n’étaient pas validées et annuler leurs mises à jour afin de
conserver l’intégrité des données de la base.
B- Placement des fichiers journaux et des fichiers de bases de données
Le placement des fichiers relatifs aux journaux et aux fichiers de bases de données font l’objet d’une
attention particulière.
Une répartition physique sur des disques différents permet
•
D’une part de garantir des performances optimales
•
De répartir les risques en cas de perte de disque (les fichiers journaux sont souvent dupliquées sur
plusieurs axes ou disques)
PATDEZ-2006- Insérer des lignes, modifier des valeurs de colonnes et supprimer des lignes
Page 6/6