Blog ENI : Toute la veille numérique !
💥 Un livre PAPIER acheté
= La version EN LIGNE offerte pendant 1 an !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL
  3. Le contrôle de transactions (TCL)
Extrait - SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
Extraits du livre
SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
3 avis
Revenir à la page d'achat du livre

Le contrôle de transactions (TCL)

Problématique des accès concurrents

Dans la majorité des développements informatiques se pose la question des accès simultanés à une donnée par plusieurs utilisateurs différents.

En effet, un développeur d’application doit prévoir la gestion des accès concurrents en utilisant les outils fournis par la base de données.

La majorité des SGBDR autorisent la réservation de données avant mise à jour afin d’empêcher un autre utilisateur de modifier cette même donnée avant que le premier n’ait validé sa modification.

1. Illustration des accès concurrents

a. Exemple 1 : mises à jour simultanées

Reprenons la table Tarifs :

SELECT * FROM Tarifs; 

idTarif

Hotel

typeChambre

DateDebut

DateFin

Prix

1

1

1

2024-10-01

2025-04-14

49,99

2

1

2

2024-10-01

2025-04-14

59,99

3

1

3

2024-10-01

2025-04-14

68,99

4

1

4

2024-10-01

2025-04-14

59,99

5

1

5

2024-10-01

2025-04-14

69,99

Maintenant un utilisateur lit l’enregistrement numéro 2 puis réalise une modification de la table en ajoutant 10 € au tarif du type de chambre numéro 2 de l’hôtel numéro 1.

Au même moment, un autre utilisateur ajoute également 15 € à cet enregistrement numéro 2.

UTILISATEUR 1

Valeur PRIX

UTILISATEUR 2

Valeur PRIX

LECTURE Tarifs NUMERO 2

59,99

MISE A JOUR PRIX = PRIX + 10

69,99

LECTURE Tarifs NUMERO 2

59,99

MISE A JOUR PRIX= PRIX + 15

74,99

SAUVEGARDE ENREGISTREMENT NUMERO 2

69,99

SAUVEGARDE ENREGISTREMENT NUMERO 2

74,99

À l’issue de ce petit scénario, la valeur finale du prix de la chambre sera de 74,99 €. Il aurait dû être de 84,99 €. En effet, si les deux transactions s’étaient déroulées l’une après l’autre, cela aurait donné :

59,99 + 10 = 69,99 puis 69,99 + 15 = 84,99 €

En conséquence, l’utilisateur 1 pense avoir ajouté 10 € au prix et le deuxième a effectivement ajouté les 15 € à son prix.

L’utilisateur 2 n’a pas pris en compte la modification du premier utilisateur qui passe le prix à 69,99. L’ajout des 15 € se serait appliqué à 69,99 et non à 59,99.

b. Exemple 2 : incohérence des données suite à une modification d’un...

Notion de transaction

Afin de limiter les problématiques indiquées dans les paragraphes précédents, il faut que le développeur se pose la question : quels sont les objets (ligne, colonne, table…) que je manipule dans mon traitement et comment éviter qu’un autre utilisateur ne puisse les atteindre avant que j’aie terminé mes mises à jour ?

À ce moment, on commence à parler de « transaction ».

1. Définition d’une transaction

La transaction permet de borner le début et la fin d’une action dans la base, sur une ou plusieurs tables, qui doivent rester cohérentes.

La transaction est une notion qui vient des applications dites « transactionnelles ». À chaque fois qu’il existe un dialogue écran entre l’application et l’utilisateur, on parle d’application transactionnelle.

Toutes les applications utilisent des mécanismes de verrouillage.

2. Comment éviter les incohérences de données

Plusieurs méthodes existent pour garantir une cohérence dans la base :

  • Lancer les transactions les unes derrière les autres (en série). L’inconvénient principal est le temps d’attente très important pour les utilisateurs. Cela revient à avoir une application mono-utilisateur !

  • Possibilité de bloquer en début de programme principal tous les objets implicitement puis les libérer à la fin. Cette méthode risque de bloquer les autres utilisateurs trop longtemps si l’utilisateur ne valide pas rapidement son écran et part en réunion quelque temps.

  • Pas de verrouillage des enregistrements en début mais lecture des données puis sauvegarde en mémoire de leur contenu puis relecture juste avant la mise à jour pour s’assurer que les données n’ont pas été modifiées entre temps. Si les valeurs sauvegardées sont différentes des valeurs relues, alors abandon de la transaction et affichage d’un message à l’utilisateur pour signaler le problème et lui indiquer qu’il doit ressaisir les éléments.

3. Mise en œuvre d’un verrouillage

Selon la norme SQL-92, on peut indiquer à la base de données quel type de méthode on...

Exercice

  • Créer une transaction qui vide la table FILM.

  • Puis compter le nombre d’actrices Carrie Fisher.

  • Insérer dans la table FILM le film Subway, genres POLICIER et DRAME, sorti le 10 avril 1985 en France, du réalisateur Luc Besson, distribué par GAUMONT avec ce résumé : « Conte les aventures de la population souterraine dans les couloirs du métro parisien ».

  • Créer un point de synchronisation.

  • Insérer dans la table FILM le film Nikita, genres DRAME et ROMANTIQUE, sorti le 21 février 1990 en France, du réalisateur Luc Besson, distribué par GAUMONT avec ce résumé : « Nikita condamnée à la prison à perpétuité est contrainte à travailler secrète-ment pour le gouvernement en tant que agent hautement qualifié des services secrets ».

  • Supprimer l’actrice Carrie Fisher.

  • Sélectionner les films du réalisateur Luc Besson sortis entre le 1er janvier 1985 et le 30 mai 1985, trier par titre de film.

  • Positionner un second point de synchronisation.

  • Insérer dans la table FILM le film Subway, genres POLICIER et DRAME, sorti le 10 avril 1985 en France, du réalisateur Luc Besson, distribué par GAUMONT avec ce résumé : « Conte les aventures de la population souterraine dans les couloirs du métro parisien »....

Solution de l’exercice

Syntaxe Oracle

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
  
/* Vidage de la table FILM */  
DELETE FROM FILM;  
 
SELECT COUNT(*) FROM ACTEUR WHERE NOM = 'FISHER' AND PRENOM =  
'CARRIE'; 
 
INSERT INTO FILM VALUES (1,'SUBWAY','POLICIER','DRAME',  
TO_DATE('10/04/1985','DD/MM/YYYY'),1, 1, 'GAUMONT',  
'Conte les aventures de la population souterraine dans les  
couloirs du métro parisien'); 
 
/* Positionnement du premier point */  
SAVEPOINT ETAPE_NUMERO1;  
INSERT INTO FILM VALUES (2,'NIKITA','DRAME','ROMANTIQUE',  
TO_DATE('21/02/1990','DD/MM/YYYY'),1,1,'GAUMONT',  
'Nikita condamnée à la prison à perpétuité est contrainte à  
travailler secrètement pour le gouvernement en tant que agent  
hautement qualifié des services secrets.');  
 
DELETE FROM ACTEUR WHERE NOM = 'FISHER' AND PRENOM = 'CARRIE';  
 
SELECT T1 * FROM FILM T1 INNER JOIN REALISATEUR ON T1.IDENT_ 
REALISATEUR = REALISATEUR.IDENT_REALISATEUR 
WHERE PRENOM = 'LUC' AND DATE_SORTIE BETWEEN ('01/01/85') AND  
('30/05/1995')  
ORDER BY TITRE; 
 
/* Positionnement du deuxième point */ 
SAVEPOINT ETAPE_NUMERO2; 
 
INSERT INTO FILM VALUES (3,'SUBWAY','POLICIER','DRAME',  
TO_DATE('10/04/1985','DD/MM/YYYY'),1,1,'GAUMONT',  
'Conte les aventures de la population souterraine dans les  
couloirs du métro parisien');  
 
/* Retour au point numéro...