Correction TP3

Étapes :

1. Créer la table :

Créez une table pour les tests dans un éditeur SQL en ligne :

CREATE TABLE compte_bancaire (

id_compte NUMBER PRIMARY KEY,
solde NUMBER
);
****************************************************************************

Explication :

● On crée une table compte_bancaire avec :

○ id_compte comme identifiant unique.

○ solde pour enregistrer le montant disponible.

2. Insérer des données initiales dans une première session.

Ouvrez un onglet (Session A) et insérez des données :

INSERT INTO compte_bancaire VALUES (1, 1000);

INSERT INTO compte_bancaire VALUES (2, 500);
COMMIT;

********************************************************************
Explication

● On insère deux lignes :

○ Le compte 1 avec 1000.

○ Le compte 2 avec 500.

 ● Le COMMIT valide les données : elles sont désormais permanentes et visibles par
toutes les autres sessions.

3. Effectuer une transaction de transfert :

Modifiez des données dans la première session sans COMMIT (pour voir l'effet d'une
transaction).

UPDATE compte_bancaire SET solde = solde - 200 WHERE id_compte = 1;

UPDATE compte_bancaire SET solde = solde + 200 WHERE id_compte = 2;

*******************************

Explication :

● Ces deux commandes simulent un transfert de 200 du compte 1 vers le compte 2.

● Aucune commande COMMIT ou ROLLBACK n’est encore lancée.

● Ces modifications ne sont donc pas encore visibles pour les autres sessions.

4. Ouvrez une deuxième session (dans un nouvel onglet ou une nouvelle fenêtre).

Dans cette session B, faites un SELECT pour voir les données :

SELECT * FROM compte_bancaire;

************************

⇒ Vous ne verrez pas les modifications effectuées dans la première session (Session A)
avant que celle-ci ne fasse un COMMIT.

Explication :

● Depuis la session B (autre onglet ou fenêtre), on exécute un SELECT.

● Résultat attendu :
 ○ Compte 1 : 1000

○ Compte 2 : 500

Pourquoi ?

Parce que la session A n’a pas encore validé les modifications. Les autres sessions ne
voient que les données validées par COMMIT.

5. Effectuez un COMMIT ou un ROLLBACK dans la première session.

⇒ Si vous effectuez un COMMIT dans la session A, les modifications deviennent visibles
pour la session B
⇒ Si vous effectuez un ROLLBACK dans la session A, les modifications sont annulées, et
la session B verra les anciennes valeurs.

Scénario 1

Étape 5a : Exécution d’un ROLLBACK dans la session A

ROLLBACK;

⇒ Ce que vous allez observer après le ROLLBACK :

Le ROLLBACK annule toutes les modifications effectuées dans la transaction en cours, ce

qui rétablit les valeurs d'origine dans la table compte_bancaire. Si vous relancez la requête
SELECT * dans la session B, vous verrez que les comptes n'ont pas été modifiés (compte 1
reste à 1000 et compte 2 reste à 500).

Le ROLLBACK rétablit l'état de la base de données à l'état qu'elle avait avant le début de la
transaction.

Explication :

● Rollback annule toutes les opérations non validées depuis le dernier COMMIT.

● Les soldes sont restaurés :

 ○ Compte 1 revient à 1000.

○ Compte 2 revient à 500.

● La session A et la session B voient maintenant les mêmes valeurs, car la

transaction a été annulée.

Scénario 2

Étape 5b : Refaire les opérations avec COMMIT

1. Refaire les deux UPDATE :

UPDATE compte_bancaire SET solde = solde - 200 WHERE id_compte = 1;

UPDATE compte_bancaire SET solde = solde + 200 WHERE id_compte = 2;

2. Puis, cette fois, exécuter :

COMMIT;

⇒ Une fois le COMMIT effectué, les modifications sont rendues permanentes. Vous pouvez
maintenant voir dans la session B que le solde du compte 1 a été réduit à 800, et celui du
compte 2 a été augmenté à 700.

***********************************

Explication :

● La transaction est confirmée.

● Dans la session B, un nouveau SELECT montrera :

○ Compte 1 : 800

○ Compte 2 : 700
Résumé des comportements observés :

1. Avant le COMMIT :

○ Les modifications effectuées dans la transaction (réduction du

solde du compte 1 et augmentation du solde du compte 2) ne sont pas
visibles dans une autre session tant que COMMIT n’a pas été exécuté.
C’est la propriété d’isolation des transactions.

2. Après un ROLLBACK :

○ Le ROLLBACK annule toutes les modifications effectuées dans la

transaction en cours, rétablissant la base de données à son état d'origine.

3. Après un COMMIT :

○ Le COMMIT rend permanentes les modifications effectuées dans

la transaction. Une fois la transaction validée, les changements
deviennent visibles pour toutes les autres sessions qui effectuent un
SELECT.

Réponses aux questions

1. Que constatez-vous dans la session B (avant COMMIT) ?

Réponse :
Les valeurs n’ont pas changé.

● Compte 1 : 1000

● Compte 2 : 500
⇒ Car la transaction n’est pas encore validée (pas de COMMIT).
 2. Que se passe-t-il si vous faites ROLLBACK dans la session A ?

Réponse :

● Toutes les modifications sont annulées.

● Les comptes retrouvent leurs valeurs initiales : 1000 et 500.

● Les données restent cohérentes.

3. Refaites le test avec COMMIT. Que voyez-vous ?

Réponse :

● Les modifications sont enregistrées définitivement.

● La session B voit maintenant les nouveaux soldes :

○ Compte 1 : 800

○ Compte 2 : 700

● La transaction est visible pour tous car elle est commise.

Résumé pédagogique
Correction de l’exercice 2 :
- Étapes effectuées

Session A (transaction en cours, non validée)

UPDATE compte_bancaire SET solde = solde + 100 WHERE id_compte = 1;
-- NE PAS COMMIT

Session B (autre utilisateur / connexion)

SELECT solde FROM compte_bancaire WHERE id_compte = 1;

Question 1 : La modification est-elle visible dans la session B ? Pourquoi ?

Non, la modification n’est pas visible dans la session B.

Explication :
En SQL (et notamment dans Oracle), tant qu’une transaction n’est
pas validée (pas de COMMIT), ses effets sont invisibles pour les
autres sessions.
Cela illustre le principe d’Isolation : chaque transaction voit l’état
de la base au moment où elle commence, pas les modifications non
validées des autres.

Question 2 : Faites un COMMIT dans A, puis refaites le SELECT dans B.

Que se passe-t-il ?

-- Dans A
COMMIT;

-- Dans B (relancer la requête)

SELECT solde FROM compte_bancaire WHERE id_compte = 1;
Cette fois, la session B voit le nouveau solde (avec les +100 € ajoutés).

Explication :
Dès que la session A fait un COMMIT, les modifications
deviennent visibles pour toutes les autres sessions.
Le SELECT exécuté après ce COMMIT lit donc les données
actualisées.

Question 3 : Que démontre ce comportement sur l'isolation des transactions

?

Ce comportement démontre que :

● Les transactions n’interfèrent pas entre elles tant qu’elles ne sont pas
validées.

● Une transaction ne peut pas lire les modifications non validées d’une
autre.

● Cela évite les lectures sales (dirty reads), assurant la consistance des
lectures.

Ce type d’isolation correspond à :

● Oracle : Read Consistency (niveau READ COMMITTED par défaut)

● Standard SQL : Isolation READ COMMITTED

******************************************************
Résumé du cours :

Les propriétés ACID des transactions en base de données

Les bases de données utilisent les propriétés ACID pour garantir que les
transactions sont fiables et que les données restent cohérentes, même en cas
d’erreur, de coupure de courant ou d’accès simultané.

A – Atomicité

Définition :
Une transaction est une opération indivisible : elle est
entièrement exécutée ou pas du tout.
S’il y a une erreur à un moment donné, aucun changement ne sera
appliqué à la base.

Exemple 1 : Commande en ligne

● Un client achète 3 articles.

● Étapes : vérifier le stock, calculer le total, valider le paiement, enregistrer la

commande.

● Si le paiement échoue, aucune des étapes n’est enregistrée.

Exemple 2 : Inscription à un cours universitaire

● Étapes : vérifier les places, affecter l’étudiant, ajouter à la liste.

● Si une erreur survient, l’étudiant n’est pas inscrit du tout, même si la place
avait été réservée.

C – Cohérence

Définition :
Une transaction respecte toutes les règles définies dans la base
(règles métier, contraintes, intégrité).
Elle maintient un état valide de la base, avant et après l’opération.
 Exemple 1 :
Une règle interdit d’enregistrer deux étudiants avec le même
numéro d’inscription.
Si on essaie d’ajouter un étudiant avec un identifiant déjà utilisé, la
transaction est refusée.

Exemple 2 : Réservation de chambre d’hôtel

Une chambre ne peut être réservée qu’une seule fois par date.

Si deux clients tentent de la réserver au même moment, une seule

réservation sera acceptée selon les règles définies.

Exemple 3 : Système de notes

● Une règle empêche de saisir une note supérieure à 20.

● Si un enseignant essaie d’entrer “21”, la base rejette la transaction.

I – Isolation

Définition :
Les transactions s’exécutent de manière isolée, même si plusieurs
utilisateurs travaillent en même temps.
Une transaction ne voit pas les modifications des autres tant
qu’elles ne sont pas validées.

Exemple 1 :
Deux étudiants modifient simultanément leur profil sur un portail.
Chacun ne voit que ses propres modifications jusqu’à ce qu’il
valide (COMMIT).
Il n'y a pas de mélange ou de conflits entre les deux.

Exemple 2 : Édition d’un document partagé

● Deux utilisateurs modifient des sections différentes d’un même
document dans une application.

● Tant que l’un n’a pas enregistré ses modifications, l’autre ne voit rien
de ce qu’il écrit.

Exemple 3 : Réservations simultanées

● Deux agents saisissent des réservations dans un même système.

● Chaque réservation s’effectue comme si elle était seule, sans interférer

avec l’autre.

D – Durabilité

Définition :
Une fois une transaction validée (COMMIT), les modifications sont
définitives, même en cas de panne de courant ou redémarrage du
système.

Exemple1 :
Un enseignant enregistre les notes d’un examen, puis valide.
Même si le serveur tombe en panne juste après, les notes sont
sauvegardées et récupérables.

Exemple 2 : Publication d’un article

● Un rédacteur publie un article et clique sur “Publier”.

● Même après une panne du serveur, l’article est toujours visible en

ligne.
 Résumé visuel

Propriété Définition simple Exemple concret

Atomicité Tout ou rien Virement entre comptes :

si crédit échoue, débit
annulé

Cohérence Respect des règles Impossible d’inscrire deux

étudiants avec le même ID

Isolation Pas d’interférence Deux utilisateurs

entre transactions modifient un profil sans
conflit

Durabilité Les données sont Une commande reste

sauvegardées après enregistrée après une
validation panne
Correction de l’exercice 3
Situati Propriété Justification
on ACID

1 Atomicité La transaction est annulée entièrement en cas

d’erreur : tout ou rien.

2 Cohérence Le système applique une règle métier (pas de

stock négatif).

3 Isolation Chaque transaction est indépendante et les

effets ne sont visibles qu’après COMMIT.

4 Durabilité La transaction validée reste enregistrée

malgré la panne.

Exercice 4 : Correction complète de l’exercice

ACID

Situation Propriété Justification

ACID

Une transaction Atomicité La transaction est traitée comme une opération

échoue à mi-chemin, indivisible : soit tout est exécuté, soit rien ne
et aucune des l’est. En cas d’échec, tous les changements
opérations n’est partiels sont annulés automatiquement.
appliquée. Exemple : si un virement bancaire échoue après
le débit mais avant le crédit, tout est annulé.
 Deux utilisateurs Isolation L’isolation garantit que les transactions
mettent à jour le s’exécutent indépendamment. Même si deux
même enregistrement utilisateurs accèdent aux mêmes données, leurs
sans se bloquer. opérations ne se perturbent pas tant qu’elles ne
sont pas validées. Cela évite les conflits, les
lectures non confirmées et les résultats
incohérents.

Une mise à jour Durabilité Une fois qu’une transaction est validée
réussie reste présente (COMMIT), ses effets sont permanents, même
même après une en cas de panne. Les bases de données
coupure de courant. garantissent que les données validées ne sont
pas perdues grâce à des mécanismes comme le
log des transactions.

Une règle métier Cohérenc La cohérence impose que la base reste dans un
empêche d’insérer un e état valide avant et après une transaction. Elle
client avec un respecte toutes les règles (clés primaires,
identifiant déjà contraintes, règles métier). Ici, l’interdiction
existant. d’un identifiant dupliqué est une contrainte
d’intégrité.

Correction Exercice 5 : Interblocage et gestion des transactions

Définition

Les interblocages (ou "deadlocks" en anglais) dans Oracle se produisent lorsqu'un groupe de
transactions se bloque mutuellement, chacune attendant que l'autre libère des ressources
dont elle a besoin pour continuer. Cela peut entraîner une situation où aucune des
transactions concernées ne peut progresser, car elles se bloquent les unes les autres
indéfiniment.

Mécanisme des interblocages dans Oracle :

 1. Origine des interblocages :
Un interblocage se produit lorsque deux ou plusieurs transactions ont des
verrous (locks) sur des ressources (comme des lignes ou des tables) et
attendent que l'autre libère une ressource. Par exemple, la transaction A
verrouille la ligne 1 et attend la ligne 2, tandis que la transaction B verrouille
la ligne 2 et attend la ligne 1. Dans ce cas, les deux transactions sont bloquées
et ne peuvent pas continuer.

3. Résolution des interblocages :

Solution 1 : détection des interblocages

Lorsque Oracle détecte un interblocage, il choisit généralement une transaction à
"annuler" (rollback). Cette transaction est "abattue", ce qui libère les ressources et
permet aux autres transactions de continuer. L'annulation d'une transaction entraîne
un message d'erreur signalant qu'un interblocage a eu lieu.
Solution 2 : Prévention des interblocages
Bien que la détection et la résolution automatiques des interblocages par Oracle
soient efficaces, il est toujours préférable de concevoir les transactions de manière à
minimiser le risque d'interblocage. Quelques bonnes pratiques :

● Ordre des verrous : S'assurer que les transactions demandent des verrous sur
les ressources dans le même ordre.

● Durée des transactions : Réduire la durée des transactions pour minimiser la

probabilité qu'elles se bloquent mutuellement.

● Verrouillage explicite : Utiliser des verrous explicites (avec FOR UPDATE)

pour mieux contrôler les ressources verrouillées.

Solution et explications :

1. Que se passe-t-il si vous exécutez ces commandes dans les deux sessions en même
temps ?

Lorsque vous exécutez ces commandes simultanément dans les deux sessions,
Oracle crée un interblocage. Voici pourquoi :
 ● Session A verrouille la ligne où la salle est Salle A, et attend de pouvoir
mettre à jour Salle B.

● Session B verrouille la ligne où la salle est Salle B, et attend de pouvoir mettre

à jour Salle A.

● Résultat : Les deux transactions attendent l'une l'autre et ne peuvent pas

progresser. C’est un interblocage.

2. Qu’est-ce qu’un interblocage dans ce contexte ?

Un interblocage (deadlock) se produit lorsque deux transactions ou plus se bloquent

mutuellement, chacune attendant que l’autre libère une ressource (une ligne de
données dans ce cas) pour pouvoir continuer. Dans ce cas précis :

● La Session A attend un verrou sur Salle B pour continuer.

● La Session B attend un verrou sur Salle A pour continuer.

Aucune des deux transactions ne peut avancer, car elles sont toutes deux bloquées.

3. Comment Oracle gère-t-il ce problème d’interblocage ?

● Oracle détecte automatiquement les interblocages en analysant les

dépendances entre les transactions.

● Lorsqu’un interblocage est détecté, Oracle annule (rollback) l'une des

transactions pour libérer les ressources et permettre à l’autre de continuer.

Dans notre cas, Oracle va annuler soit la Session A soit la Session B, et un message
d'erreur similaire à ceci sera renvoyé :

ORA-00060: deadlock detected while waiting for resource

4. Quelles solutions simples pouvez-vous proposer pour éviter cet interblocage ?

Voici quelques solutions simples pour éviter l’interblocage dans ce scénario :

Verrouiller les lignes dans un ordre fixe :

○ Il est préférable de garantir que toutes les transactions qui accèdent à

plusieurs lignes le fassent dans le même ordre. Par exemple, si une
transaction met à jour d'abord Salle A, puis Salle B, toutes les
transactions doivent suivre cet ordre.

Exemple :

BEGIN;
UPDATE reservations SET date_reservation = TO_DATE('2025-05-12', 'YYYY-MM-
DD') WHERE salle = 'Salle A';
UPDATE reservations SET date_reservation = TO_DATE('2025-05-12', 'YYYY-MM-
DD') WHERE salle = 'Salle B';

○ -- NE PAS COMMIT