Module 140

Administrer et exploiter des bases de données

Jour 11 - Verrous et révisions

# Objectifs du jour

Objectifs du jour

  • Fin des présentations des problèmes d'isolation
  • Niveaux d'isolation

  • Verrous

  • Révision commune - Quiz

  • Mise à jour du mindmap

  • Formulaire d'évaluation du module

  • Révisions générales

Exercice 8.2

Exercice en groupe de présentation
des problèmes de concurrence des
données :

Concurrence des données

# Notions ACID des SGBDRs

# Notions ACID des SGBDRs

Une transaction lit des données modifiées, mais non validées par une autre transaction. Si cette dernière est annulée (ROLLBACK), la première transaction aura utilisé des données incorrectes.

Lecture sale (Dirty Read)

Session 1 :
-- Démarrage de la transaction
UPDATE comptes SET solde_compte = solde_compte - 500 WHERE code_compte = 'IV001';
-- Pas encore de COMMIT, les changements ne sont pas définitifs

Session 2 :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT solde_compte FROM comptes WHERE code_compte = 'IV001';
-- Ici, on voit le solde diminué alors que la transaction en Session 1 n'est pas validée !
Session 1 :
ROLLBACK;
-- La modification est annulée, mais la Session 2 a vu des données erronées.

  Comment s'en prémunir ?

  • Mettre l'isolation en READ COMMITED
  • Utiliser des verrous explicites (LOCK TABLE) pour éviter qu'une autre transaction accède aux données en cours de modification.

# Notions ACID des SGBDRs

Une lecture non répétable survient lorsqu'une transaction lit deux fois la même donnée, mais qu'entre-temps, une autre transaction l’a modifiée et validée.

Lecture non répétable

Session 1 :
SELECT solde_compte FROM comptes WHERE code_compte = 'CC001';
-- Résultat : CHF 1000
Session 2 :
UPDATE comptes SET solde_compte = solde_compte - 200 WHERE code_compte = 'CC001';
COMMIT;
Session 1 :
SELECT solde_compte FROM comptes WHERE code_compte = 'CC001';
-- Résultat : CHF 800 (différent de la première lecture)

  Comment s'en prémunir ?

  • Mettre l'isolation en REPEATABLE READ
  • Utiliser des verrous explicites (SELECT FOR UPDATE ou LOCK TABLE) pour éviter qu'une autre transaction accède aux données en cours de modification.

# Notions ACID des SGBDRs

Une lecture fantôme se produit lorsqu'une transaction lit un ensemble de lignes avec une condition donnée, puis une autre transaction insère de nouvelles lignes correspondant à cette condition avant que la première transaction ne se termine.

Lecture fantôme

Session 1 :
SELECT * FROM comptes WHERE solde_compte > 500;
-- 3 comptes affichés
Session 2 :
INSERT INTO comptes (code_compte, solde_compte) VALUES ('CC002', 600);
COMMIT;
Session 1 :
SELECT * FROM comptes WHERE solde_compte > 500;
-- 4 comptes affichés au lieu de 3 !

  Comment s'en prémunir ?

  • Mettre l'isolation en SERIALIZABLE
  • Utiliser des verrous explicites (LOCK TABLE) pour éviter qu'une autre transaction accède aux données en cours de modification.

# Notions ACID des SGBDRs

Un interblocage survient lorsque deux transactions se bloquent mutuellement, chacune attendant qu'une ressource détenue par l'autre soit libérée.

Interblocage (Deadlock)

Session 1 :
UPDATE comptes SET solde_compte = solde_compte - 100 WHERE code_compte = 'EP001';
-- Transaction non validée (en attente)
Session 2 :
UPDATE comptes SET solde_compte = solde_compte - 200 WHERE code_compte = 'CC001';
-- Transaction non validée (en attente)

Comment s'en prémunir ?

  • Modifier les données dans le même ordre
  • Utilisation des timeout WAIT ou NOWAIT pour retourner une erreur
Session 1 :
UPDATE comptes SET solde_compte = solde_compte - 100 WHERE code_compte = 'CC001';
-- BLOQUÉ et en attente car détenu par Session 2
Session 2 :
UPDATE comptes SET solde_compte = solde_compte - 200 WHERE code_compte = 'EP001';
-- BLOQUÉ car détenu par Session 1 → DEADLOCK !

# Notions ACID des SGBDRs

Niveaux d'isolation

Les SGBD utilisent les niveaux d’isolation pour gérer ces problèmes et assurer l’intégrité des données. Il existe 4 niveaux d'isolation.

Niveau 1 - READ UNCOMMITTED :
Permet de lire des données non validées (ROLLBACK peut rendre les fausses lectures).
Utilisation : Rare, car manque de fiabilité. Pour une lecture rapide et sans contrainte.

 

Niveau 2 - READ COMMITTED :

Une transaction ne peut voir que les données validées (COMMIT).
Utilisation : Oracle, SQL Server par défaut.

# Notions ACID des SGBDRs

Niveau 3 - REPEATABLE READ :
Garantit que si une transaction lit une valeur, elle ne changera pas pendant l’exécution.
Utilisation : MySQL par défaut.

Niveau 4 - SERIALIZABLE :
Empêche tout accès concurrent sur les mêmes données.
Utilisation : Sécurité maximale, mais coûteux en performance.

 

Niveaux d'isolation

Niveau d’isolation Lecture sale Lecture non répétable Lecture fantôme Dead Lock
1 - READ UNCOMMITTED Possible Possible Possible Possible
2 - READ COMMITTED Impossible Possible Possible Possible
3 - REPEATABLE READ Impossible Impossible Possible Possible (fréquent)
4 - SERIALIZABLE Impossible Impossible Impossible Possible (très fréquent)

# Notions ACID des SGBDRs

En oracle, il est possible de changer le niveau d'isolation de la transaction ou de la base de données entière.

Niveaux d'isolation

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ou
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Pour une transaction :

Pour toute la base de données :

ALTER SYSTEM SET TRANSACTION ISOLATION READ COMMITTED;
ou
ALTER SYSTEM SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# Notions ACID des SGBDRs

Les verrous sont essentiels pour garantir l'intégrité des données dans un environnement multi-utilisateurs. Les verrous implicites sont automatiques et simplifient la gestion des transactions, tandis que les verrous explicites offrent plus de contrôle dans des situations spécifiques.

 

Ils assurent que plusieurs transactions ne modifient pas simultanément les mêmes données de manière conflictuelle.

Verrous et Dead Lock

# Notions ACID des SGBDRs

Verrous implicites

Ils sont automatiquement gérés par le SGBD notamment selon le niveau d'isolation. À chaque opération de LMD (Langage de manipulation des données), comme INSERT, UPDATE ou DELETE, le SGBD place des verrous sur les lignes ou les tables concernées.

 

Verrous explicites

Ils sont manuellement définis par le développeur avec des commandes SQL spécifiques comme LOCK TABLE ou SELECT ... FOR UPDATE.

Verrous et Dead Lock

# Notions ACID des SGBDRs

Verrous et Dead Lock

La commande LOCK TABLE permet de verrouiller une table entière dans un mode spécifique (par exemple, mode partagé ou exclusif), empêchant d'autres transactions de lire ou de modifier la table jusqu'à ce que le verrou soit libéré (par la fin de transaction, COMMIT ou ROLLBACK).

LOCK TABLE

LOCK TABLE nom_de_table IN [SHARE | EXCLUSIVE] MODE;

Share : Plusieurs transactions peuvent lire la même donnée en même temps, mais aucune ne peut la modifier tant que le verrou est en place.

Exclusive : Uniquement une transaction peut lire et modifier la donnée verrouillée. Les autres transactions doivent attendre la libération du verrou pour accéder à cette donnée.

# Notions ACID des SGBDRs

Verrous et Dead Lock

LOCK TABLE - Exemple

BEGIN;
LOCK TABLE comptes IN EXCLUSIVE MODE;

UPDATE comptes
SET solde = solde - 100
WHERE pk_compte = 1;

COMMIT;

# Notions ACID des SGBDRs

Verrous et Dead Lock

SELECT FOR UPDATE

SELECT * FROM nom_de_table WHERE condition FOR UPDATE;

La commande SELECT ... FOR UPDATE place un verrou exclusif sur les lignes sélectionnées par la requête. Cela permet de s'assurer qu'aucune autre transaction ne pourra modifier ces lignes tant que la transaction actuelle n'aura pas été validée.

# Notions ACID des SGBDRs

Verrous et Dead Lock

SELECT FOR UPDATE - Exemple

-- Verrouiller le compte source
SELECT solde FROM comptes
WHERE pk_compte = 1
FOR UPDATE;

-- Verrouiller le compte destinataire
SELECT solde FROM comptes
WHERE pk_compte = 2
FOR UPDATE;

-- Effectuer le transfert
UPDATE comptes
SET solde = solde - 100
WHERE pk_compte = 1;

UPDATE comptes
SET solde = solde + 100
WHERE pk_compte = 2;

-- Valider la transaction
COMMIT;

# Notions ACID des SGBDRs

Précisions concernant l'isolation

  • RAISE_APPLICATION_ERROR déclenche automatiquement un Rollback

  • Pas de LCT (COMMIT ou ROLLBACK) dans les triggers car nous sommes déjà dans une transaction

  • LDD (CREATE, ALTER, etc) ou LCD (GRANT, REVOKE) font un commit implicite

  • Certain SGBD comme MySQL sont en autocommit par défaut

  • Tablespace UNDO :

# Notions ACID des SGBDRs

Que retenir de l'isolation ?

  • Il y a des problèmes de concurrences des données entre les transactions
  • Il y a un niveau d'isolation qui peut être changé pour éviter des problèmes, mais au détriment de la performance
  • Dans une transaction, il y a toujours un risque de Dead Lock (plus ou moins important selon le niveau d'isolation)
  • Il y a des verrous implicites (notamment selon le niveau d'isolation) et explicites (SELECT ... FOR UPDATE, LOCK TABLE)

# Quiz

Récapitulation commune (Quiz)

# Mindmap

Mise à jour du Mindmap

# Formulaire d'évaluation

Formulaire d'évaluation du module 140

Merci de votre attention 😊
Bonne révision !

M140 - Jour 11

By paranor

M140 - Jour 11

  • 165

More from paranor