Caractéristiques fondamentales d'une transaction : ACID

Pour aborder les niveaux d'isolation, il est essentiel de comprendre les propriétés clés d'une transaction, résumées par l'acronyme ACID :

• Atomicité (Atomicity) : Une transaction est une unité indivisible. Toutes ses opérations réussissent, ou elles sont toutes annulées.
• Cohérence (Consistency) : Une transaction amène la base de données d'un état valide à un autre état valide, préservant toutes les contraintes d'intégrité définies.
• Isolation (Isolation) : Les exécutions concurrentes de transactions sont isolées les unes des autres. C'est le point central que les niveaux d'isolation cherchent à définir.
• Durabilité (Durability) : Une fois une transaction validée (commmit), ses modifications sont permanentes et survivent aux pannes ultérieures.

Niveaux d'isolation des transactions

L'exécution concurrente de transactions sans contrôle adéquat peut engendrer des problèmes : lectures sales, lectures non répétables et phantoms. Pour y remédier, le standard SQL définit quatre niveaux d'isolation, offrant un compromis entre intégrité et performance.

Problèmes de concurrence

• Lecture sale : Lire des données modifiées par une autre transaction qui n'a pas encore été validée.
• Lecture non répétable : Obtenir des résultats différents pour la même requête au sein d'une même transaction, car une autre transaction a modifié et validé les données entre-temps.
• Phantom : L'ensemble de résultats d'une requête change entre deux exécutions dans la même transaction, à cause d'insertions ou suppressions effectuées par d'autres transactions.

Les quatre niveaux d'isolation

De la plus permissive à la plus restrictive :

1. Read Uncommitted (Lecture non validée) : Permet les lectures sales, non répétables et les phantoms. Rarement utilisé.
2. Read Committed (Lecture validée) : Élimine les lectures sales. Niveau par défaut pour de nombreuses bases de données autres que MySQL/InnoDB.
3. Repeatable Read (Lecture répétable) : Élimine les lectures sales et non répétables. C'est le niveau par défaut d'InnoDB dans MySQL.
4. Serializable (Sérialisable) : Élimine tous les problèmes en forçant l'exécution sérielle des transactions. Le plus restrictif.

Configuration dans MySQL

Pour inspecter le niveau d'isolation actuel de la session :

-- Pour MySQL 8.0 et versions ultérieures
SELECT @@transaction_isolation;

-- Pour MySQL 5.7
SELECT @@tx_isolation;

Pour modifier le niveau d'isolation :

-- Pour la session en cours
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Au niveau global (prend effet après le redémarrage du serveur)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Illustration : Prévention des lectures non répétables

Considérons deux transactions accédant au même enregistrement. La transaction B effectue deux lectures successives. Sous le niveau Read Committed, elle verra la modification apportée et validée par la transaction A entre les deux lectures. Sous Repeatable Read, la transaction B obtiendra un instantané (snapshot) au début de sa première lecture, garantissant ainsi la cohérence des résultats tout au long de sa durée de vie.

MVCC : Contrôle de concurrence par versions multiples

MVCC (Multi-Version Concurrency Control) est la technique clé employée par InnoDB pour implémenter les niveaux d'isolation Read Committed et Repeatable Read. Son principe est de maintenir plusieurs versions d'une ligne de données, permettant ainsi aux lectures de ne pas bloquer les écritures, et vice-versa.

Composants fondamentaux du MVCC

Le MVCC repose sur trois éléments interconnectés :

1. Journal d'annulation (Undo Log) : Enregistre les anciennes valeurs des lignes modifiées, permettant leur restauration en cas d'annulation de transaction.
2. Chaîne de versions : Chaque ligne de données contient des colonnes cachées, notamment DB_TRX_ID (identifiant de la transaction modifiant la donnée) et DB_ROLL_PTR (pointeur vers l'entrée correspondante dans le journal d'annulation). Ces pointeurs forment une chaîne liant toutes les versions historiques d'une ligne.
3. Vue de lecture (Read View) : Créée par une transaction au moment d'une requête SELECT, elle définit les règles de visibilité. Elle contient la liste des identifiants de transactions actives (m_ids), l'identifiant minimal (min_trx_id), l'identifiant maximal suivant à attribuer (max_trx_id), et l'identifiant de la transaction créatrice (creator_trx_id).

Logique de visibilité

Pour déterminer si une version de donnée (identifiée par son trx_id) est visible pour une transaction donnée, le système vérifie :

1. Si trx_id < min_trx_id : La version provient d'une transaction déjà validée. Visible.
2. Si trx_id ≥ max_trx_id : La version provient d'une transaction démarrée après la création de la vue. Non visible.
3. Si trx_id se situe entre min_trx_id et max_trx_id : Vérifier si trx_id appartient à m_ids. Si oui (transaction active), non visible. Sinon, visible.

Comportement par niveau d'isolation

• Read Committed : Une nouvelle Read View est générée pour chaque instruction SELECT. Ainsi, une transaction peut voir les modifications validées par d'autres transactions entre deux requêtes.
• Repeatable Read : Une seule Read View est créée au démarrage de la première requête SELECT de la transaction et est réutilisée pour toutes les lectures suivantes. Cela garantit la répétabilité des lectures.

Verrous de plage (Gap Locks / Next-Key Locks)

Au niveau d'isolation Repeatable Read, InnoDB utilise des verrous spécifiques pour prévenir les phantoms lors des requêtes utilisant des index. Le verrou composite par défaut sur les lignes est le Next-Key Lock, qui combine un verrou d'enregistrement sur la ligne indexée courante et un verrou de plage sur l'intervalle d'index qui la précède.

Types de verrous d'InnoDB

• Verrou d'enregistrement (Record Lock) : Verrouille un index précis correspondant à une seule ligne.
• Verrou de plage (Gap Lock) : Verrouille l'intervalle entre deux index, empêchant l'insertion de nouvelles lignes dans cet espace.
• Verrou Next-Key : Combine un verrou d'enregistrement et le verrou de plage précédant cet enregistrement. C'est le comportement par défaut.

Déclenchement et portée

Les verrous de plage sont généralement activés lors d'opérations de recherche par plage (ex: WHERE id > 3) ou via des index non uniques au niveau Repeatable Read. Une recherche précise via un index unique ne déclenchera généralement qu'un verrou d'enregistrement sur la ligne concernée.

Par exemple, si une table a des index avec les valeurs 1, 3, 5, 7 et qu'une transaction exécute SELECT ... FOR UPDATE WHERE id > 3, elle appliquera des verrous Next-Key sur les enregistrements 5 et 7, ainsi que des verrous de plage sur les intervalles (3,5), (5,7) et (7, +∞). Cela empêche toute insertion d'un nouvel enregistrement avec un id supérieur à 3 jusqu'à la fin de la transaction, évitant ainsi les phantoms.

Étude de cas : Prévention du surpaiement dans un système de règlement concurrentiel

Scénario

Une plateforme possède un pool budgétaire central. Plusieurs agents déclenchent simultanément des demandes de règlement, chacune devant déduire un montant du solde disponible du pool. Le système doit garantir que le total des déductions ne dépasse jamais le solde initial.

Le risque principal est une condition de concurrance (race condition) : plusieurs transactions lisent le même solde disponible, le jugent suffisant, et procèdent à la déduction, pouvant conduire à un solde négatif.

Solutions d'optimisme et de pessimisme

1. Approche pessimiste (verrouillage)

Elle convient aux scénarios à fort taux d'écriture. Elle consiste à acquérir un verrou exclusif sur la ressource partagée (la ligne du pool budgétaire) avant de la lire et de la modifier, garantissant ainsi l'atomicité de l'opération "lire-vérifier-déduire".

Implémentation SQL (pessimiste) :

-- On suppose l'existence d'une table 'pool_budget' avec une colonne 'solde_disponible'
BEGIN;
-- Acquérir un verrou exclusif sur l'enregistrement (en supposant un index unique sur 'id')
SELECT solde_disponible FROM pool_budget WHERE identifiant = 1 FOR UPDATE;

-- Logique applicative pour vérifier si solde_disponible >= montant_regle
-- Si oui, exécuter :
UPDATE pool_budget SET solde_disponible = solde_disponible - 800.00 WHERE identifiant = 1;
COMMIT;

Extrait de code applicatif (Java/Spring) :

@Service
public class ServiceReglement {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public boolean effectuerReglement(BigDecimal montant) {
        BigDecimal soldeActuel = jdbcTemplate.queryForObject(
            "SELECT solde_disponible FROM pool_budget WHERE identifiant = 1 FOR UPDATE",
            BigDecimal.class
        );

        if (soldeActuel.compareTo(montant) < 0) {
            return false; // Solde insuffisant
        }

        int lignesModifiees = jdbcTemplate.update(
            "UPDATE pool_budget SET solde_disponible = solde_disponible - ? WHERE identifiant = 1",
            montant
        );
        return lignesModifiees == 1;
    }
}

2. Approche optimiste (versionnement)

Elle est adoptée aux scénarios avec beaucoup de lectures et peu de conflits d'écriture. Elle évite le verrouillage explicite en ajoutant un numéro de version (ou un horodatage) aux données. La mise à jour n'est effectuée que si la version n'a pas changé depuis la lecture initiale, évitant ainsi les conflits silencieux.

Implémentation avec version (SQL) :

-- La table 'pool_budget' contient une colonne supplementaire 'version'
BEGIN;
-- Lire le solde et le numéro de version
SELECT solde_disponible, version FROM pool_budget WHERE identifiant = 1;

-- Logique applicative pour vérifier si solde_disponible >= montant_regle
-- Si oui, exécuter (en incluant la condition sur la version) :
UPDATE pool_budget
SET solde_disponible = solde_disponible - 800.00, version = version + 1
WHERE identifiant = 1 AND version = <version_lue>;
COMMIT;

Si la mise à jour n'affecte aucune ligne (à cause d'une modification concurrente de la version), l'application peut décider de réessayer l'opération ou de retourner une erreur.

Comparaison des approches

• Pessimiste : Garantit une forte cohérence sans tentative de réessai, mais introduit une attente de verrouillage qui peut nuire aux performances sous haute concurrence.
• Optimiste : Offre de meilleures performances en lecture et évite les blocages, mais nécessite une logique de gestion des conflits (réessayes) et peut engendrer plus de trafic réseau en cas de conflits fréquents.

Le choix dépend du profil de charge et du taux de conflit attendu. Pour des systèmes critiques financiers avec des écritures concurrentes fréquentes, l'approche pessimiste est souvent privilégiée.