Dans MySQL utilisant le moteur de stockage InnoDB, les verrous de niveau ligne jouent un rôle crucial pour assurer l'isolation des transactions. Leur application peut varier entre verrous next-key, verrous de plage et verrous d'enregistrement selon le type de requête et d'index utilisé.

Requêtes SQL générant des verrous de niveau ligne

Les verrous de niveau ligne sont appliqués lors des lectures verrouillées ou des modifications de données. Par exemple :

-- Lecture avec verrou partagé (S)
SELECT * FROM produits WHERE id_produit = 100 LOCK IN SHARE MODE;

-- Lecture avec verrou exclusif (X)
SELECT * FROM produits WHERE id_produit = 100 FOR UPDATE;

-- Mise à jour ou suppression appliquant un verrou exclusif (X)
UPDATE produits SET prix = 50 WHERE id_produit = 100;
DELETE FROM produits WHERE id_produit = 100;

Ces requêtes doivent s'exécuter dans une tranasction, car les verrous sont libérés à la validation.

Types de verrous de niveau ligne

Les types de verrous dépendent du niveau d'isolation. En isolation "repeatable read", trois types existent :

• Record Lock : verrou exclusif sur une ligne spécifique.
• Gap Lock : verrou sur un intervalle, excluant la ligne elle-même, pour empêcher les insertions.
• Next-Key Lock : combinaison de Record Lock et Gap Lock, verrouillant un intervalle et la ligne.

Règles d'application des verrous

L'unité de base pour l'application des verrous est le next-key lock, qui peut se dégrader en record lock ou gap lock selon les conditinos.

Requête sur un index unique avec égalité

Considérons une table commandes avec un index unique sur id_commande :

CREATE TABLE commandes (
    id_commande BIGINT PRIMARY KEY,
    client VARCHAR(50),
    montant DECIMAL(10,2),
    INDEX idx_montant (montant)
) ENGINE=InnoDB;

Pour une requête SELECT * FROM commandes WHERE id_commande = 500 FOR UPDATE; :

• Si la ligne existe, le next-key lock se dégrade en record lock sur id_commande = 500.
• Si la ligne n'existe pas, le next-key lock se dégrade en gap lock sur l'intervalle précédant la première ligne supérieure.

Requête sur un index non unique avec plage

Pour un index non unique comme idx_montant, les règles diffèrent. Une requête SELECT * FROM commandes WHERE montant >= 200 FOR UPDATE; appliquera des next-key locks sur chaque ligne scannée, sans dégradation en gap lock ou record lock pour les index non uniques.

Scans complets de table

Si une requête de lecture verrouillée n'utilise pas d'index, un scan complet est effectué, appliquant des next-key locks sur toutes les lignes, ce qui équivaut à un verrouillage complet de la table.

Analyse des verrous appliqués

Pour inspecter les verrous actifs, utilisez :

SELECT * FROM performance_schema.data_locks\G;

Les champs LOCK_MODE indiquent le type :

• X : next-key lock.
• X, REC_NOT_GAP : record lock.
• X, GAP : gap lock.

Cela permet de vérifier l'application correcte des verrous et d'optimiser les requêtes pour éviter les blocages indésirables.