Cours MariaDB / Comparer isolation, verrous et interblocages avec deux connexions
Le niveau d’isolation détermine les changements simultanés qu’une transaction peut lire, tandis que les verrous coordonnent les opérations en conflit. Avec deux connexions distinctes et un ordre d’exécution contrôlé, vous pouvez comparer REPEATABLE READ et READ COMMITTED, puis distinguer une attente de verrou, un délai dépassé et un interblocage.
Préparer deux connexions distinctes A et B
Ouvrez deux clients en ligne de commande sur le même serveur d’apprentissage et la même base shopcourse, avec course_user. Le compte doit pouvoir créer, lire, modifier et nettoyer les objets d’exercice. Le guide du client MariaDB présente la connexion. Identifiez visuellement vos deux fenêtres comme A et B. Coller tous les blocs dans une seule fenêtre ne constitue pas un essai simultané.
Vérifiez le serveur et la base avec SELECT VERSION(), DATABASE();, et assurez-vous qu’aucune transaction utile n’est déjà ouverte. Les règles de COMMIT et ROLLBACK dans InnoDB expliquent ces frontières. Cet essai provoque volontairement des conflits : utilisez uniquement une base d’apprentissage isolée.
Exécutez le bloc suivant séparément dans A et dans B. Les CONNECTION_ID() doivent être différents. Les variables utilisateur sauvegardent les réglages propres à chaque connexion, pour les rétablir à la fin.
SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;
Vérifiez aussi les noms d’objets avec SHOW TABLES. Créez la table ci-dessous uniquement dans A. Si elle existe déjà, examinez sa définition et ses lignes, et conservez ce qui doit l’être avant de décider si elle peut être nettoyée. Ne remplacez pas une table d’application.
USE shopcourse;
CREATE TABLE accounts_36_lab (
account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);
La table InnoDB contient deux identifiants : 1 avec un solde de 1000, et 2 avec un solde de 2000. B doit également utiliser shopcourse avant les blocs suivants. Vous pouvez enregistrer uniquement ce bloc de préparation en UTF-8 puis l’exécuter avec SOURCE dans A ; les étapes A/B doivent ensuite être envoyées dans l’ordre indiqué, sans les regrouper dans un fichier exécuté d’un seul trait.
| Cas | Lecture ou coordination | Distinction utile |
|---|---|---|
| SELECT ordinaire en REPEATABLE READ | Instantané de lecture cohérente de la transaction | À distinguer d’une lecture verrouillante courante |
| SELECT ordinaire en READ COMMITTED | Version validée visible pour chaque instruction | Deux lectures peuvent différer |
| Attente de verrou | Attendre la libération d’un verrou incompatible | Délai dépassé et interblocage sont différents |
Comparer REPEATABLE READ dans l’ordre A, B, puis A
Commencez avec le solde 1000 de l’identifiant 1. Le premier SELECT ordinaire de A établit ici son instantané de lecture cohérente ; lancez le bloc de B seulement après ce résultat.
A-1 : établir l’instantané
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1 : modifier et valider
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2 : relire dans la même transaction
SELECT balance FROM accounts_36_lab WHERE account_id=1;
A-1 et A-2 renvoient tous deux 1000, même si B a validé 1100 entre les deux lectures. Il s’agit des SELECT ordinaires de cet exemple. Une lecture verrouillante comme SELECT ... FOR UPDATE n’a pas la même règle d’instantané et ne garantit pas de relire l’ancienne valeur.
A-3 : terminer puis lire à nouveau
ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
Après la fin de la transaction de A, la nouvelle lecture donne 1100. La référence SET TRANSACTION décrit les niveaux d’isolation et leur portée. SESSION règle les transactions suivantes de cette connexion ; aucun réglage GLOBAL n’est nécessaire ici.
Repartir de l’état initial avec READ COMMITTED
Terminez l’essai précédent avant de continuer. Dans B, le premier bloc rétablit le solde 1000 et le valide ; le solde de l’identifiant 2 reste 2000.
B-0 : réinitialiser le solde d’exercice
ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;
A-1 : première lecture en READ COMMITTED
ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1 : valider le solde 1100
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2 : seconde lecture puis fin
SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;
A lit d’abord 1000, puis 1100 dans sa même transaction : chaque lecture cohérente ordinaire utilise un nouvel instantané des données validées visibles au début de l’instruction. Cette observation ne suffit pas à choisir le niveau d’isolation d’une application ; les conflits d’écriture et les contraintes de cohérence du traitement comptent également.
Distinguer un verrou conservé d’un délai d’attente dépassé
Les transactions précédentes doivent être terminées. Dans A, lisez l’identifiant 1 avec FOR UPDATE à l’intérieur d’une transaction explicite, puis laissez cette transaction ouverte.
A-1 : conserver le verrou sur la ligne 1
START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;
B-1 : tenter de modifier la même ligne
SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
Tant que A conserve son verrou, B attend. Avec le délai de session fixé à 3, vous attendez une erreur de délai de verrou dépassé, généralement 1205, après environ trois secondes. Ce délai concerne l’attente du verrou, pas une promesse de durée totale exacte pour n’importe quelle requête.
Ne supposez pas que l’erreur a annulé toute la transaction de B. Terminez explicitement B, restaurez son délai, puis terminez A pour libérer son verrou :
B-2 : annuler l’essai et restaurer le délai
ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
A-2 : libérer le verrou
ROLLBACK;
La documentation FOR UPDATE précise ses conditions transactionnelles. L’essai oppose ici une lecture verrouillante à une écriture de la même ligne ; il ne faut pas en déduire que toutes les lectures ordinaires seraient bloquées de la même façon.
Créer un interblocage avec quatre étapes croisées
Vérifiez que les transactions précédentes sont terminées. Un interblocage, aussi appelé deadlock, apparaît ici lorsque A attend une ligne détenue par B et que B attend une ligne détenue par A. Respectez l’ordre suivant ; ce n’est pas un script séquentiel à lancer dans une seule connexion.
| Ordre | Connexion | Action |
|---|---|---|
| 1 | A | Modifier la ligne 1 |
| 2 | B | Modifier la ligne 2 |
| 3 | A | Demander la ligne 2 : attente |
| 4 | B | Demander la ligne 1 : cycle détectable |
A-1 : verrouiller la première ligne
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
B-1 : verrouiller l’autre ligne
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A-2 : demander la ligne détenue par B
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A attend maintenant. N’attendez pas que cette instruction de A se termine : passez immédiatement à la fenêtre B pour exécuter le bloc suivant. Sinon, vous risquez d’observer un simple délai d’attente au lieu du cycle voulu.
B-2 : demander la ligne détenue par A
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
Lorsque la détection des interblocages InnoDB est activée, une transaction peut recevoir l’erreur 1213, indiquant Deadlock found. Ne fixez pas à l’avance la connexion victime. L’autre instruction peut se débloquer après l’annulation de la victime ; ne validez pas pour autant les changements de cet essai. Exécutez ROLLBACK; séparément dans les deux fenêtres.
Le réglage innodb_deadlock_detect peut être consulté sans le modifier. S’il est désactivé, le cycle peut se terminer par un délai d’attente plutôt que par une détection immédiate. Les variables InnoDB de détection et d’attente expliquent ces conditions. Une application doit prévoir des reprises de transaction limitées et éviter les doubles opérations, pas seulement renvoyer aveuglément la dernière instruction.
Restaurer chaque session et réduire les conflits
Pour réduire le risque d’interblocage, accédez aux lignes dans un ordre de clés cohérent et gardez les transactions courtes. Des conditions larges ou l’absence d’index utiles peuvent étendre la zone concernée par les verrous. Un interblocage reste à analyser à partir des requêtes et de leur ordre réel ; cet exercice contrôle seulement deux lignes identifiées par leurs clés primaires.
Dans chacune des deux connexions, terminez les changements non validés et restaurez les valeurs sauvegardées au début :
ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;
Comparez les deux réglages affichés aux valeurs initiales de la connexion concernée. Les variables @saved_isolation et @saved_wait disparaissent à la fermeture de leur connexion : si vous avez reconnecté le client, ne les utilisez pas comme si elles contenaient encore les anciennes valeurs. Examinez les paramètres de la nouvelle session.
Après avoir terminé les transactions dans A et B, supprimez seulement la table d’exercice si rien n’y est à conserver. Exécutez ce dernier bloc une seule fois. Cette suppression enlève structure et données ; un ROLLBACK ordinaire ne la rétablit pas.
DROP TABLE shopcourse.accounts_36_lab;




