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 passet-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 ?