Examinons les mécanismes de verrouillage dans MySQL InnoDB à travers des scénarios variés, en fonction du niveau d'isolation et du type d'index utilisé. La table de test suivante sert de base :

CREATE TABLE `t_exemple` (
  `cle_prim` int(11) NOT NULL,
  `val_unique` int(11) DEFAULT NULL,
  `val_index` int(11) DEFAULT NULL,
  `col_donnee` int(11) DEFAULT NULL,
  PRIMARY KEY (`cle_prim`),
  UNIQUE KEY `idx_val_unique` (`val_unique`),
  KEY `idx_val_index` (`val_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Données initiales :

INSERT INTO t_exemple VALUES (1, 3, 5, 7), (3, 5, 7, 9), (5, 7, 9, 11), (7, 9, 11, 13);

Cas 1 : Niveau d'isolation RR (Répétable)

1.1 Clé primaire : Une requête SELECT ... FOR UPDATE sur une clé inexistante applique un verrou de ligne via la clé primaire, accompagné d'un verrou de gap. Résultat : 2 structures de verrou, 1 verrou de ligne.

1.2 Clé unique : Des verrous sont posés sur l'index unique et la clé primaire. Pour les enregistrements non trouvés, des verrous de gap sont ajoutés. Résultat : 3 structures de verrou, 2 verrous de ligne.

1.3 Index secondaire : Des verrous next-key sont utilisés sur l'index secondaire, avec des plages étendues. Par exemple, une requête sur une valeur comme 8 modifie la portée des verrous de gap. Résultat : 4 structures de verrou, 3 verrous de ligne.

1.4 Aucun index : Tous les enregistrements subissent des verrous next-key, incluant un enregistrement "supremum", simulant un verrou de table. Résultat : 2 structures de verrou, 6 verrous de ligne.

Cas 2 : Niveau d'isolation RC (Lecture validée)

2.1 Clé primaire : Seules les lignes correspondantes sont verrouillées via la clé primaire. Résultat : 2 structures de verrou, 1 verrou de ligne.

2.2 Clé unique : Des verrous sont appliqués sur l'index unique et la clé primaire. Résultat : 3 structures de verrou, 2 verrous de ligne.

2.3 Index secondaire : Des verrous de ligne sur l'index secondaire et la clé primaire. Résultat : 3 structures de verrou, 2 verrous de ligne.

2.4 Aucun index : Bien que tous les enregistrements soient verrouillés initialement, MySQL libère les verrous sur les lignes non pertinentes via la méthode unlock_row, optimisant ainsi les performances. Résultat : 2 structures de verrou, 1 verrou de ligne.

Cas 3 : Opérations d'insertion et de suppression

Lors de l'exécution d'une requête ENSERT INTO ... SELECT, des verrous de lecture sont acquis sur la table source pour les lignes parcourues. Cela peut entraîner des conflits, par exemple avec des opérations DELETE concurrentes sur la même table.

Les paramètres tels que innodb_autoinc_lock_mode=2 garantissent une incrémentation monotone mais potentiellement non continue des clés auto-incrémentées.

Des sorties transactionnelles peuvent montrer des attentes de verrous, comme lorsqu'une transaction tente de verrouiller une ligne déjà occupée par une opération d'insertion longue.