Cours3.pdf

Base de données et interopérabilité Département Informatique École supérieure d'ingénieurs Léonard-de-Vinci (ESILV) 26 MARS, 2024 Ruiwen HE LES TRANSACTIONS PÔLE LÉ ONARD DE VINCI La notion de transaction. Les anomalies transactionnelles Niveau d’isolation B D D & i n t e r o p é r a b i l i t é rui...

Base de données et interopérabilité Département Informatique École supérieure d'ingénieurs Léonard-de-Vinci (ESILV) 26 MARS, 2024 Ruiwen HE LES TRANSACTIONS PÔLE LÉ ONARD DE VINCI La notion de transaction. Les anomalies transactionnelles Niveau d’isolation B D D & i n t e r o p é r a b i l i t é [email protected] PLAN DU COURS 3 PÔLE LÉ ONARD DE VINCI B D D & i n t e r o p é r a b i l i t é [email protected] Système bancaire 4 Opérations de transfert entre deux comptes bancaires Opérations de transfert → Procédure stockée CREATE PROCEDURE transfer_proc( IN compte_debit INT, IN compte_credit INT, IN sum_conso DECIMAL(18,2)) BEGIN DECLARE solde_credit DECIMAL(18,2); DECLARE solde_debit DECIMAL(18,2); SELECT solde INTO solde_credit FROM compte WHERE id = compte_credit; SELECT solde INTO solde_debit FROM compte WHERE id = compte_debit; UPDATE compte SET Solde = solde_credit + sum_conso WHERE id = compte_credit; UPDATE compte SET Solde = solde_debit - sum_conso WHERE id = compte_debit; P Ô L END E LÉ ONARD DE VINCI Système bancaire Appeler la procédure stockée : CALL transfer_proc(2,1,800); Consulter les données de votre compte : SELECT * FROM compte; Vendeur Client 1 Achat B D D & i n t e r o p é r a b i l i t é 800€ 5 PÔLE LÉ ONARD DE VINCI [email protected] Définition Un ensemble ordonné d'opérations de mise à jour (INSERT, UPDATE, DELETE) d'une base de données qui sera entièrement validé ou entièrement annulé. Une transaction est une séquence d’opérations de lecture ou d’écriture, se terminant par COMMIT ou ROLLBACK. B D D & i n t e r o p é r a b i l i t é Qu’est-ce qu’une transaction 6 Le commit est une instruction qui valide toutes les mises à jour. Le rollback est une instruction qui annule toutes les mises à jour. PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 7 Qu’est-ce qu’une transaction On parle de transaction plutôt que de programme : beaucoup plus précis. Une transaction est le produit d’un échange entre un processus client et un processus serveur (SGBD). On peut effectuer une ou plusieurs transactions successives dans un même processus : elles sont dites sérielles. En revanche, deux processus distincts engendrent des transactions concurrentes. PÔLE LÉ ONARD DE VINCI Quand plusieurs programmes clients sont actifs simultanément, les transactions engendrées s’effectuent en concurrence. B D D & i n t e r o p é r a b i l i t é [email protected] Exécutions concurrentes 8 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 9 Propriétés des transactions Un système relationnel contrôle la concurrente et garantit un ensemble de propriétés rassemblées sous l’acronyme ACID. A = Atomicité. Une transaction est validée complètement ou pas du tout. C = Cohérence. Une transaction mène d’un état cohérent à un autre état cohérent. I = Isolation. Une transaction s’exécute comme si elle était seule. D = Durabilité. Quand le commit s’exécute, ses résultats sont définitifs. PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 10 Anomalies transactionnelles Mais parfois, en raison d‘une mauvaise conception des transactions ou de bogues dans le système, les transactions vont à l’encontre des quatre principes susmentionnés. En général, plusieurs types de anomalies peuvent se produire, comme suit, souvent appelé problème de contrôle de la concurrence : Les mises à jour perdues Lectures sales Les lectures non répétables Les lectures fantôme PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 11 Les mises à jour perdues Lorsque plusieurs transactions lisent les mêmes données et y apportent des modifications en même temps, les modifications apportées par la dernière transaction validée peuvent écraser les modifications apportées par la première transaction validée, ce qui entraîne la perte des modifications précédentes. Les mises à jour perdues peuvent entraîner des données incohérentes et des résultats inattendus. PÔLE LÉ ONARD DE VINCI En supposant que deux clients fassent leurs achats en même temps et paient en même temps à des guichets différents i n t e r o p é r a b i l i t é [email protected] Example : mises à jour perdues & Guichet B (T2) B D D 12 Guichet A (T1) PÔLE LÉ ONARD DE VINCI C1 = 0 A2 = 1000 A3 = 1000 C2=1000 - 800 C1=0 + 800 C1=0 + 300 Guichet A (T1) Guichet B (T2) Start transaction Start transaction Lecture C1 Lecture C1 Lecture C2 Lecture C3 Écriture C2 Écriture C1 Écriture C1 B D D & i n t e r o p é r a b i l i t é [email protected] Example : mises à jour perdues Écriture C3 C3=1000 - 300 Committed Committed C1=800 C2=200 13 C1=300 C3=700 PÔLE LÉ ONARD DE VINCI Pas à pas, voici ce qui se passe. T1 lit C2 et C1. solde de vendeur: 1000 € et 0 €. T2 lit C3 et C1. solde de vendeur : 1000 € et 0 €. T 1 écrit C1 avec solde de vendeur = 0 + 800 = 800 €. T1 écrit le nouveau compte de C1. T 2 écrit s avec solde = 0 + 300 = 1300 €. T2 écrit le nouveau compte de C2. B D D & i n t e r o p é r a b i l i t é [email protected] Analyses de l’ Anomalies 14 À l’arrivée, 1100 € de marchandises vendues, 300 € acquis. Incohérence. PÔLE LÉ ONARD DE VINCI [email protected] Cette anomalie ne survient qu’en cas d’entrelacement défavorable. Dans la plupart des cas, une exécution concurrente ne pose pas de problème. Le programme est correct. On peut le regarder 1000 fois, le tester 10000 fois sans jamais détecter d’anomalie. B D D & i n t e r o p é r a b i l i t é Mise à jour perdues 15 PÔLE LÉ ONARD DE VINCI Solution radicale : exécution en série [email protected] Guichet A (T1) Start transaction Très pénalisant.. Je débute une petite transaction, Je vais commencer à faire du shopping, je bloque tout le monde. Lecture C1 Lecture C2 Écriture C1 Écriture C2 C2 = 200 C1 = 800 Guichet B (T2) Start transaction Lecture C1 C1 = 800 C3 = 1000 Lecture C3 B D D & i n t e r o p é r a b i l i t é Committed Écriture C3 C3 = 1000 - 300 C1 = 800 + 300 Écriture C1 Committed A2=700 A3=1100 16 PÔLE LÉ ONARD DE VINCI [email protected] Les exécutions concurrentes sont possibles Guichet A (T1) Start transaction Lecture C1 Lecture C2 B D D Écriture C1 Start transaction Lecture C3 C3 = 1000 C1 = 800 Lecture C1 Écriture C1 Écriture C2 & i n t e r o p é r a b i l i t é C1 =0 + 800 Guichet B (T2) C1=800 + 300 C2=1000 - 800 C3=1000 - 300 Écriture C3 Committed Committed C1=1100 C2=200 C3=700 17 PÔLE LÉ ONARD DE VINCI [email protected] Un Lecture sale fait référence à la lecture, dans une transaction, de données modifiées par une autre transaction non validée. Un Lecture sale se produit lorsqu'une transaction lit des données qui n'ont pas encore été validées par une autre transaction, et qu'un rollback s'ensuit B D D & i n t e r o p é r a b i l i t é Lectures sales 18 PÔLE LÉ ONARD DE VINCI B D D & i n t e r o p é r a b i l i t é [email protected] Example : Lectures sales Guichet A (T1) Start transaction Lecture C1 C1 = 0 C2 = 1000 C1 = 0 + 800 C2 = 1000 - 800 Lecture C2 Écriture C1 Guichet B (T2) Écriture C2 Start transaction C1 = 800 C3 = 1000 C1=0 C3=1000 Lecture C1 Lecture C3 Rollback Écriture C1 C1=1000 - 300 C3=800 + 300 Écriture C3 Committed 19 C1=1100 C2=1000 PÔLE C3=700 LÉ ONARD DE VINCI [email protected] Une transaction lit deux fois la même ligne de données et obtient des résultats dans des états différents. Étant donné qu'une autre transaction a mis à jour les données au cours du processus intermédiaire, les deux résultats ne sont pas identiques et ne sont pas fiables. B D D & i n t e r o p é r a b i l i t é Les lectures non répétables 20 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 21 Les lectures non répétables Paul prend sa carte et va faire des achats, il y a 1000 € dans la carte, quand il est prêt à payer (la transaction est ouverte), le système détecte à l'avance qu'il y a 1000 € dans sa carte, juste à ce moment-là, sa femme va retirer tout l'argent pour acheter des bijoux et le soumettre. Lorsque le système de facturation est prêt à débiter, il détecte le montant de la carte et constate qu'il n'y a pas de solde (la deuxième détection du montant, bien sûr, attend que la femme de A transfère le montant de la transaction), A sera très déprimé : où est passé l'argent ? PÔLE LÉ ONARD DE VINCI [email protected] Une transaction interroge tous les enregistrements d'une table de données, lorsque la première transaction n'ai pas encore validée, une autre transaction ajoute un élément de données, puis la première transaction est validée et constate que le résultat n'est pas le même que celui trouvé précédemment. B D D & i n t e r o p é r a b i l i t é Les lectures fantôme 22 PÔLE LÉ ONARD DE VINCI [email protected] Lorsque vous vérifiez votre solde à la banque, vous constatez qu'il vous reste 3 000 euros sur votre carte, puis votre femme achète 2 000 euros de marchandises avec votre carte, et lorsque votre conseiller bancaire vous demande si vous devez imprimer votre demande, vous réalisez soudain qu'il ne vous reste que 1 000 euros sur votre carte. B D D & i n t e r o p é r a b i l i t é Les lectures fantôme 23 PÔLE LÉ ONARD DE VINCI T1 lit une certaine plage de données, T2 insère de nouvelles données dans cette plage, T1 lit à nouveau cette plage de données et le résultat de cette lecture est différent du résultat de la première lecture. T1 & INSERT INTO student(id, gender, age) VALUES(‘008’,’M’, 18) (Add a new record with age=18) B D D 24 T2 SELECT * FROM student WHERE age=18 (100 records with age=18) i n t e r o p é r a b i l i t é [email protected] Les lectures fantôme SELECT * FROM student WHERE age=18 (101 records with age=18 !!!) PÔLE LÉ ONARD DE VINCI B D D & i n t e r o p é r a b i l i t é [email protected] Niveaux d’isolation 25 Définis en fonction des anomalies : lectures sales, lectures non répétables, fantômes. read uncommitted : tout est permis, toutes les anomalies sont possibles. read committed : lectures sales non permises. repeatable read : seuls les tuples fantômes sont permis. serializable : isolation totale, aucune anomalie, interblocages possibles. Niveau par défaut : read committed (Oracle) ou repeatable read (MySQL, PostgreSQL). Consulter le niveau d‘isolation actuel: SELECT @@transaction_ISOLATION; PÔLE LÉ ONARD DE VINCI Toutes les anomalies sont possibles! SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read uncommitted 26 PÔLE LÉ ONARD DE VINCI Toutes les anomalies sont possibles! Terminal B Terminal A B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read uncommitted 27 PÔLE LÉ ONARD DE VINCI Normalement on aura: i n t e r o p é r a b i l i t é [email protected] Le mode read uncommitted B D D & Mise à jour perdues Ça arrive parfois ne pas essayer de répéter 28 PÔLE LÉ ONARD DE VINCI Terminal A Terminal B B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read uncommitted 29 PÔLE LÉ ONARD DE VINCI Terminal A Terminal B B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read uncommitted 30 PÔLE LÉ ONARD DE VINCI Lectures sales B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read uncommitted 31 PÔLE LÉ ONARD DE VINCI [email protected] Pas de lecture sale, car donnée en cours de modification ne fait pas partie de l’état de la base. Assez fluide, mais autorise beaucoup d’anomalies (mises à jour perdues, , lectures non répétables, fantômes.). B D D & i n t e r o p é r a b i l i t é Le mode read committed 32 Démonstration : on peut lire deux fois le même tuple dans une transaction et obtenir des résultats différents. PÔLE LÉ ONARD DE VINCI B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read committed 33 PÔLE LÉ ONARD DE VINCI Terminal A Terminal B B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read committed 34 PÔLE LÉ ONARD DE VINCI B D D & i n t e r o p é r a b i l i t é [email protected] Le mode read committed Terminal A 35 Les lectures non répétables PÔLE LÉ ONARD DE VINCI Terminal B [email protected] i n t e r o p é r a b i l i t é Pas de lecture sale, pas de lecture non répétable : les requêtes accèdent toujours au même état de la base. Autorise les mises à jour perdues (non répétables, ça arrive tous les temps, SAUF……. ). SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; B D D & Le mode repeatable read Démonstration : on peut lire n fois le même tuple dans une transaction et obtenir toujours le même résultat. 36 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 37 Le mode serializable Retenir : garantit l’isolation totale, et donc la cohérence de la base. mais. On se place dans ce mode avec la commande suivante, au début du code de la transaction. SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; PÔLE LÉ ONARD DE VINCI [email protected] Contrôle des transactions in mySQL -- Début de transactions START TRANSACTION; -- Exécuter des requêtes SQL UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; B D D & i n t e r o p é r a b i l i t é UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; -- Déterminer s'il faut faire un commit ou un rollback IF (condition) THEN %@ERROR 0 COMMIT; -- Soumission des transactions ELSE ROLLBACK; -- Annulation des transactions END IF; 38 PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 39 Transaction irréversible Certaines clauses ne peuvent pas être annulées dans une transaction. En général, il s'agit des instructions du langage de définition des données (DDL), telles que celles qui créent ou suppriment des bases de données, celles qui créent, suppriment ou modifient des tables ou des routines stockées. DDL(Data Definition Language) Verrouillage Ouvrir une nouvelle transaction avant que la transaction actuelle ne soit validée. PÔLE LÉ ONARD DE VINCI [email protected] B D D & i n t e r o p é r a b i l i t é DDL(Data Definition Language) 40 CREATE TABLE… PÔLE LÉ ONARD DE VINCI [email protected] B D D & i n t e r o p é r a b i l i t é DDL(Data Definition Language) 41 DROP TABLE … ALTER TABLE … PÔLE LÉ ONARD DE VINCI 42 B D D & i n t e r o p é r a b i l i t é [email protected] Verrouillage PÔLE LÉ ONARD DE VINCI [email protected] B D D & i n t e r o p é r a b i l i t é Ouvrir une nouvelle transaction avant que la transaction actuelle ne soit validée. 43 PÔLE LÉ ONARD DE VINCI