Diagnostiquer les interblocages

Deux transactions peuvent chacune détenir un verrou dont l’autre a besoin. Le prêt ou la mise à jour attend alors sans pouvoir avancer : c’est un interblocage, même si chaque requête prise isolément est valide. Vous allez provoquer ce cas sur deux exemplaires, lire le diagnostic InnoDB et définir une reprise qui ne valide pas une opération partielle.

Comprendre le cycle d’attente

Un interblocage survient lorsque la transaction A détient le verrou sur l’exemplaire 1 et attend l’exemplaire 2, tandis que la transaction B détient l’exemplaire 2 et attend l’exemplaire 1. Chaque transaction attend donc une ressource détenue par l’autre : l’attente forme un cycle. InnoDB détecte ce cycle et annule une transaction, appelée victime, pour libérer ses verrous et permettre à l’autre de continuer.

Pour observer le phénomène, ouvrez deux connexions distinctes à la même base MariaDB. L’exemple suppose que la table `exemplaires` contient au moins deux lignes ; chaque session sélectionne les deux mêmes identifiants. Dans la session A, exécutez le premier verrouillage, puis passez à la session B avant de lancer son second verrouillage.

Provoquer l’interblocage sur deux sessions

Les variables de session choisissent les deux premiers exemplaires existants dans les deux connexions. Exécutez les commandes jusqu’au commentaire indiquant la pause ; ne lancez pas tout le bloc en une seule fois. Les transactions ne modifient aucune donnée : elles servent uniquement à rendre visible l’attente des verrous.

-- Session A : choisissez les deux mêmes identifiants dans les deux sessions.
SET @id_1 = (
    SELECT MIN(exemplaire_id)
    FROM exemplaires
);
SET @id_2 = (
    SELECT MIN(exemplaire_id)
    FROM exemplaires
    WHERE exemplaire_id > @id_1
);

START TRANSACTION;

-- A détient le premier verrou.
SELECT exemplaire_id
FROM exemplaires
WHERE exemplaire_id = @id_1
FOR UPDATE;

-- Faites maintenant exécuter à la session B son premier SELECT ... FOR UPDATE.
-- Reprenez ensuite ici : ce SELECT attendra le verrou détenu par B.
SELECT exemplaire_id
FROM exemplaires
WHERE exemplaire_id = @id_2
FOR UPDATE;

-- Session B : utilisez les deux mêmes identifiants, dans l’ordre inverse.
SET @id_1 = (
    SELECT MIN(exemplaire_id)
    FROM exemplaires
);
SET @id_2 = (
    SELECT MIN(exemplaire_id)
    FROM exemplaires
    WHERE exemplaire_id > @id_1
);

START TRANSACTION;

-- B détient le second verrou.
SELECT exemplaire_id
FROM exemplaires
WHERE exemplaire_id = @id_2
FOR UPDATE;

-- Après le premier SELECT de A, lancez ce SELECT : le cycle est formé.
SELECT exemplaire_id
FROM exemplaires
WHERE exemplaire_id = @id_1
FOR UPDATE;

L’une des requêtes en attente reçoit généralement l’erreur 1213, « Deadlock found when trying to get lock ». InnoDB annule la transaction victime ; l’autre peut ensuite obtenir le verrou libéré. Si aucune transaction n’est victime, vérifiez l’ordre des commandes et que les deux sessions ciblent bien les mêmes lignes.

Lire le diagnostic et distinguer les erreurs

Après l’erreur, la session qui dispose encore de sa transaction peut la terminer avec `ROLLBACK`. Pour obtenir un rapport détaillé sur le dernier interblocage détecté par InnoDB, exécutez la commande suivante dans une session autorisée à consulter ces informations. Le rapport indique les transactions concernées, les verrous détenus ou attendus et la transaction choisie comme victime ; utilisez-le pour identifier les requêtes et l’ordre de prise des verrous qui ont créé le cycle.

-- À exécuter après l’incident pour examiner le dernier rapport InnoDB.
SHOW ENGINE INNODB STATUS;

Ne confondez pas l’erreur 1213 avec l’erreur 1205, qui signale une expiration du délai d’attente d’un verrou. Ces erreurs appellent une gestion prudente : dans les deux cas, l’application doit abandonner la transaction en cours et vérifier son état avant de recommencer. Après un interblocage, InnoDB annule la transaction victime ; après un délai d’attente, ne supposez pas que toute la transaction a été annulée. Un `ROLLBACK` explicite ramène l’opération à un état connu.

Réduire le risque et reprendre sûrement

La prévention la plus directe consiste à verrouiller les lignes dans le même ordre dans toutes les opérations. Pour les deux exemplaires de l’exemple, chaque transaction doit verrouiller l’identifiant le plus petit, puis le plus grand ; aucune ne prend ainsi les verrous en ordre inverse. Réduisez également le temps passé dans une transaction : effectuez les contrôles nécessaires sans délai, évitez d’attendre une intervention externe et validez ou annulez rapidement.

Si une erreur de verrou survient, ne reprenez pas seulement la dernière instruction ayant échoué. Annulez la transaction, puis recommencez l’opération métier entière depuis le début : relisez la disponibilité de l’exemplaire, refaites les contrôles et ne validez qu’après avoir obtenu un résultat cohérent. Limitez les tentatives et prévoyez un court délai croissant entre elles ; si le conflit persiste, remontez une erreur plutôt que de boucler indéfiniment.

À retenir

• Un interblocage est un cycle d’attente ; InnoDB le détecte et annule une transaction victime.
• Consultez `SHOW ENGINE INNODB STATUS` pour relier l’incident aux verrous et aux requêtes concernés.
• Prenez les verrous dans un ordre uniforme et gardez les transactions courtes.
• Après une erreur, annulez la transaction et recommencez toute l’opération après avoir refait ses contrôles.

La prochaine étape mettra ces pratiques en application dans le projet : vous créerez le schéma de la médiathèque et chargerez un jeu de données reproductible.