BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
INSERT, UPDATE, DELETE TRANSACTION
DANS UNE BD
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
PROBLEME:
PROBLEME:
TRAITEMENT
BD BD
TRAITEMENT
BD BD UNITE SEMANTIQUE
ETAT COHERENT ETAT COHERENT
temps
temps T0 T1
T0 T1
Exemple: Transfert d'argent entre 2 comptes:
UPDATE Compte1
Val = Val -100
UPDATE Compte2
Val = Val + 100
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
Transaction: définition Transaction: Propriétés
Une transaction est une séquence d’actions <a11, a12, ..., a1ni>
Exemple: Atomicité
Début de la transaction
DEBUT TRANSACTION
INSERT INTO compte_1 (…) VALUES (…);
Consistance
INSERT INTO compte_2 (…) VALUES (…);
DELETE FROM comptabilité WHERE num=123; Isolation
UPDATE …….
FIN TRANSACTION ;
Fin de la transaction Durabilité
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
Transaction: Propriétés Transaction: Propriétés
Atomicité Consistance
L'ensemble des opérations d'une transaction apparaît comme une seule opération L'exécution de la transaction fait passer la base de données d'un état consistant à un
atomique autre état consistant
Soit toutes les opérations sont validées ou toutes annulées (tout ou rien)
BD TRANSACTION BD
ETAT COHERENT ETAT COHERENT
temps
T0 T1
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
Transaction: Propriétés Transaction: Propriétés
Isolation Durabilité
Chaque transaction est indépendante des autres transactions concurrentes. C'est la persistance des mises à jour d'une transaction validée.
Sérialisation des transactions. Les effets d'une transaction validée sont durables et permanents, quelques soient les
problèmes logiciels ou matériels, notamment après la fin de la transaction.
Les résultats d'une transaction ne sont visibles aux autres transactions qu'une fois la
transaction validée.
Les concurrences sont parfaitement contrôlées
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
Transaction: primitives de gestion
BEGIN TRANSACTION
SET TRANSACTION
OUVERTURE DE SESSION
COMMIT
DEBUT_DE_TRANSACTION Etat cohérent Etat cohérent
de la BD de la BD
INSERT INTO compte_1 (…) VALUES (…);
……
INSERT INTO compte_2 (…) VALUES (…);
………..
COMMIT
DELETE FROM comptabilité WHERE num=123; ROLBACK
………….. •Validation de la transaction
FIN_DE_TRANSACTION ; •Rend effectives toutes les
mises à jour de la transaction
ROLBACK, ABORT
Annulation de la transaction
Défait toutes les
mises à jour de la transaction
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
SET TRANSACTION READ [ONLY | WRITE]
INSERT INTO compte_1 (…) VALUES (…);
INSERT INTO compte_2 (…) VALUES (…);
TRANSACTION SAVEPOINT <NOM>
DELETE FROM comptabilité WHERE num=123;
UPDATE …….
SOUS SAVEPOINT <NOM>
DELETE FROM
ORACLE [COMMIT | ROLLBACK] ;
Une transaction commence soit à la connexion ou en début de session, soit à la fin d'une transaction
précédente annulée ou validée.
Le début d’une transaction dans Oracle peut être IMPLICITE (pas besoin de SET TRANSACTION ….)
COMMIT VALIDE entièrement la transaction
ROLLBACK ANNULE entièrement la transaction
Les savepoint sont des points de contrôle utilisés dans les transactions pour annuler partiellement l’une d’elles.
Il est possible de définir des Savepoint qui sont des points de contrôle utilisés dans une transaction ainsi on a la
possibilité de faire des annulations partielles de transaction.
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
EXEMPLE 1 : COMMIT et ROOLLBACK SQL> --transaction implicite dans oracle
SQL> insert into trans values (1, 'toto');
SQL> create table trans (
2 id number(5) primary key, 1 ligne créée.
3 nom varchar2(20)
4 ); SQL>
SQL>
Table créée. SQL> select * from trans;
SQL> ID NOM
SQL> ---------- --------------------
SQL> desc trans; 1 toto
Nom NULL ? Type
----------------------------------------- -------- ---------------------------- SQL>
ID NOT NULL NUMBER(5) SQL>
NOM VARCHAR2(20) SQL> rollback;
Annulation (rollback) effectuée.
SQL>
SQL> select * from trans;
aucune ligne sélectionnée
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
SQL> set transaction read only;
SQL>
SQL> insert into trans values (1, 'toto'); Transaction définie.
1 ligne créée. SQL>
SQL>
SQL> commit; SQL> --test d'insertion
SQL>
Validation effectuée. SQL> insert into trans values (4, 'titi');
insert into trans values (4, 'titi')
SQL> *
SQL> ERREUR à la ligne 1 :
SQL> select * from trans; ORA-01456: impossible d'exécuter l'opération insérer/supprimer/modifier dans
une transaction READ ONLY
ID NOM
---------- --------------------
1 toto SQL>
SQL> update trans set nom='zozo' where id=1;
SQL> update trans set nom='zozo' where id=1
*
ERREUR à la ligne 1 :
ORA-01456: impossible d'exécuter l'opération insérer/supprimer/modifier dans
une transaction READ ONLY
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
SQL> set transaction read write; EXEMPLE 2 : SAVEPOINT et ROLLBACK TO
SQL> --test savepoint
Transaction définie.
SQL>
SQL> SQL> select * from trans;
SQL> select * from trans;
aucune ligne sélectionnée
ID NOM
---------- -------------------- SQL>
1 tata
SQL> insert into trans values (1, 'toto');
SQL> update trans set nom= 'zozo' where id=1;
1 ligne créée.
1 ligne mise à jour.
SQL>
SQL> commit; SQL> select * from trans;
Validation effectuée.
ID NOM
SQL> select * from trans; ---------- --------------------
1 toto
ID NOM
---------- -------------------- SQL>
1 zozo SQL> savepoint toto;
Savepoint créé.
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
SQL>
SQL> insert into trans values (2, 'tata'); SQL>
SQL> insert into trans values (3, 'titi');
1 ligne créée.
1 ligne créée.
SQL>
SQL> select * from trans; SQL>
SQL> select * from trans;
ID NOM
---------- -------------------- ID NOM
1 toto ---------- --------------------
2 tata 1 toto
2 tata
SQL> 3 titi
SQL> savepoint tata;
SQL>
Savepoint créé. SQL> savepoint titi;
SQL> Savepoint créé.
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
SQL>
SQL> rollback to titi;
Annulation (rollback) effectuée. SQL> rollback to toto;
SQL> Annulation (rollback) effectuée.
SQL> select * from trans;
SQL>
ID NOM
---------- --------------------
SQL> select * from trans;
1 toto
2 tata ID NOM
3 titi ---------- --------------------
1 toto
SQL> rollback to tata;
SQL> rollback;
Annulation (rollback) effectuée.
Annulation (rollback) effectuée.
SQL>
SQL> select * from trans;
SQL>
ID NOM SQL>
---------- -------------------- SQL> select * from trans;
1 toto
2 tata aucune ligne sélectionnée
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
EXEMPLE 3 : SAVEPOINT et ROLLBACK GLOBAL EXEMPLE 4 : SAVEPOINT et COMMIT GLOBAL
SQL> --test savepoint et rollback et commit; SQL> insert into trans values (1, 'toto');
SQL> insert into trans values (1, 'toto'); 1 ligne créée.
1 ligne créée.
SQL> savepoint toto;
SQL> savepoint toto; Savepoint créé.
Savepoint créé.
SQL> insert into trans values (2, 'tata');
SQL> insert into trans values (2, 'tata'); 1 ligne créée.
1 ligne créée.
SQL> savepoint tata;
SQL> savepoint tata; Savepoint créé.
Savepoint créé.
SQL> insert into trans values (3, 'titi');
SQL> select * from trans; 1 ligne créée.
ID NOM SQL> commit;
---------- -------------------- Validation effectuée.
1 toto
2 tata SQL> select * from trans;
SQL> rollback; ID NOM
Annulation (rollback) effectuée. ---------- --------------------
1 toto
SQL> select * from trans; 2 tata
aucune ligne sélectionnée 3 titi
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
SYNTHESE
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
Partie 1: Transaction et ordre CREATE TABLE ET DROP TABLE
Pour réaliser cette première partie du TP vous devez avoir deux sessions différentes ouvertes sur la même
TRAVAUX
base (connectez vous avec deux SQLPLUS avec le même compte.)
1. créer la table "transa" comme présenté ci-aprés.
CREATE TABLE transa (
ID NUMBER(5) PRIMARY KEY,
PRATIQUES
NOM VARCHAR2(20)
);
2. Considérons les ordres CREATE et DROP. La création et la suppression d'une table sont-elles
transactionnelles ?
Pour vérifier cela, avec vos deux connexions, tentez de créer la table "transa" dans une transaction, et de
vérifier dans l'autre session si vous la voyez .
3. Que constatez-vous ?
4. Exécutez la même tentative avec un DROP.
5. Conclusion ?
ORACLE PL/SQL ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES BASES DE DONNEES RELATIONNELLES
Partie 2 : atomicité d’une transaction courante Partie 2 : atomicité d’une transaction courante
6. Insérez trois ou quatre lignes dans la table transa et les voir; 19. Insérez trois ou quatre lignes dans la table transa et les voir,;
20. Insérez deux ou trois lignes puis faites une sauvegarde partielle de la transaction (SAVEPOINT)
7. Modifiez une ligne, en supprimer une autre, enfin annuler les mises à jour venant d’être
21. Insérez deux ou trois lignes puis faites une sauvegarde partielle de la transaction (SAVEPOINT)
effectuées (en écrivant « ROLLBACK ; »). 22. Faites un ROLLBACK puis vérifiez le contenu votre table. Conclusion?
8. Vérifier le contenu de le contenu de la table et sa structure. 23. Insérez deux ou trois lignes puis faites une sauvegarde partielle de la transaction (SAVEPOINT)
9. Conclusion? 24. Insérez deux ou trois lignes puis faites une sauvegarde partielle de la transaction (SAVEPOINT)
10. Insérer à nouveau trois ou quatre lignes, les modifier et les détruire partiellement, puis 25. Faites un COMMIT puis vérifiez le contenu votre table. Conclusion?
valider (en écrivant « COMMIT ; ») ces mises à jour, 26. Videz votre table par un delete.
11. Faites maintenant un ROLLBACK. Que s’est-il passé ? 27. Insérez deux ou trois lignes puis faites une sauvegarde partielle de la transaction (SAVEPOINT)
12. Maintenant détruire les données de votre table et valider. 28. Insérez deux ou trois lignes puis faites une sauvegarde partielle de la transaction (SAVEPOINT)
29. Faites une annulation partielle de la transaction (ROLLBACK TO …)
13. Insérer à nouveau dans votre table vide trois ou quatre lignes et clore la transaction par un
30. Vérifier le contenu de la table par un SELECT.
EXIT . 31. Faites un COMMIT. Et vérifiez le contenu de la table. Conclusion?
14. Reconnectez vous à SQLPLUS. Que s’est-il passé ? Expliquez
15. Dans votre table, insérez à nouveau deux ou trois lignes dans la table et fermez
brutalement votre session. Partie 3 : transaction et ordre DDL
16. Reconnectez vous à SQLPLUS. Les données saisies ont-elles été préservées ? Expliquez! 32. Videz votre table par un delete.
17. Insérer à nouveau deux ou trois lignes dans la table, puis ajouter une nouvelle colonne à la 33. Insérez deux ou trois lignes.
table et essayer d’annuler les dernières insertions puis faites un DESC de la table. 34. Faites un ALTER TABLE (par exemple, modification du schéma logique en général).
Conclusion. 35. Faites un DESC de la table
36. Vérifiez le contenu de la table. Que constatez-vous? Conclusion??
18. Videz votre table par un delete.
ORACLE PL/SQL ORACLE PL/SQL