Administration des b ases de données

1. Administration des bases de données Chapitre 5: Gestion des transactions et gestion des accès concurrents

2. Agenda • Introduction • Transaction et contrôle de transaction • Gestion des accès concurrents • Consistance de données • Mécanisme de verrouillage • Conclusion

3. Introduction • Une BD peut être manipulée simultanément par plusieurs users ce qui implique une utilisation concurrente des BDs partagées • Certaines contraintes doivent être assurées par le SGBDR: • Une bonne disponibilité de l’information, • Une garantie de la cohérence des datas, • Et une gestion du conflit d’accès. • Pour assurer cette cohérence, nous aborderons les concepts de transaction et d’accès concurrents.

4. Partie n°1 Gestion des transactions

5. Objectifs • Ce qu’est une transaction • Pourquoi il est important de gérer les transactions dans les BDs • Quels sont les principaux protocoles de gestion de transaction • Comment écrire une transaction avec Oracle • Comment les transactions sont gérées dans Oracle

6. Transaction • Définition • Une transaction est une unité logique de traitement formée d’une suite d’opérations (interrogation (lecture R) et/ou modification (écriture W) de la BD) • Cet ensemble d’opération doit être soit validé, soit annulé • Exemple : Environnement bancaire, transfert d’une somme S d’un compte C1 à un compte C2. (1)Début transaction (2)   R(C1) (3)C1 = C1 – S (4)   W(C1) (5)R(C2) (6)C2 = C2 + S (7)   W(C2) (8)Fin de transaction. • Dans cet environnement, soit toutes les actions sont effectuées (transfert réussi), soit toutes annulées (pas de transfert)

7. Contrôle de transaction 1/5 • Le contrôle de transaction consiste à définir : • Le début et la fin de la transaction • Et le découpage de la transaction en sous transactions dans le cas où le nbre d’opérations composant la transaction est important. • Début et la fin de transaction • Une transaction démarre par • N’importe quel ordre SQL suivant la connexion à Oracle • Ou par la fin de la transaction précédente • La fin d’une transaction peut être définie soit de façon • Explicite par l’un des ordres COMMIT ou ROLLBACK • Ou implicite

8. Contrôle de transaction 2/5 • Fin de transaction explicite • COMMIT termine une transaction • En validant les datas, • En rendant définitives et accessibles aux autres users toutes les modifications effectuées pendant la transaction en les sauvegardant dans la BD, • Et en annulant tous les verrous positionnés pendant la transaction • ROLLBACK termine une transaction • En annulant toutes les modifications de datas , • Et en annulant tous les verrous positionnés pendant la transaction

9. Contrôle de transaction 3/5 • Fin de transaction implicite • L’exécution d’un ordre de LDD(create, drop, alter) après un ordre de LMD(insert, update, delete) valide la transaction en cours. • L’arrêt normal d’une session par un EXIT permet la validation en cours. • L’arrêt anormal d’une session annule la transaction en cours.

10. Contrôle de transaction 4/5 • Transaction avec étapes intermédiaires • Le découpage d’une transaction en plusieurs parties se fait en insérant des points de repères. • Ce découpage permet soit : • De valider l’ensemble des mises à jour, • Soit d’annuler tout ou une partie des maj. • La création d’un point de repère se fait par l’ordre SAVEPOINT point_de_repère

11. Contrôle de transaction 5/5 • Transaction avec étapes intermédiaires -Exemple • Update table; Insert into table; Savepoint a Delete from table; Savepoint b Rollback to a Insert into table; Commit • Quels sont les ordres SQL qui seront, définitivement, pris en considération ?

12. Atomicité Toutes les mises à jour doivent être effectuées ou aucune. Exemple : Begin CEpargne = CEpargne – 3000 CCourant = CCourant + 3000 Commit T1 Annulation du débit !! Durabilité Les modifications d’une transaction validée ne seront jamais perdue Exemple : Begin CEpargne = CEpargne – 3000 CCourant = CCourant + 3000 Commit T1 S’assurer que le virement a été bien fait !! Propriétés des transactions (ACID) Panne Crash disque

13. Cohérence La transaction doit faire passer la BD d'un état cohérent à un autre. Exemple : Cohérence des opérations Isolation Les résultats d'une transaction ne sont visibles aux autres transactions qu'une fois la transaction validée. Propriétés des transactions (ACID) N°Compte 1024 921 738 512 ... Solde 1000 -400 357 250 Programme 1 : calculer le total des soldes Programme 2: transférer 100 du compte 1024 au compte 512 Alors que le programme 1 en est au compte N°738, le programme 2 fait le transfert

14. Échéanciers • Un échéancier • est une suite d’opérations (de lecture (R), d’écriture (W), de validation (C) ou d ’abandon (A)), réalisées au cours d’un ensemble de transactions et ordonnées dans le temps • Cet échéancier n’est pas complet car aucune des deux transactions n’est validée ou abandonnée T1 T2 R(A) W(A) R(B) W(B) R(C) W(C)

15. Sérialisabilité • Un échéancier est sérialisable ssi : • l’effet de l’exécution de l’échéancier est identique à l’effet de l’exécution en série des transactions qui le composent • Notons que : • différentes exécutions en séries peuvent amener à différents résultats que l’on considèrera tous comme acceptables, la BD n’assure pas un résultat plutôt qu’un autre • Notre objectif : avoir des transactions sérialisables

16. Partie n°2 La gestion de concurrence

17. Objectifs • Permettre l’exécution simultanée d’un grand nombre de transactions • Régler les conflits lecture/écriture • Garder de très bonne performance • Eviter les blocages

18. Concurrence • Définition • Il y a Accès Concurrent (AC) lorsque plusieurs transactions (Tr) accèdent à la même data dans la BD. • La Gestion des Accès Concurrents (GAC) consiste à s’assurer que l’exécution simultanée d’un {Tr1,…Trn} qui accèdent à la même data dans la BD produit le même résultat que si les Tri avaient été exécutées en séquence c’est à dire que l’échéancier est belle bien sérialisable. • La GAC repose sur les concepts de • Consistance • Intégrité des datas

19. Problèmes posés par les AC 1/3 • Interférence : • Il y a une interférence entre 2 transactions T1 et T2 si le résultat de l’exécution de T1 et T2 en // est  de celui de l’exécution en série de T1 et T2. • Cette interférence engendre soit : • Une perte de mise à jour • Ou une lecture impropre

20. Problèmes posés par les AC 2/3 • Exemple de perte de mise à jour

21. Problèmes posés par les AC 3/3 • Exemple de lecture impropre

22. Comment résoudre le problème d’interférence? • Ce problème a été résolu grâce aux concepts: • Consistance des datas : il y a consistance lorsque le système garantit que les datas utilisées par le query ne sont pas modifiées par d’autres opérations pendant toute la durée de l’exécution de ladite query. • Intégrité : l’intégrité est assurée lorsque la base passe d’un état cohérent à un autre état cohérent après la maj.

23. Consistance des datas • En consultation • Un user exécutant un SELECT sur une table ou +sieurs est sûr de voir toutes les datas telles qu’elles étaient au début de la transaction même si d’autres users modifient la table et valident leurs modifications pendant ce temps. • Pour cela, le SGBDR enregistre la date de début de la Tr • En mise à jour • Les modifications des datas non validées par un COMMIT sont visibles uniquement à l’intérieur de la transaction en cours (propriété d’isolation d’une Tr). Elles sont accessibles aux autres users qu’après la validation de la transaction.

24. Principe de la consistance de datas TrA lit la data modifié, TrB lit l’ancienne valeur Toutes les Tr lisent les datas de la table TrA modifie un enregistrement, l’ancienne valeur est placée dans le rollback segment 20 20 TrA TrB

25. Principe de la consistance de datas • Validation de la TrA • C’est juste qu’après la validation de la TrA que les modifications deviennent permanents • Rollback segment et verrous seront libérés • Seules les ordres qui ont débuté après la validation accèdent aux datas modifiés • Tous les autres accèdent aux datas conservées dans le Rollback segment • Annulation de la TrA • Les datas écrites dans le Rollback segment viennent écraser les datas modifiés • Rollback segment et verrous seront libérés

26. Mécanismes de verrouillage • La consistance est assurée grâce aux mécanismes de verrouillage. • Ces mécanismes garantissent la réservation temporaire de ressources à une transaction. • Un SGBDR offre plusieurs niveaux de verrouillage, appelés granularité: • Logique, sur table ou sur une ligne • Physique, sur segment, fichier ou page

27. Mécanismes de verrouillage • Oracle8 n’offre que le niveau logique: • Verrouillage au niveau de la ligne (type TX). • Juste les lignes modifiées par la transaction sont verrouillées • On doit disposer de l’option TPO (Transaction Processing Option), mise en œuvre par le paramètre ROW_LOCKING=ALWAYS du fichier init.ora • Verrouillage au niveau de la table (type TM) • Toutes les lignes de la table sont verrouillées • Les autres transactions continuent à consulter les datas de la table, y compris ceux qui sont modifiées

28. Mécanismes de verrouillage • On peut utiliser les verrous pour verrouiller: • Les datas : (data locks ou DML) DML protègent les datas de l’user. • Le dictionnaire de datas (dictionary lock) : ce qui permet de protéger la description des structures de datas • La définition de la table ne peut pas être modifiée pendant une transaction • Les Objets systèmes (SGA, buffer,…). Ce qu’on appelle par verrous internes (latches) • Automatiques

29. Les différents types de verrouillage • Les verrous TX (ligne) et TM (table) sont aussi classés selon deux catégories: • Verrou exclusif • Verrou partagé • Alors, les différents type de verrouillage sont: • Verrouillage exclusif de la table (X) • Verrouillage de la table en mode partagé (S) • Verrouillage sélectif des lignes (RS) • Verrouillage exclusif des lignes (RX)

30. Mise en œuvre des verrous • Verrouillage implicite géré par le SGBDR. Ce type de verrouillage assure la consistance et l'intégrité des données • INSERT, UPDATE et DELETE posent automatiquement un verrou RX sur la table concernée. • SELECT ne demande jamais de verrou. Possibilité d’exécuter un SELECT sur une table même si il y a des verrous sur la table ou sur ses lignes. • SELECT … FOR UPDATE permet de réserver, en vue d’une maj ultérieure, les lignes qui sont consultées. Un verrou RS est allouée donc sur la table • Verrouillage explicite par la commande LOCK • En complément du verrouillage implicite, nous pouvons définir une stratégie de verrouillage. • Il est fortement déconseillé d’utiliser le LOCK TABLE car le verrouillage automatique d’Oracle (à partir de 6.x) est plus performant que le mode manuel

31. Verrouillage exclusif de la table (X) • C'est le mode le plus restrictif, il assure aux Tr qui l'obtient un accès exclusif en écriture sur une table par la commande : LOCK TABLE table IN EXCLUSIVE MODE; • Opérations • Seule une Tr peut avoir un accès en mode X pour une table • Les autres Tr peuvent consulter les datas de la table mais ne peuvent ni les modifier, ni poser des verrous sur la table et sur ses lignes.

32. Verrouillage de la table en mode partagé (S) • Commande • LOCK TABLE table IN SHARE MODE; • Opérations • Les autres Tr peuvent mettre différents verrous en mode partagé sur la table (RS ou S) • Une Tr qui verrouille une table en mode S empêche les autres transactions de demander des verrous de type SRX ou X.

33. Verrouillage sélectif des lignes (RS) • Il est acquis de manière automatique si l'une des commandes SQL suivante est exécuté: • SELECT ... FROM table ... FOR UPDATE OF ... ; • LOCK TABLE table IN ROW SHARE MODE; • Opérations • Ce verrou est le moins contraignant. • Il permet aux autres Tr de mettre à jour les datas autres que celles concernées par la Tr qui a demandé le verrou (RS). • En revanche, aucune autre transaction ne peut demander de verrou de type X.

34. Verrouillage exclusif des lignes (RX) • Ce mode est acquis de façon automatique pour une table modifiée par l'une des instructions suivantes : INSERT INTO table ...; UPDATE table ...; DELETE FROM table ...; LOCK TABLE table IN ROW EXCLUSIVE MODE; • Opérations: • Ce mode empêche d'autres Tr de verrouiller manuellement la table en mode S, X ou SRX • La Tr qui a mis le verrouillage peut modifier un ou plusieurs les lignes de la table

35. DML Locks • Le tableau suivant résumera les combinaisons possibles des divers modes de verrouillage que peuvent demander des transactions simultanées sur la même table.

36. Gestion des verrous • Le gestionnaire de verrou maintient une table de verrous qui contient pour chaque objet sur lequel il y a un (ou des) verrou(s) : • le nombre de transactions portant sur l’objet • la nature des verrous (partagé ou exclusif) • un pointeur vers une queue de demandes de verrous • Le SGBD maintient aussi la description des transactions dans une table des transactions qui contient : • des pointeurs vers une liste de verrous posés par chaque transaction

37. Contrôle de concurrence : Protocole de S2PL • Le protocole de verrou «Strict 2-Phase Locking» est basé sur deux règles : • Si une transaction T veut lire (resp. modifier) un objet, il demande d’abord un verrou partagé (resp. un verrou exclusif) sur l’objet • Tous les verrous posés par une transaction sont relâchés lorsque la Tr se termine

38. T1 T2 R(A) W(A) R(A) W(A) R(B) W(B) Commit R(B) W(B) Commit Contrôle de concurrence : Protocole de S2PL Sans verrou T1 T2 X(A) R(A) W(A) X(B) R(B) W(B) Commit X(A) R(A) W(A) X(B) R(B) W(B) Commit Protocole S2PL

39. Contrôle de concurrence : Protocole de S2PL • Limite du protocole S2PL: • Temps d’attente pour la Tr qui n’a pas obtenu en premier le verrou • Nécessité donc d’un autre protocole qui ne pénalise pas les Tr

40. Contrôle de concurrence : Protocole de 2PL • Le protocole de verrou «2-Phase Locking» est basé aussi sur deux règles : • Si une transaction T veut lire (resp. modifier) un objet, il demande d’abord un verrou partagé (resp. un verrou exclusif) sur l’objet • Une transaction peutrelâcher un verrou avant de se terminer, mais elle ne peut plusredemander de verrou une fois qu’elle en a relâché un • Toute Tr est alors composée de deux phases : • la phase croissante : demandes de verrous • la phase décroissante : relâchements de verrous • une Tr ne relâche un verrou sur un objet que si elle est sûre que celui-ci ne sera plus utilisé (ni en lecture ni en écriture) • une Tr ne relâche les verrous sur un objet que si elle est sûre que plus aucun verrou ne sera posé sur les autres objets

41. T1 T2 X(A) R(A) W(A) X(B) Rel(A) X(A) R(A) W(A) R(B) W(B) Rel(B) Commit ... T1 T2 X(B) Rel(A) R(B) W(B) Rel(B) Commit T1 T2 R(A) W(A) R(A) W(A) R(B) W(B) Commit R(B) W(B) Commit ... Contrôle de concurrence : Protocole de 2PL Sans verrou Protocole 2PL

42. T1 T2 W(A) W(B) W(A) Commit W(B) Commit Contrôle de concurrence :deadlock Avec le 2PL Conflit WW classique Avec le S2PL T1 T2 X(A) W(A) X(B) W(B) X(A) Rel(B) T1 T2 X(A) W(A) X(B) W(B) X(A) Impossible que T2 met un verrou sur A puisque T1 a déjà mis un verrou exclusif sur A : Blocage Pour pouvoir relâcher le verrou sur B, T2 doit mettre un verrou sur A, ce qui est impossible puisque T1 a déjà mis un verrou exclusif sur A : Blocage

43. T2 T1 T3 Contrôle de concurrence: Deadlock • Problème de deadlock (DL) • Un DL peut survenir quand deux ou +sieurs users sont en attente d'une ressource mutuellement verrouillée par chacun d'entre eux, • Oracle détecte automatiquement lesDL et le résout par l'annulation de l'une des commandes qui l'a entraîné • un message est envoyé aux Tr concernées. C'est la Tr qui détecte en premier le DL qui est annulée. • Graphe d’attente • chaque noeud identifie une transaction active • il y a un arc de Ti vers Tj ssi Ti attend que Tj aie relâché un verrou pour continuer • si le graphe contient un cycle, il y a DL

44. Résolution du DL • Prévention versus Détection? • Prévention • définir des critères de priorité de sorte à ce que le problème ne se pose pas • exemple : priorité aux transactions les plus anciennes ou priorité aux transactions qui ont le plus de verrous • Détection • gérer le graphe des attentes • lancer un algorithme de détection de circuits dès qu’une transaction attend trop longtemps • choisir une victime qui brise le circuit

45. Contrôle de concurrence sans verrou • Contrôle de concurrence par estampillage : • Chaque transaction reçoit une date de début (estampillage) • Si une action ai de la transaction Ti est en conflit avec l’action aj de la transaction Tj et TS(Ti) > TS (Tj) alors Ti est abandonnée