Les mécanismes de verrouillage sont essentiels pour garantir l'atomicité des transactions et permettre leur exécution simultanée. Bien qu'ils améliorent la concurrence, ils peuvent introduire des problèmes. Heureusement, en raison des exigences d'isolation des transactions, les verrous ne génèrent que trois types de problèmes principaux. En prévenant ces situations, on évite les anomalies de concurrence.

Lectures sales (Dirty Reads)

Commençons par définir les données sales, les pages sales et les lectures sales.

• Page sale : Il s'agit d'une page modifiée dans le pool de tampons (buffer pool) qui n'a pas encore été écrite sur le disque. Les données en mémoire diffèrent donc de celles sur disque. Les journaux de transactions sont cependant écrits dans le fichier de redo log (undo log) avant toute écriture sur disque.
• Données sales : Ce sont les modifications apportées aux enregistrements de lignes dans le pool de tampons par une transaction, avant que celle-ci ne soit validée. La lecture de pages sales est normale ; elle est due à l'asynchronisme entre la mémoire et le disque et n'affecte pas la cohérence des données à terme. Elle améliore également les performances.
• Lecture sale : Une transaction peut lire des données non validées par une autre transaction. Cela viole directement le principe d'isolation des bases de données.

Exemple de lecture sale :

CREATE TABLE t (a INT PRIMARY KEY);
INSERT INTO t VALUES (1);

Niveau d'isolation de la transaction :

• Read Uncommitted (Lecture non validée) : Le niveau le plus bas, n'offre aucune garantie.
• Read Committed (Lecture validée) : Évite les lectures sales.
• Repeatable Read (Lecture répétable) : Évite les lectures sales et les lectures non répétables.
• Serializable (Sériable) : Évite les lectures sales, les lectures non répétables et les lectures fantômes.

Dans l'exemple suivant, la session B lit la ligne insérée par la session A avant que cette dernière ne valide sa transaction. C'est une lecture sale.

-- Session A
START TRANSACTION;
INSERT INTO t VALUES (2);
-- La session A n'a pas encore fait COMMIT.

-- Session B
SELECT * FROM t WHERE a = 2; -- Session B peut lire le 2 non validé.

-- Session A
COMMIT;

Lectures non répétables (Non-Repeatable Reads)

Une lecture non répétable se produit lorsqu'une transaction lit plusieurs fois le même ensemble de données. Si, entre deux lectures, une autre transaction modifie et valide ces données, la première transaction obtiendra des résultats différents lors de ses lectures successives. La différence avec une lecture sale est que, dans le cas d'une lecture non répétable, les données lues ont été validées.

Mises à jour perdues (Lost Updates)

Bien que les bases de données puissent prévenir les problèmes de mise à jour, un problème logique de mise à jour perdue peut survenir dans les applications. Cela est courant dans les systèmes multi-utilisateurs. Par exemple, si un utilisateur dispose de 10 000 € et effectue deux transferts simultanés depuis deux clients bancaires différents (l'un de 9 000 €, l'autre de 1 €). Si les deux opérations sont finalisées, le solde final pourrait être de 9 999 €, mais le transfert de 9 000 € n'a pas été correctement appliqué à une seule des opérations. Pour éviter cela, les opérations concurrentes doivent être sérialisées.

Blocage de verrous (Lock Blocking)

Le blocage se produit lorsqu'un verrou détenu par une transatcion doit attendre la libération d'une ressource par une autre transaction en raison de la compatibilité des différents types de verrous. Ce n'est pas nécessairement un problème, car c'est un mécanisme pour assurer le bon déroulement des transactions concurrentes.

Dans InnoDB, le paramètre innodb_lock_wait_timeout (par défaut 50 secondes) contrôle la durée d'attente. innodb_rollback_on_timeout (par défaut 'off') détermine si une transaction doit être annulée en cas de dépassement du délai.

Par défaut, InnoDB ne déclenche pas de retour arrière automatique pour les erreurs de délai d'attente.

Pour vérifier les informations sur le blocage des verrous :

SELECT * FROM information_schema.innodb_trx\G; -- Informations sur les transactions courantes
SELECT * FROM information_schema.innodb_locks\G; -- Informations sur les verrous courants
SELECT * FROM information_schema.innodb_lock_waits\G; -- Informations sur les attentes de verrous
SELECT * FROM sys.innodb_lock_waits\G; -- Informations sur les attentes de verrous (alternative)
SHOW ENGINE INNODB STATUS\G;
SELECT * FROM performance_schema.events_statements_current\G; -- Instructions en cours d'exécution
SHOW FULL PROCESSLIST; -- Processus actifs

Interblocage (Deadlock)

Un interblocage survient lorsque deux transactions ou plus entrent dans un état d'attente mutuelle pour des ressources verrouillées.

Deux méthodes pour résoudre les interblocages au niveau de la base de données :

1. Transformer toute attente en retour arrière : La méthode la plus simple consiste à ne pas attendre. Si une attente se produit, annuler la transaction et la relancer. Cela évite les interblocages mais peut réduire les performances et rendre le système instable si les retours arrière sont trop fréquents.
2. Utiliser un délai d'attente : Lorsqu'une attente mutuelle se produit, si elle dépasse un seuil défini (via innodb_lock_wait_timeout), l'une des transactions est annulée, permettant à l'autre de continuer. Cette méthode est simple mais peut être inefficace si la transaction annulée est particulièrement lourde (par exemple, de nombreuses lignes mises à jour).

Gestion des interblocages par version MySQL :

• MySQL 5.6.x : Utilise la détection d'attente de verrou avec délai d'attente (timeout), sans résolution automatique d'interblocage.
• MySQL 5.7.x et versions ultérieures : Incluent un mécanisme de protection contre les interblocages activé par défaut.

Démonstration d'un interblocage :

Les interblocages ne se produisent que dans des contextes concurrents.

-- Préparation des tables
CREATE TABLE temp (id INT PRIMARY KEY, name VARCHAR(10));
INSERT INTO temp VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- Transaction 1 :
START TRANSACTION;
UPDATE temp SET name = 'aa' WHERE id = 1;

-- Transaction 2 :
START TRANSACTION;
UPDATE temp SET name = 'bb' WHERE id = 2;

-- Transaction 1 (suite) :
UPDATE temp SET name = 'aaa' WHERE id = 2; -- Attend le verrou de la Transaction 2 sur id=2

-- Transaction 2 (suite) :
UPDATE temp SET name = 'bbb' WHERE id = 1; -- Attend le verrou de la Transaction 1 sur id=1
-- Un interblocage se produit ici.

Méthodes pour éviter les interblocages :

1. Minimiser la durée des transactions : Des transactions courtes réduisent les risques.
2. Valider ou annuler rapidement : Libérez les verrous dès que possible.
3. Maintenir un ordre d'accès cohérent : Accédez aux tables et aux lignes dans le même ordre à chaque fois, idéalement en encapsulant les opérations dans des procédures stockées.
4. Utiliser des index appropriés : Réduisez le nombre de lignes scannées.
5. Minimiser l'utilisation des verrous : Privilégiez SELECT aux SELECT ... FOR UPDATE si l'isolation n'est pas critique.
6. Utiliser des verrous au niveau de la table : En dernier recours, pour sérialiser l'exécusion.

Contexte des verrous MySQL

Les verrous sont un mécanisme de sécurité pour maintenir la cohérence des données dans un environnement multi-thread. MySQL a des implémentations différentes selon le moteur de stockage. MyISAM ne supporte que les verrous de table, tandis qu'InnoDB supporte les verrous de ligne et de table. InnoDB est le moteur par défaut.

Moteur de stockage InnoDB

Les avantages principaux d'InnoDB sont le support des transactions et des verrous de ligne.

Transactions MySQL

Les transactions concurrentes dans un environnement à haute concurrence peuvent entraîner :

• Lecture sale : Une transaction lit des données non validées par une autre.
• Lecture non répétable : Une transaction lit des données différentes lors de lectures successives de la même ligne, car une autre transaction a modifié et validé ces données entre-temps.
• Lecture fantôme : Une transaction effectue des modifications sur un ensemble de données, mais une autre transaction insère de nouvelles lignes qui satisfont les critères de la première transaction, rendant le résultat de la première transaction incomplet. Les lectures fantômes concernent principalement les opérations INSERT.

Les niveaux d'isolation des transactions (Read Uncommitted, Read Committed, Repeatable Read, Serializable) visent à prévenir ces problèmes.

Types de verrous courants dans InnoDB

1. Verrous partagés (Shared Locks - S) et exclusifs (Exclusive Locks - X) :

   • S Lock : Permet la lecture de données. Plusieurs transactions peuvent détenir un S lock sur la même ressource.
   • X Lock : Permet la modification ou la suppression de données. Une seule transaction peut détenir un X lock sur une ressource. Une transaction détenant un S lock ne peut pas obtenir un X lock, et vice-versa.

   
   START TRANSACTION WITH CONSISTENT SNAPSHOT;
   SELECT * FROM category WHERE category_no = 2 LOCK IN SHARE MODE; -- Verrou partagé (S)
   SELECT * FROM category WHERE category_no = 2 FOR UPDATE; -- Verrou exclusif (X)
   COMMIT;
   
   START TRANSACTION WITH CONSISTENT SNAPSHOT;
   SELECT * FROM category WHERE category_no = 2 LOCK IN SHARE MODE; -- Verrou partagé (S)
   UPDATE category SET category_name = 'Anime' WHERE category_no = 2; -- Verrou exclusif (X)
   COMMIT;
   
   

2. Verrous d'intention (Intention Locks) : Ce sont des verrous au niveau de la table qui indiquent qu'une transaction a l'intention d'acquérir un verrou de ligne (S ou X). Ils existent en deux types :

   • Verrou d'intention partagé (IS Lock) : Indique qu'une transaction a l'intention d'acquérir un S lock sur une ligne.
   • Verrou d'intention exclusif (IX Lock) : Indique qu'une transaction a l'intention d'acquérir un X lock sur une ligne.

   Les verrous d'intention sont acquis automatiquement et n'empêchent pas les verrous de table sauf si une opération demande un verrou de table complet.

3. Verrous d'enregistrement (Record Locks) : Verrouillent une seule ligne dans la table. S'il existe un index, le verrou est appliqué sur l'index. Sinon, InnoDB crée un index caché. Il est donc préférable d'utiliser des index pour les requêtes afin de minimiser les conflits de verrous.

4. Verrous d'intervalle (Gap Locks) : Verrouillent l'espace entre les enregistrements d'index, ou avant le premier enregistrement, ou après le dernier. Ils sont utilisés pour prévenir les lectures fantômes lors de requêtes par plage. Par exemple, SELECT * FROM student WHERE grade > 72 FOR UPDATE verrouillera non seulement les enregistrements avec une note supérieure à 72, mais aussi les intervalles potentiels où de nouvelles données pourraient être insérées.

5. Verrous Next-Key (Next-Key Locks) : Combinaison d'un verrou d'enregistrement et d'un verrou d'intervalle. Ils verrouillent un enregistrement et l'intervalle suivant. Ils sont utilisés par défaut dans InnoDB pour la plupart des requêtes afin de garantir l'isolation Repeatable Read.

Exemple de Next-Key Lock :

CREATE TABLE `a` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uid` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `a` (uid) VALUES (1), (2), (3), (6), (10);

-- T1
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT * FROM a WHERE uid = 6 FOR UPDATE; -- Verrou Next-Key sur (3, 6]

-- T2
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Tentative d'insertion dans l'intervalle verrouillé par T1
INSERT INTO a (uid) VALUES (5); -- Bloqué
INSERT INTO a (uid) VALUES (7); -- Bloqué
INSERT INTO a (uid) VALUES (8); -- Bloqué
INSERT INTO a (uid) VALUES (9); -- Bloqué
INSERT INTO a (uid) VALUES (11); -- Possible si uid=10 n'est pas inclus dans le verrou suivant.

-- La T2 est bloquée sur l'insertion de uid=5 car le verrou Next-Key pour uid=6 couvre l'intervalle (3, 6].
-- Il faut attendre le COMMIT de T1 pour que T2 puisse continuer.