gestion de transactions concurrentescosy.univ-reims.fr/~lsteffenel/public_html/sgbd-duts3/10...31...

40
LA CONCURRENCE Objectifs Les mécanismes de verrouillage Le verrouillage sous Oracle Exercices

Upload: others

Post on 20-Jan-2020

3 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

LA CONCURRENCE

ObjectifsLes mécanismes de verrouillageLe verrouillage sous OracleExercices

Page 2: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

2

1. ObjectifsPermettre l’exécution simultanée d’un grand

nombre de transactionsRégler les conflits lecture / écritureGarder de très bonne performanceÉviter les blocages

Page 3: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

3

Récapitulatif sur les transactions

Transaction : • possibilité d'exécuter intégralement un bloc

d'opérations, • ou alors défaire les modifications si abort

En théorie :• les transactions sont effectuées en séquence, la

consistance des données est garantie

En pratique :• l'exécution de transactions en séquence est très peu

efficace• temps d'attente peut devenir insupportable

Page 4: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

4

Exemples

Opérations bancaires• transactions séquentielles empêchent deux

utilisateurs de travailler sur leurs comptes

Réservation de billets d'avion• la réservation de toutes les places de l'avion est

bloquée tant qu'une transaction d'achat ne finit pas (entrée données utilisateurs, payement, etc.)

Opérations de longue durée• transaction pour créer le rapport financier d'une

banque lors des 12 derniers mois.

Page 5: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

5

Solution : relaxer les contraintes

Afin d'optimiser l'exécution des transactions, nous allons permettre l'exécution concurrente de certaines opérations

• permettre le travail concurrent sur des données distincts

• permettre un contrôle de concurrence sur les données partagées

Page 6: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

6

Deux transactions qui effectuent des operations sur un compte A

1.lire (A)2.A=A+1003.écrire (A)

Dans certaines conditions les transactions peuvent coexister sans aucun probleme

Transactions Parallèles

1.lire (A)2.si a < 50 trigger

ALERT

Page 7: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

7

L'application Bar à Bière PAUL

1.SELECT MAX(prix) FROM BarÀBière WHERE bar='Jo'

2.SELECT MIN(prix) FROM BarÀBière WHERE bar='Jo'

Problèmes d'Incohérence

FRED1.DELETE FROM BarÀBière

WHERE bar='Jo'2.INSERT INTO BarÀBière

VALUES(‘Jo’, ‘Kwak’, 3.50);

Jo

Jo

Moe

Heinekein

Budweiser

Duffy

2,50

3.00

5,00

Page 8: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

8

Les problèmes de concurrence

Perte de mises à jour• Lorsqu'une transaction écrase les valeurs écrits par

une autre transaction

1.lire (A)

3.A=A+250

4.écrire (A)

1.lire (A)

2.A=A+250

3.écrire (A)

BaseA=10

A=260A=110

Page 9: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

9

Les problèmes de concurrence

Lecture impropre• Lorsqu'une transaction accède à des valeurs qui

sont modifiées et postérieurement rétractées

1.

1.lire (A)

1.lire (A)2.A=A+2503.écrire (A)

4.Rollback

BaseA=10

A=260A=10

Page 10: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

10

Les problèmes de concurrence

Lecture non reproductibles• Lorsqu'une valeur est modifiée entre deux lectures

différentes

1.lire (A)1.lire (A)2.A=A+2503.écrire (A)

BaseA=10

A=2602.lire (A)

Page 11: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

11

2. Les mécanismes de verrouillage

Idée : identifier les accès concurrents à des ressources et signaler à travers un verrou que ce donnée pourra être modifiée

• empêche certaines transactions d'avancer avant qu'une donnée soit mise à jour

• limite les dégâts (effet domino) lors des rollbacks

Page 12: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

12

Exemple d'utilisation de verrousT1 T2

lock(c1)

read(c1) for update

c1:=c1-m1 T2 attend la libération de c1

écrire(c1) (demande accès pour update de c1)

lock(c2) "

read(c2) for update "

c2:=c2+m1 "

écrire(c2) "

unlock(c1,c2) lock(c1)

read(c1) for update

c1:=c1-m2

écrire(c1)

lock(c3)

read(c3) for update

c3:=c3+m2

écrire(c3)

unlock(c1,c3)

Page 13: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

13

Verrouillage binaire

Principe :

• les données sont marquées d'un verrou

0 – libre1 – verrouillé

• une opération ne peut se produire si elle a préalablement acquis le verrou sur un élément X

• si le verrou est à 1 la transaction doit attendre

Page 14: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

14

Verrouillage binaire

Désavantages

• le verrouillage binaire est trop restrictive

• parfois certaines transactions ne font que lire des données

T1 T2lire (taux) lire(taux)A=A*taux B=B*tauxécrire(A) écrire(B)

Page 15: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

15

Verrouillage binaire

Avec un verrouillage binaire

T1 T2verrouiller(taux) attend libération de tauxlire(taux)A=A*tauxécrire(A)libérer(taux) verrouiller(taux) lire(taux) B=B*taux écrire(B) libérer(taux)

Page 16: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

16

Verrouillage partagée X exclusive

Principe :

• les données sont marquées d'un verrou

0 – libre s – partagéex - exclusive

• un verrou partagée permet la lecture concurrente des données, mais empêche l'écriture sur les éléments

Page 17: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

17

Problèmes de concurrence

Interblocage (deadlock)• chaque transaction T d’un ensemble de n (n ≥ 2)

transactions attend une donnée verrouillée par une autre transaction T’ de l’ensemble

T1 T2

lock(c1)

read(c1)

c1:=c1+m lock(c2)

écrire(c1) read(c2)

c2:=c2+n

T1 attend libération de c2 écrire(c2)

" T2 attend libération de c1

" "

" "

Page 18: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

18

Solution pour le deadlock

Graphes d'attente de ressources • le système maintient un graphe avec la liste de

ressources en attente des transactions• chaque fois que le système détecte un cycle il tue

une ou plusieurs transactions afin de débloquer l'exécution

T1 T2

T2

T1 T3

Page 19: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

19

Niveaux d'Isolation

Le standard SQL définit 4 niveaux d'isolation pour les verrous

SERIALIZABLE REPEATABLE READ READ COMMITTED

READ UNCOMMITTED

consistance fléxibilité /performance

Page 20: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

20

Serializable

Les transactions sont effectuées les unes après les autres, de manière séquentielle• évite tout problème d'inconsistance des données• très contraignant pour la performance

Exemple – Bar à bières

PAUL1.SELECT MAX(prix) FROM

BarÀBière WHERE bar='Jo'2.SELECT MIN(prix) FROM

BarÀBière WHERE bar='Jo'

FRED1.DELETE FROM BarÀBière

WHERE bar='Jo'2.INSERT INTO BarÀBière

VALUES(‘Jo’, ‘Kwak’, 3.50);

Page 21: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

21

Repeatable read

Seulement les données valides seront accessibles• évite les lectures non reproductibles• l'application peut avoir accès à des nouveaux tuples

ou des tuples qui ont été effacés

Exemple – Bar à bièresordre d'exécution : (max)(del)(ins)(min)

• max voit 2,50 et 3,00min voit 2,50, 3,00 et aussi 3,50

Page 22: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

22

Read committed

Seulement les données valides seront lues, mais des lectures successives peuvent rendre des valeurs (validées) différentes• permet de voir les mises à jour des autres processus

Exemple – Bar à bièresordre d'exécution :

(max)(del)(ins)(commit)(min)• max voit 2,50 et 3,00

min voit 3,50

Page 23: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

23

Read uncommitted

Les transactions ont accès aux valeurs dès leur modification• même si les valeurs ne sont pas validées (abort)

Exemple – Bar à bièresordre d'exécution : (max)(del)(ins)(abort)(min)

• max voit 2,50 et 3,00min voit 3,50

Page 24: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

24

Verrouillage et niveau de granularité

Le verrouillage peut s'appliquer à des différents niveaux de la base• attributs d'un tuple• un ou plusieurs tuples• une ou plusieurs tables• toute la base

Le plus étendu le verrou le plus contraignant est l'accès des transactions concurrentes• les transactions restent à l'attente des la libération

des verrous

Page 25: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

25

Verrouillage et niveau de granularité

attributs d'un tupleun ou plusieurs tuplesune ou plusieurs tables

toute la base

Il est important de choisir la granularité la plus adaptée à chaque type d'accès aux données

accèsconcurrent

complexité

Page 26: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

26

3. Les verrouillages sous Oracle

Oracle implémente par défaut un mécanisme d'accès consistant en mode lecture• ready consistency• une transaction n'a accès qu'aux données validées

lorsqu'elle a commencé (Isolation)• les données modifiées après le début d'une

transaction ne sont pas visibles

a=5

a=5

a=a+10

Page 27: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

27

Les verrouillages sous Oracle

Ce mécanisme implémente un verrouillage automatique lors des mises à jour• la première transaction à vouloir modifier une

donnée pose un verrou, les autres attendent la fin de la transaction

Possibilité de créer une transaction spéciale• accès concurrent en lecture seule• permet des lectures non reproductibles SET TRANSACTION READ ONLY ... COMMIT

Page 28: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

28

L'isolation sous Oracle

Oracle supporte seulement deux types d'isolation entre les transactions• accès READ COMMITTED

modèle par défaut

• accès SERIALIZABLE

Commande SQL

SET TRANSACTION ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}

Page 29: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

29

Read committed

Modèle d'isolation standard implémenté par Oracle

• permet la concurrence des transactions

• seulement les données validées sont visibles aux autres transactions

Verrouillage des modifications

• Oracle gère l'accès en écriture en verrouillant les tuples qui seront modifiés

permet l'accès aux anciennes valeurs des tuples

empêche la modification des éléments des tuples verrouillés

Page 30: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

30

Serializable

Les transactions sont effectuées les unes après les autres, de manière séquentielle

Résout le problème des lectures non reproductibles

Très contraignant pour le système

• empêche l'exécution concurrent des transactions

Page 31: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

31

Niveaux de verrouillage avec Oracle

Verrouillage des tuples• implémentation standard d'Oracle• chaque tuple peut être verrouillé individuellement (et

pour des transactions différentes)• l'accès par d'autres transactions se fait en lecture

seule (avec les valeurs précédentes à la transaction)

Mécanisme de verrouillage• un verrou DML (data manipulation language)

empêche d'autres transactions d'écrire sur le tuple

• un verrou DDL (data dictionary language) sur la table empêche la modification de la structure de la table

• ces verrous ne sont relâchés qu'après un commit ou rollback

Page 32: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

32

Niveaux de verrouillage avec Oracle

Verrouillage des tables• seule la transaction qui a posé le verrou peut modifier

la table• l'accès par d'autres transactions se fait en lecture

seule

Relâche des verrous• il est important de relâcher les verrous lorsqu'on ne les

utilise plus évite des interblocages permet aux autres transactions d'avancer

• effectuer des COMMIT ou ROLLBACK dès que possible

Page 33: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

33

Les types de verrouillage

Oracle gère les types de verrouillage suivants :

• row share (RS)

• row exclusive (RX)

• share (S)

• share row exclusive (SRX)

• exclusive (X)

Certains de ces types s'appliquent automatiquement lors des requêtes SQL

Page 34: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

34

Les types de verrouillage - aucun

Aucun verrou

• lors d'une requête SELECT simple le système ne pose aucun verrou

on suppose une opération de lecture seule

• tout contrôle dépend du mode d'accès permis aux données visualisées

Page 35: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

35

Les types de verrouillage - RS

Row Share (RS)

• le type le moins restrictif

• une transaction pose le verrou sur une tuple

• opérations qui posent un RS

SELECT … FROM table … FOR UPDATE OF … ;

LOCK TABLE table IN ROW SHARE MODE [NOWAIT];

• les autres transactions ont le droit de consulter les données de cette tuple de verrouiller d'autres tuples dans la même table de poser des verrous de type RS, RX, S et SRX

• ce verrou sert à empêcher la pose d'un verrou X avant que votre mise à jour soit effectuée

Page 36: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

36

Les types de verrouillage - RX

Row Exclusive (RX)

• opérations qui posent un RX

INSERT INTO table ... ;

UPDATE table ... ;

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE [NOWAIT];

• les autres transactions ont le droit de consulter les données de cette tuple de verrouiller d'autres tuples dans la même table de poser des verrous de type RX, S et SRX

• ce verrou sert à empêcher une requête de verrouiller la table entière (en lecture ou écriture)

Page 37: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

37

Les types de verrouillage - S

Share (S)

• opérations qui posent un S

LOCK TABLE table IN SHARE MODE [NOWAIT];

• les autres transactions ont le droit

de consulter les données de cette table

de verrouiller em mode share certaines tuples

de poser des verrous de type S

• aucun verrou plus restrictif (X, RX, SRX) n'est permis

• la mise à jour sur cette table dépend du fait que aucune autre transaction a des verrous sur la table

Page 38: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

38

Les types de verrouillage - SRX

Share Row Exclusive (SRX)

• opérations qui posent un SRX

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE [NOWAIT];

• un seul verrou SRX est permis dans une table

• donne aux autres transactions seulement le droit de lecture (sauf des verrous SR avec SELECT...FOR UPDATE)

• la mise à jour sur cette table ne peut être faite que pour la transaction qui détient le verrou

Page 39: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

39

Les types de verrouillage - X

Exclusive (X)

• opérations qui posent un X

LOCK TABLE table IN EXCLUSIVE MODE [NOWAIT];

• un seul verrou X est permis dans une table

• donne aux autres transactions seulement le droit de lecture

• garantit les propriétés de lecture cohérente

Page 40: GESTION DE TRANSACTIONS CONCURRENTEScosy.univ-reims.fr/~lsteffenel/public_html/SGBD-DUTS3/10...31 Niveaux de verrouillage avec Oracle Verrouillage des tuples •implémentation standard

40

Exercices

TD Verrous

TP Verrouillage et concurrence d'accès sur Oracle