Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

TD 3 – Transactions et accès concurrents 1 Etablissement : ISET-Charguia Départ

TD 3 – Transactions et accès concurrents 1 Etablissement : ISET-Charguia Département : Technologies de l’Informatique Matière : SGBD Année Universitaire : 2021 - 2022 Niveau : 2ème DSI TD 3 – Transactions et accès concurrents Exercice 1 1- Quel est le résultat de l'exécution suivante sous Oracle ? Solution : 06 2- Combien de lignes renvoie l'avant-dernière instruction SELECT ? Solution : 03 lignes 3- Combien de tables sont créées par cette série d'instruction ? Solution : 03 tables TD 3 – Transactions et accès concurrents 2 Exercice 2 Pour faire un transfert sécurisé d'un point de vue transactionnel de 100€ du compte bancaire numéro 12 vers le compte bancaire numéro 15, quelle est la série d'instructions correcte (en mode autocommit off) ?  UPDATE Compte SET Solde=Solde-100 WHERE Numero=12; ROLLBACK; UPDATE Compte SET Solde=Solde+100 WHERE Numero=15; COMMIT;  UPDATE Compte SET Solde=Solde-100 WHERE Numero=12 ; UPDATE Compte SET Solde=Solde+100 WHERE Numero=15 ROLLBACK;  UPDATE Compte SET Solde=Solde-100 WHERE Numero=12; COMMIT; UPDATE Compte SET Solde=Solde+100 WHERE Numero=15; COMMIT;  UPDATE Compte SET Solde=Solde-100 WHERE Numero=12; UPDATE Compte SET Solde=Solde+100 WHERE Numero=15; COMMIT;  UPDATE Compte SET Solde=Solde-100 WHERE Numero=12; ROLLBACK; UPDATE Compte SET Solde=Solde-100 WHERE Numero=15; ROLLBACK; Solution : 4ème série d’instructions Exercice 3 Soit l'exécution concurrente des deux transactions ci-dessous (on suppose qu'aucune autre transaction ne s'exécute par ailleurs). Temps Transaction 1 Transaction 2 t0 Connect t1 Connect t2 Update Table1 Set Table1.A=Table1.A + 1 ; t3 Update Table1 Set Table1.A=Table1.A + 1 ; t4 Update Table1 Set Table1.A=Table1.A + 1 ; t5 Commit ; t6 ? 1- Quel est le type des verrous acquis pour ces opérations de mise à jour ? TD 3 – Transactions et accès concurrents 3 Solution : Verrou Exclusif X car c’est une mise à jour. 2- De combien le champ TABLE1.A a-t-il été augmenté à t6 du point de vue de la transaction 2 ? Expliquez votre réponse en fonction du type des verrous obtenus pour chaque mise à jour. Solution : 2 (augmentation définitive) + 1 (augmentation non confirmée) Explication : • La transaction 1 obtient un verrou X à t2 et exécute donc sans problème ses deux UPDATE. • La transaction 2 est mise en attente avant son UPDATE à t3, car elle ne peut obtenir de verrou X déjà posé par la transaction 1. • Mais dès que la transaction 1 exécute son COMMIT à t5, la transaction 1 peut poser un verrou X (libéré par la transaction 1) et peut donc exécuter son UPDATE. • A t6 la transaction 2 voit donc que A a été augmenté de 3, puisque les trois transactions UPDATE ont été exécutées. • Notons néanmoins que la transaction 2 n'a pas encore fait de COMMIT, et donc que potentiellement un ajout de 1 sur A pourrait être annulé plus tard par un ROLLBACK de la transaction 2.  Donc une augmentation de 2 est définitivement acquise et une augmentation de 1 supplémentaire est acquise pour le moment, mais pourra être remise en cause. Juste pour explication (ce n’est pas demandé dans l’exercice) : De combien le champ TABLE1.A a-t-il été augmenté à t6 du point de vue de la transaction 1 ? Une augmentation de 2 uniquement est définitivement acquise car tant que la transaction 2 n’a pas validé son ajout par un commit, alors cet ajout ne sera pas visible par la transaction 1. Notons que la transaction 2 peut ne pas valider cet ajout en appliquant un rollback. TD 3 – Transactions et accès concurrents 4 Exercice 4 - Le schéma relationnel de la base de données est le suivant : CompteCourant(idClient, nom, prenom, soldeC) CompteEpargne(#idClient, soldeE) Partie 1 Soient les événements suivants survenant sur la BD : - Le client Robert Dupont est créé dans l'agence du centre-ville de Compiègne, qui vient d'ouvrir. - Le client Alphonse Durand est créé dans la même agence, mais il veut également un compte d'épargne sur lequel il dépose tout de suite 1000. - Le client Robert Dupont dépose un chèque de 100 sur son compte courant. - Le client Alphonse Durand transfère 500 de son compte d'épargne à son compte courant. - Le client Robert Dupont dépose un chèque de 200 sur son compte courant. Travail demandé 1- Écrire le code SQL permettant de traiter ces événements. Respecter l’ordre des événements. Solution : insert into CompteCourant values(1, ‘Robert’, ‘Dupont’, 0); insert into CompteCourant values(2, ‘Alphonse’, ‘Durand’, 0); insert into CompteEpargne values(2,NULL); update CompteEpargne set SoldeE=1000 where idClient=2; update CompteCourant set SoldeC=100 where idClient=1; update CompteEpargne set SoldeE=SoldeE-500 where idClient=2; update CompteCourant set SoldeC=SoldeC+500 where idClient=2; update CompteCourant set SoldeC= SoldeC+200 where idClient=1; TD 3 – Transactions et accès concurrents 5 Partie 2 Suite à des problèmes de coupure réseaux, on constate des problèmes sur les comptes. Ainsi suite à l'exécution des opérations précédentes, la requête suivante renvoie des résultats erronés. SELECT TCC.idClient AS N, TCC.SoldeC + NVL (TCE.SoldeE, 0) AS SoldeDeTousComptes FROM CompteCourant TCC, CompteEpargne TCE WHERE TCC.idClient=TCE.idClient; N SoldeDeTousComptes 1 100 2 500 Indication : La fonction NVL renvoie comme valeur le second paramètre, si le premier à pour valeur NULL. Travail demandé 1- Réécrivez le résultat de la requête avec les valeurs qui étaient attendues N SoldeDeTousComptes 1 300 2 1000 2- Expliquer à quoi peuvent être dus les problèmes rencontrés et localiser le moment de la panne. Solution : Cause : Certainement l’autocommit est activé. Localisation de la panne : avant les 2 dernières mises à jour. 3- Proposer une solution permettant d'assurer la cohérence des opérations, en aménageant le code SQL. Solution : désactiver l’autocommit et considérer l’ensemble des évènements comme une seule transaction. set autocommit off ; insert into CompteCourant values(1, ‘Robert’, ‘Dupont’, 0); insert into CompteCourant values(2, ‘Alphonse’, ‘Durand’, 0); TD 3 – Transactions et accès concurrents 6 insert into CompteEpargne values(2,NULL); update CompteEpargne set SoldeE=1000 where idClient=2; update CompteCourant set SoldeC=100 where idClient=1; update CompteEpargne set SoldeE=SoldeE-500 where idClient=2; update CompteCourant set SoldeC=SoldeC+500 where idClient=2; update CompteCourant set SoldeC= SoldeC+200 where idClient=1; Commit ; Notion de conflit : deux transactions dans une exécution sont en conflit si elles accèdent au même nuplet (la même ressource) et si un de ces accès au moins est une écriture. Exercice 5 Quelles sont parmi les exécutions suivantes celles qui produisent des anomalies/conflits ? Avec : i : désigne le numéro de la transaction. ai : signifie que la transaction i est avortée. vi : signifie que la transaction i est validée 1. x, y et z sont des tuples de la base de données. r : read (opération de lecture). w : write (opération d’écriture). a- r1(x), w1(x), r2(x), w2(y), a1, v2 Solution : anomalie de lecture impropre. Explication : la transaction 2 lit une valeur de x (r2(x)) mise à jour par la transaction 1 (w1(x)) mais qui ne sera finalement pas validée (a1). b- r1(x), w1(x), r2(y), w2(y), a1, v2 Solution : pas de conflit car les transactions 1 et 2 n’agissent pas sur la même ressource. c- r1(x), r2(x),r2(y), w2(y),r1(z), a1, v2 Solution : pas de conflit car x (ressource concurrente) n’est que lue par les deux transactions. TD 3 – Transactions et accès concurrents 7 d- r1(x), r2(x), w2(x),w1(x), v1, v2 Solution : anomalie de perte de mise à jour Explication : la mise à jour faite par la transaction 2 sur x (w2(x)) est écrasée par la mise à jour faite par la transaction 1 sur x. e- r1(x), r2(x), w2(x),r1(y), v1, v2 Solution : il y a un conflit mais qui ne pose pas de problème ici, car après la mise à jour w2(x) T1 n’accède plus à x et la T1 est terminée (v1). Dans le cas contraire, on aura une lecture non reproductible. Exercice 6 r1(x) r2(y) w1(y) w3(x) w1(t) w5(x) r4(z) r2(z) w5(z) r3(t) r5(t) Travail demandé 1- Déduire les conflits, par tuples, et tracer le graphe de dépendances. Solution : Histoire/série d’exécution sur x : • Les conflits : r1(x) w3(x) w5(x) donc : T1→ T3→ T5 Histoire/série d’exécution sur y : • Les conflits : r2(y) w1(y) donc : T2→ T1. Histoire/série d’exécution sur z : • Les conflits : r4(z) r2(z) w5(z) donc : T4→ T2→ T5 Histoire/série d’exécution sur t : • Les conflits : w1(t) r3(t) r5(t) donc : T1→ T3→ T5. Le graphe de dépendances : T1 T2 T3 T4 T5 TD 3 – Transactions et accès concurrents 8 2- Cette exécution est-elle sérialisable ? Si oui, donnez une exécution en série qui lui soit équivalente. Solution : oui acyclique, Exécution en série : T4 →T2→T1→T3→T5. Exercice 7 1- Construisez les graphes de dépendances pour les trois exécutions suivantes. E1 : w2[x] w3[z] w2[y] c2 r1[x] w1[z] c1 r3[y] c3 Solution : • H1 – conflits sur x : w2[x] r1[x] donc : T2 → T1 • H2 – conflits sur y : w2[y] r3[y] donc : T2 → T3 • H3 – conflits sur z : w3[z] w1[z] donc : T3→ T1 E2 : r1[x] w2[y] r3[y] w3[z] c3 w1[z] c1 w2[x] uploads/Finance/ td3-transactions-et-acces-concurrents-correction.pdf