1

Bases de données et sites WEB Licence d’informatique

LI345

Anne Doucet Anne.Doucet@lip6.fr

http://www-bd.lip6.fr/ens/li345-2013/index.php/LesCours

2

Contenu

• Transactions en pratique • Modèle relationnel-objet • Langage de requêtes SQL3 • XML et DTD • XPath • XSLT (2 cours) • Web et BD • PHP (2 cours) • Droits et privilèges, autorisations

3

Bibliographie

• G. Gardarin : Bases de Données – objet et relationnel, Eyrolles, 1999.

• H. Garcia-Molina, J.D.Ullman, J. Widom : Database System Implementation, Prentice Hall, 2000.

• R. Ramakrishnan : Database Management Systems, mc-Graw Hill, 1997.

• Documentation Oracle (Ressources, Documentation sur www.infop6.jussieu.fr )

• Documentation XML : www.w3c.org/TR/REC-xml • B. Amann, P. Rigaux : Comprendre XSLT, O’Reilly, 2002

4

Bases de données et sites WEB Cours1 : Transactions en pratique

Anne Doucet

5

Plan

• Rappels – Sérialisabilité et propriétés ACID – Lectures sales et degrés d’isolation en SQL

• Transactions en ORACLE – Gestion de la cohérence des données

• READ ONLY / READ WRITE • SET TRANSACTION • Contrôle de cohérence multiversion • Modes de verrouillage

6

Prérequis

• Notion de transaction : – Suite d’opérations de mise à jour de la base de données, qui

transforme la base d’un état cohérent en un autre état cohérent.

• Gestion des pannes – Journalisation, algorithmes UNDO et REDO

• Contrôle de concurrence – Problèmes d’exécution simultanée de transactions – Algorithmes de verrouillage

7

Sérialisabilité

• L’exécution de plusieurs transactions est sérialisable si elle est équivalente à une exécution séquentielle des différentes transactions. – Garantit la correction du résultat global des mises à jour (ex.

réservation de places) – Verrouillage des données pour assurer la sérialisabilité – Différents algorithmes de contrôle de concurrence (verrouillage deux-

phases, estampillage) – Peut affecter les performances du système

• Dans le standard SQL, toutes les transactions doivent être sérialisables. Pour des raisons de performance, certains SGDB autorisent l’exécution de transactions non sérialisables

8

Atomicité

• Certaines transactions se terminent anormalement (panne, erreur, blocage, violation de contrainte, …).

• Pour éviter les incohérences, le SGBD garantit l’atomicité des

transactions: une transaction doit être exécutée entièrement ou pas du tout.

• Une transaction arrêtée avant la fin est « défaite » (rollback) :

les opérations déjà effectuées sont annulées.

9

Cohérence

• Une transaction préserve la cohérence des objets qu’elle manipule. – La base est cohérente à l’état initial et à l’état final – Les contraintes d’intégrité sont vérifiées après les mises à

jour. Si une contrainte est violée, la transaction est annulée. – La cohérence ne porte que sur le contenu d’une transaction.

C’est le programmeur qui doit préserver la cohérence (fonctionne si les propriétés A, I et D sont assurées par le SGBD).

10

Isolation

• Les effets d’une transaction sont invisibles aux transactions concurrentes. – Permet d’éviter les interférences entre transactions (protège

les transactions des effets des transactions concurrentes) – Ex: virement bancaire

• Une exécution sérialisable garantit la propriété d’isolation.

11

Durabilité

• Les effets d’une transaction validée sont permanents. – Si la transaction a fait un COMMIT, les mises à jour ne

sont pas perdues, et sont écrites dans la base, même en cas de panne.

– Gestionnaire de pannes : journalisation des opérations, algorithme REDO.

• Point fort des SGBD, qui peuvent résister aux pannes,

sans perdre de données et en restituant la base dans un état cohérent.

12

Propriétés ACID et systèmes commerciaux

• Garantir les propriétés ACID des transactions pénalise les performances. Les systèmes commerciaux relâchent souvent la propriété d’isolation.

• Conséquences : – Lecture sale

• Une transaction lit une donnée écrite par une transaction qui n’a pas encore validé.

– Lecture non reproductible • Une transaction lit deux fois la même donnée et obtient des valeurs

différentes. – Lecture fantôme

• Deux évaluations de la même requête donnent des résultats différents, car une autre transaction a inséré de nouveaux n-uplets entretemps.

13

Degrés d’isolation en SQL

Degré Lecture sale Lecture non reproductible

Lecture fantôme

READ UNCOMMITTED

POSSIBLE POSSIBLE POSSIBLE

READ COMMITTED

IMPOSSIBLE POSSIBLE POSSIBLE

REPEATABLE READ

IMPOSSIBLE IMPOSSIBLE POSSIBLE

SERIALIZABLE IMPOSSIBLE IMPOSSIBLE IMPOSSIBLE

14

Transactions dans Oracle

• Une transaction démarre lorsqu’on exécute une instruction SQL qui modifie la base ou le catalogue (DML et DDL). – Ex : UPDATE, INSERT, CREATE TABLE…

• Une transaction se termine dans les cas suivants : – L’utilisateur valide la transaction (COMMIT) – L’utilisateur annule la transaction (ROLLBACK sans SAVEPOINT) – L’utilisateur se déconnecte (la transaction est validée) – Le processus se termine anormalement (la transaction est défaite)

• Amélioration des performances dans Oracle : – Niveaux d’isolation – Contrôle de concurrence multiversion

• Verrouillage

15

Commandes transactionnelles

• COMMIT – Termine la transaction courante et écrit les modifications dans la base. – Efface les points de sauvegarde (SAVEPOINT) de la transaction et

relâche les verrous. • ROLLBACK

– Défait les opérations déjà effectuées d’une transaction • SAVEPOINT

– Identifie un point dans la transaction indiquant jusqu’où la transaction doit être défaite en cas de rollback.

– Les points de sauvegarde sont indiqués par une étiquette (les différents points de sauvegarde d’une même transaction doivent avoir des étiquettes différentes).

16

SET TRANSACTION

• SET TRANSACTION – Spécifie le comportement de la transaction :

• Lectures seules ou écritures (READ ONLY ou READ WRITE)

• Établit son niveau d’isolation (ISOLATION LEVEL) • Permet de nommer une transaction (NAME)

• Cette instruction est facultative. Si elle est utilisée, elle doit être la première instruction de la transaction, et n’affecte que la transaction courante.

17

READ ONLY et READ WRITE

• SET TRANSACTION READ ONLY – La transaction devient en lecture seule (pas d’INSERT,

UPDATE, DELETE) – Garantit la cohérence en lecture pour toute la transaction.

Cette transaction voit seulement les modifications de la base effectuées avant le début de la transaction.

– Utile pour des transactions qui font beaucoup de lectures successives sur des objets modifiés simultanément par d’autres utilisateurs.

• SET TRANSACTION READ WRITE – Option par défaut.

18

Exemple

COMMIT; % assure que l’instruction set transaction est la première de la transaction

SET TRANSACTION READ ONLY NAME ‘Toronto’;

SELECT product_id, quantity_on_hand FROM inventory WHERE warehouse_id=5;

COMMIT; % termine la transaction READ ONLY

19

Niveaux d’isolation

• Oracle propose deux niveaux d’isolation, pour spécifier comment gérer les mises à jour dans les transactions – SERIALIZABLE – READ COMMITTED

• Définition – Pour une transaction :

• SET TRANSATION ISOLATION LEVEL SERIALIZABLE • SET TRANSATION ISOLATION LEVEL READ COMMITTED

– Pour toutes les transactions à venir (dans une session) • ALTER SESSION SET ISOLATION LEVEL = SERIALIZABLE; • ALTER SESSION SET ISOLATION LEVEL = READ

COMMITTED;

20

ISOLATION LEVEL SERIALIZABLE

• L’exécution est équivalente à l’exécution séquentielle • Empêche de modifier une ressource mise à jour par une transaction

non encore validée. • Pas de lecture sale, pas de lecture non reproductible, pas de lecture

fantôme • Pénalisant pour les performances • Utilisation :

– grandes BD avec nombreuses transactions courtes et mises à jour de quelques n-uplets seulement.

– Peu de transactions concurrentes (modifiant les même données) – Lorsque les transactions longues sont essentiellement en lecture

21

ISOLATION LEVEL READ COMMITTED

• Option par défaut • Les verrous en lecture sont relâchés dès la fin de la

consultation de l’objet, sans attendre la fin de la transaction • Évite les lectures sales • Si la transaction contient une instruction qui nécessite un

verrou de tuple tenu par une autre transaction, il faut attendre que ce verrou soit libéré pour pouvoir continuer

• Utilisation :

– Peu de transactions concurrentes

22

Contrôle de cohérence multiversion

Permet d’éliminer des conflits de données, de réduire les verrous mortels, et les conflits de verrous.

Ex: Mise à jour concurrente de la relation Authors(au_id, phone, …) Session 1 : UPDATE authors SET phone = ’01 23 45 67 89’ WHERE au_id = ‘123’ Session 2 : SELECT * FROM authors Pour lire le n-uplet modifié par la session 1, la session 2 doit

attendre la fin de la transaction de la session 1.

23

Contrôle de cohérence multiversion Principe : maintenir les données de l’état précédent (version) jusqu’à

la validation. Les requêtes lisent l’ancienne version, et ne sont pas obligées d’attendre la fin de la transaction qui fait les mises à jour.

Données de Authors (après le commit de la session 1):

Au_id Phone …

123 01 23 45 67 89 …

456 99 88 77 66 55 … … … …

Données temporaires (pendant le déroulement de la session 1) :

Au_id Phone …

123 98 76 54 32 10 … 456 99 88 77 66 55 … … … …

24

Résultat

Au_id Phone … 123 98 76 54 32 10 …

456 99 88 77 66 55 …

… … …

Résultat de la session 2 :

Quand la requête de la session 2 lit les données modifiées par une transaction non encore terminée, c’est l’ancienne version qui est effectivement lue. Les données en rouge sont lues de la table temporaire.

25

Mise en œuvre dans Oracle

• Chaque transaction a une estampille (SCN : system change number), affectée à l’exécution.

• La transaction ne lit que des données dont l’estampille est inférieure à la sienne.

• Si une donnée a une estampille supérieure (elle a été modifiée depuis le début de la transaction), le système reconstruit la valeur précédente à l’aide du journal (rollback segment).

• Garantit que chaque lecture est cohérente (par rapport aux données validées avant son lancement). Les modifications effectuées par d’autres transactions initiées après le début de celle-ci ne sont pas vues.

26

27

Verrouillage en Oracle

• Oracle utilise 2 modes de verrouillage : • Verrous exclusifs (Exclusive locks) : pour modifier

des données • Verrous partagés (Shared locks) : plusieurs

transactions peuvent partager des données, sans qu’aucune ne puisse les modifier.

• Les verrous sont relâchés après le COMMIT ou le ROLLBACK.

28

Verrous mortels

29

Verrous de tuple

Pour contrôler l’isolation, Oracle dispose de plusieurs types de verrous (verrous de tuple et verrous de table)

Une transaction acquiert un verrou exclusif pour chaque tuple modifié par INSERT, UPDATE, DELETE, SELECT ..FOR UPDATE

Les verrous de tuple, en combinaison avec la cohérence multiversion, – Permettent de lire une donnée sans attendre, même si une

autre transaction écrit une donnée du même tuple. – Evitent les blocages des écritures par les lectures des

mêmes données, sauf en cas de SELECT … FOR UPDATE

30

Verrous de table

Une transaction acquiert un verrou de table pour chaque relation modifiée par une instruction INSERT, UPDATE, DELETE SELECT .. FOR UPDATE et LOCK TABLE.

Permet de réserver les accès du DML pour une transaction donnée et empêche les opérations du DDL.

Plusieurs modes : Row share (RS), row exclusive (RX), share (S), share row exclusive (SRX) et exclusive (X)

Le mode de verrouillage détermine les types de verrous que d’autres transactions peuvent détenir sur la même table.

Ex: Si T1 possède un verrou RX pour un INSERT, d’autres transactions peuvent poser des verrous RS et RX, mais pas S, SRX, X.

31

ROW SHARE (RS)

• La transaction qui possède le verrou a verrouillé des tuples avec l’intention de les modifier.

• Ce verrou est posé après les instructions : – SELECT .. FROM table … FOR UPDATE OF …; – LOCK TABLE table IN ROW SHARE MODE;

• Opérations permises par les autres transactions : select, insert, update, delete, lock rows (RS, RX, S SRX)

• Opérations interdites : verrou exclusif (X)

32

ROW EXCLUSIVE (RX)

• La transaction qui tient le verrou a fait des mises à jour sur les tuples de la relation.

• Acquis après les instructions : – INSERT INTO table…; – UPDATE table …; – DELETE FROM table …; – LOCK TABLE table IN ROW EXCLUSIVE MODE;

• Opérations permises : select, insert, update, delete, lock rows • Opérations interdites : verrouiller la table en modes share

(S), share exclusive (SX), et exclusive (X).

33

SHARE (S)

• Acquis pour la table spécifiée dans l’instruction : LOCK TABLE table IN SHARE MODE;

• Opérations permises : select, verrouiller des tuples spécifiques avec SELECT … FOR UPDATE, et LOCK TABLE … IN SHARE MODE.

• Opérations interdites : toutes les mises à jour, et le verrouillage de la même table en mode SHARE ROW EXCLUSIVE, EXCLUSIVE, ROW EXCLUSIVE.

34

SHARE ROW EXCLUSIVE (SRX)

Avoir en même temps un verrou partagé sur la table (S) et un verrou exclusif sur un tuple (RX).

Acquis par l’instruction : LOCK TABLE table IN SHARE ROW EXCLUSIVE; Opérations permises : select et verrouillage de tuples

avec la clause SELECT .. FOR UPDATE. Opérations interdites : verrouiller la table en modes

SHARE, SHARE ROW EXCLUSIVE, ROW EXCLUSIVE, EXCLUSIVE

35

EXCLUSIVE (X)

Le mode Exclusive est le plus restrictif. Il est obtenu par l’instruction :

LOCK TABLE table IN EXCLUSIVE MODE; Opérations permises : select Opérations interdites : toutes les autres.

36

Conclusion

• Maintien de la cohérence : – Compromis entre cohérence et performance

• Propositions d’Oracle : – Contrôle de concurrence multiversion – Niveaux d’isolation – Mécanismes de verrouillage pour limiter les

verrous mortels et améliorer les performances.