TP5 -Zoo - Transactions AS

Année Spéciale
2008-2009
OMGL-10
Bases de données
TP n° 5 : Zoo - Transactions
Ce TP illustre l’utilisation des transactions sous Postgres.
Par défaut, en Postgres, chaque commande utilisateur est exécutée dans sa propre transaction
et une validation est lancée implicitement à la fin de la commande (si l'exécution a eu lieu,
sinon une annulation est faite). Donc toutes les commandes que vous avez effectuées lors des
précédents TPs ont été exécutées dans une transaction. Par exemple, l’insertion suivante
constitue implicitement une transaction pour Postgres :
insert into Gardien values (80,'Bill','aa','16/01/1990') ;
Pour initier explicitement une transaction, on utilise le mot clé « BEGIN » : toutes les
opérations utilisateur après « BEGIN » seront exécutées dans une transaction unique jusqu'à
un « COMMIT » ou un « ROLLBACK » explicite.
Exercice 1 : Transaction et Intégrité des données
1. Commit / Rollback
- Commencer une transaction par begin ; et dans cette transaction, essayez de faire les
manipulations ci-dessous :
BEGIN;
INSERT INTO GARDIEN VALUES (80,’Durand’,'aa','16/01/1990');
SELECT * FROM GARDIEN ;
ROLLBACK ;
SELECT * FROM GARDIEN ;
Commenter chaque action! Que fait le serveur Postgres?
- Recommencer une autre transaction, et dans cette transaction, essayez de faire les
manipulations ci-dessous :
BEGIN ;
INSERT INTO GARDIEN VALUES (80,’Durand’,'aa','16/01/1990');
SELECT * FROM GARDIEN;
COMMIT ;
SELECT * FROM GARDIEN;
Commenter chaque action ! Que fait le serveur Postgres ?
- Faites une transaction contenant un begin, puis 3 insertions dans Gardien, suivies d’un
commit, suivi d’une insertion, une mise à jour et une destruction, suivies d’un begin, suivi
d’une sélection complète sur la table, suivi de 2 mises à jour, suivies d’une sélection
complète sur la table, suivie d’un rollback, suivi d’une sélection complète sur la table.
Commenter chaque action! Que fait le serveur Postgres?
2. Intégrité des données
- Recommencer une autre transaction, et dans cette transaction, essayez de faire les
manipulations ci-dessous :

BEGIN ;
INSERT INTO GARDIEN VALUES (null, ‘Dupont’, ‘Grenoble’, ‘25/10/1970’);
SELECT * FROM GARDIEN ;
COMMIT ;
SELECT * FROM GARDIEN ;
Commenter chaque action! Que fait le serveur Postgres?
- Recommencer une autre transaction, et dans cette transaction, essayez de faire les
manipulations ci-dessous :
BEGIN ;
INSERT INTO GARDIEN VALUES (85, ‘Dupont’, ‘Grenoble’, ‘25/10/1970’);
SELECT * FROM GARDIEN where nomg=’Dupont’;
COMMIT ;
SELECT * FROM GARDIEN where nomg=’Dupont’;
Commenter chaque action! Que fait le serveur Postgres?
Exercice 2 : Insertion et modification de lignes par transaction
L’éléphant Kalanag donne naissance à un éléphanteau Toomai. Les animaux de la cage de
Kalanag doivent donc être transférés afin de laisser la cage de Kalanag libre pour la maman et
le bébé…
En une seule transaction :
1) Modifier la cage de tous les animaux qui étaient jusqu’alors dans la cage de Kalanag
(excepté Kalanag).
2) Insérer l’animal « Toomai » dans la table Animal et le mettre dans la cage de Kalanag.
Quel est l’avantage de mettre ces instructions dans une transaction ?
Remarque importante à lire avant de commencer l’exercice 3 :
Contrairement aux systèmes de gestion de base de données traditionnels, Postgres n’utilise
pas de verrous pour gérer les transactions, mais un système de gestion de version. Cela
signifie que chaque transaction qui utilise la base de données ne voit qu’une « photo » des
données (une version). Par défaut dans Postgres, une requête d’une transaction ne voit que les
changements validés avant l'exécution de la requête ainsi que les changements effectués dans
la transaction elle-même. On dit que le mode d’exécution est READ COMMITED.
L’avantage est d’avoir moins de conflit qu’avec la gestion des verrous. Cela permet aux
transactions de ne pas voir de données incohérentes, mais cela ne garantit pas la sérialisabilité.
Pour garantir la sérialisabilité, il est possible de poser explicitement des verrous ou de passer
dans un mode « sérializable ». En mode SERIALIZABLE, les requêtes ne verront que les
changements validés avant que la transaction ne débute.
Exercice 3 : Transactions concurrentes
Pour bien mettre en évidence le fonctionnement des transactions, il est nécessaire d’avoir des
transactions concurrentes. Vous allez donc simuler l’utilisation simultanée de la base de
données de « Zoo » par 2 utilisateurs en vous connectant à votre base dans 2 fenêtres
différentes. Pour éviter des problèmes de droits, c’est le même utilisateur (usera101) qui se
connecte 2 fois.
Copier le fichier trans.sql disponible dans /users/info/pub/1a/zoo
Questions 1 et 2 : Exécuter les requêtes dans l’ordre dans lequel elles sont écrites dans le
fichier trans.sql et dans la fenêtre correspondant à chaque utilisateur. Notez vos conclusions.

Pour les questions suivantes, c’est à vous d’écrire dans le fichier trans.sql les requêtes à
effectuer, en respectant l’ordre chronologique, et en indiquant en commentaires l’utilisateur
concerne ainsi que vos conclusions.
Question 3 : Dans deux transactions parallèles, insérez deux cages différentes (intercalez les
requêtes).
Question 4 : Dans deux transactions parallèles, insérez deux fois la même cage. Que se passe-
t-il lorsque le premier utilisateur valide sa transaction ? lorsqu’il abandonne sa transaction ?
Question 5 : L’insertion d’un nouveau gardien s’accompagne généralement de l’insertion des
cages dont il s’occupe
a) Ecrivez la transaction correspondante.
b) Effectuez cette transaction pour 2 nouveaux gardiens différents insérés en parallèle par
les deux utilisateurs (à vous d’intercaler les requêtes).
c) Regardez si les deux transactions ont été prises en compte.
Question 6 : Il est question de changer les éléphants kalanag et toomai de cage. Suite à
incompréhension, l’utilisateur 1 les transfère dans la cage 5 alors que l’utilisateur 2 les
transfère dans la cage 4.
Que se passe-t-il dans les exécutions suivantes (respecter l’ordre chronologique) :
a) L’utilisateur 1 change kalanag de cage, puis l’utilisateur 2 change kalanag, ensuite
l’utilisateur 1 change toomai, et enfin l’utilisateur 2 change toomai.
Utilisateur 1
Utilisateur 2
BEGIN ;
BEGIN ;
UPDATE Animal SET numc = 5
WHERE noma = ‘kalanag’ ;
UPDATE Animal SET numc = 4
WHERE noma = ‘kalanag’ ;
UPDATE Animal SET numc = 5
WHERE noma = ‘toomai’ ;
UPDATE Animal SET numc = 4
WHERE noma = ‘toomai’ ;
COMMIT;
COMMIT;
b) L’utilisateur 1 change kalanag de cage, puis l’utilisateur 2 change toomai, ensuite
l’utilisateur 1 change toomai, et enfin l’utilisateur 2 change kalanag.
Utilisateur 1
Utilisateur 2
BEGIN ;
BEGIN ;
UPDATE Animal SET numc = 5
WHERE noma = ‘kalanag’ ;
UPDATE Animal SET numc = 4
WHERE noma = ‘toomai’ ;
UPDATE Animal SET numc = 5
WHERE noma = ‘toomai’ ;
UPDATE Animal SET numc = 4
WHERE noma = ‘kalanag’ ;

COMMIT;
COMMIT;
c) Quelles sont vos conclusions sur le mode READ COMMITTED ?
Question 7 : Pour aller plus loin : mode SERIALIZABLE
Pour la question 6, vous avez besoin d’un mode de gestion de transaction plus rigoureux que
celui proposé par défaut par Postgres. Vous pouvez donc passer au mode SERIALIZABLE en
tapant juste après le BEGIN, la commande suivante :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Exécutez les transactions de la question 6 en mode SERIALIZABLE. Quelles sont vos
conclusions sur ce mode ?