Remerciez-le!

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

Introduction D´ eclencheurs Transactions Bases de donn´ ees Cours 6 : Concepts

Introduction D´ eclencheurs Transactions Bases de donn´ ees Cours 6 : Concepts avanc´ es : D´ eclencheurs, Transactions, Contrˆ ole de concurrence Odile PAPINI ESIL Universit´ e de la m´ editerran´ ee Odile.Papini@esil.univ-mrs.fr http://pages-perso.esil.univmed.fr/∼papini/ Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Plan du cours 1 Introduction 2 D´ eclencheurs 3 Transactions Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Bibliographie Livres : G. Gardarin : Bases de donn´ ees objet et relationnel. Eyrolles ed. 1999. C. J. Date : Introduction aux bases de donn´ ees. (8i` eme edition). Vuibert ed. 2004. H. Garcia-Molina, J. D. Ullman, J. Widow : Database systems, the complete book. Prentice Hall ed. 2002. Supports de cours : Support de cours : J. Le Maitre : http ://lemaitre.univ-tln.fr/cours.htm Support de cours : C. Sabatier, Universit´ e de la M´ editerran´ ee. Support de cours : N. Durand, ESIL, Dpt INTERNET Support de cours : A. Cornu´ ejols : www.lri.fr/ antoine Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs D´ eﬁnition Un d´ eclencheur est une r` egle, dite active, de la forme : ´ ev` enement-condition-action Trigger : proc´ edure stock´ ee qui est d´ eclench´ ee automatiquement par des ´ ev´ enements sp´ eciﬁ´ es par le programmeur et ne s’ex´ ecutant que lorsqu’une condition est satisfaite D´ eclencheur ou Trigger est activ´ e par une requˆ ete de mise ` a jour Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs il permet : La possibilit´ e d’´ eviter les risques d’incoh´ erence dus ` a la pr´ esence de redondance L’enregistrement automatique de certains ´ ev´ enements La sp´ eciﬁcation de contraintes li´ ees ` a l’´ evolution de donn´ ees (ex : un salaire ne peut qu’augmenter) De d´ eﬁnir toutes r` egles complexes li´ ees ` a l’environnement d’ex´ ecution (ex : restrictions sur des horaires, des utilisateurs) Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Principe d’un d´ eclencheur S´ equence ´ ev´ enement-Condition-Action : Trigger d´ eclench´ e par un ´ ev´ enement, sp´ eciﬁ´ e par le programmeur Insertion, destruction, modiﬁcation sur une table Le trigger teste une condition : si cette derni` ere n’est pas v´ eriﬁ´ ee, alors l’ex´ ecution s’arrˆ ete L’action est r´ ealis´ ee (toutes op´ erations sur la base de donn´ ees) Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Caract´ eristiques d’un d´ eclencheur Concernant l’aspect action : SQL n’est pas proc´ edural Les SGBD fournissent une extension du langage SQL instructions de branchement conditionnel, instructions de r´ ep´ etition, aﬀectations, · · · Langage imp´ eratif permettant de cr´ eer des v´ eritables proc´ edures (proc´ edures stock´ ees) : PL/SQL pour ORACLE T-SQL pour SQL Server norme SQL2003 pour DB2 et MySQL5 Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Caract´ eristiques d’un d´ eclencheur Concernant l’aspect action : Manipulation simultan´ ee de l’ancienne et de la nouvelle valeur d’un attribut (permet tests sur l’´ evolution) Un trigger peut ˆ etre ex´ ecut´ e : Une fois pour un seul ordre SQL Ou ` a chaque ligne concern´ ee par cet ordre L’action peut ˆ etre r´ ealis´ ee avant ou apr` es l’´ ev´ enement Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Caract´ eristiques d’un d´ eclencheur Il ne peut y avoir qu’un seul trigger par ´ ev´ enement sur une table Les triggers permettent de rendre une base de donn´ ees dynamique Une op´ eration peut en d´ eclencher d’autres, qui elles-mˆ emes peuvent entraner en cascade d’autres triggers Ce m´ ecanisme n’est pas sans danger ` a cause de risque de boucle inﬁnie Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Cr´ eation d’un d´ eclencheur (1) Sp´ eciﬁer l’´ ev´ enement qui d´ eclenche l’action en indiquant le type de la mise ` a jour (INSERT, UPDATE, DELETE), le nom de la table et ´ eventuellement le nom des attributs mis ` a jour Indiquer si l’action est r´ ealis´ ee avant ou apr` es ´ eventuellement, donner un nom ` a l’ancien et au nouveau n-uplet (uniquement le nouveau en cas d’insertion et uniquement l’ancien en cas de suppression) Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Cr´ eation d’un d´ eclencheur (2) D´ ecrire la condition sous laquelle se d´ eclenche l’´ ev´ enement sous la forme d’une expression SQL bool´ eenne, c.-` a-d. une expression pouvant ˆ etre plac´ ee dans une clause WHERE D´ ecrire l’action ` a r´ ealiser sous la forme d’une proc´ edure Indiquer si l’action est r´ ealis´ ee pour chaque n-uplet mis ` a jour ou une seule fois pour la requˆ ete Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs CREATE TRIGGER nom trigger BEFORE | AFTER INSERT | DELETE | UPDATE [OF col,...,col] [OR INSERT | DELETE | UPDATE [OF col,· · · ,col] · · · ] ON nom table [[REFERENCING [OLD [AS] old] [NEW [AS] new ]] FOR EACH ROW [WHEN (condition pl/sql)]] bloc pl/sql ; Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Cr´ eation d’un d´ eclencheur : exemple V´ eriﬁcation qu’un prix ne peut baisser CREATE OR REPLACE TRIGGER prixNePeutDiminuer BEFORE UPDATE OF prixUnitaire ON Article FOR EACH ROW WHEN (OLD.prixUnitaire > NEW.prixUnitaire) BEGIN raise application error(-20100, ”le prix d’un produit ne peut diminuer”) ; END ; Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions D´ eclencheurs Cr´ eation d’un d´ eclencheur : exemple D´ eclencheur ins´ erant un enregistrement ` a l’int´ erieur d’une seconde table ”table2” lorsqu’une op´ eration d’insertion s’est accomplie dans une premi` ere table ”table1”. il v´ eriﬁe aussi si le nouveau n-uplet poss` ede un attribut ”attr1” sup´ erieur ou ´ egal ` a 10 CREATE TRIGGER declencheur1 AFTER INSERT ON table1 FOR EACH ROW WHEN (NEW.attr1 >= 10) BEGIN INSERT INTOtable2 VALUES( :NEW.attr1, :NEW.attr2) ; END ; Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Transactions : introduction SGBD multi-utilisateurs souvent des centaines, voire des milliers d’utilisateurs ex : syst` emes de r´ eservation (compagnies a´ eriennes), op´ erations bancaires · · · contraintes de temps r´ eel Comment ´ eviter les int´ eractions n´ egatives entre les utilisateurs et garantir la coh´ erence de la base de donn´ ees ? Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Transactions Transaction s´ equence d’instructions SQL souvent d´ elimit´ ee par des instructions de d´ ebut et de ﬁn de transaction Op´ erations ´ el´ ementaires d’acc` es ` a la base de donn´ ees : read x : lecture d’une donn´ ee lit un ´ el` ement x et le stocke dans une variable du programme write x : ´ ecriture d’une donn´ ee ´ ecrit la valeur de la variable de programme x et le stocke dans ´ el` ement de donn´ ees x Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Transactions Exemple de transaction : transfert d’une somme S d’un compte A vers un compte B T start read A A = A - S write A read B B = B + S write B commit Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Transactions transactions concurrentes Plusieurs transactions peuvent se d´ erouler en mˆ eme temps Un SGBD doit assurer que toute transaction poss` ede les propri´ et´ es suivantes (ACID) propri´ et´ es ACID Atomicit´ e : une transaction est une unit´ e atomique de traitement Coh´ erence : une transaction pr´ eserve la coh´ erence de la BD Isolation : les ex´ ecutions des transactions ne doivent pas interf´ erer les unes avec les autres Durabilit´ e : les changements appliqu´ es ` a la BD par une transaction valid´ ee doivent persister (mˆ eme suite ` a une d´ efaillance) Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Transactions : probl` emes dus ` a la concurrence Si aucun contrˆ ole du d´ eroulement des transactions : probl` emes perte de mise ` a jour lecture impropre lecture de donn´ ees incoh´ erentes lecture de donn´ ees non conﬁrm´ ees lecture non reproductible objets fantˆ omes Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Transactions : probl` emes dus ` a la concurrence (1) Perte de mise ` a jour T1 T2 BD A = 10 read A read A A = A + 10 write A A = 20 A = A + 50 write A A = 60 On devrait avoir A= 70 MAIS on a A = 60 : perte de la mise ` a jour de T1 Odile PAPINI Bases de donn´ ees Introduction D´ eclencheurs Transactions Transactions : probl` emes dus ` a la concurrence (2) Lecture impropre (donn´ ees incoh´ erentes) T1 T2 BD Contrainte A + B = uploads/Finance/ cours-bd-5bis.pdf