Verrouiller les exemplaires lors d’un prêt

Deux demandes de prêt peuvent arriver presque au même instant pour le même exemplaire. Si chacune vérifie sa disponibilité avant que l’autre ait écrit l’emprunt, les deux demandes risquent de réussir. Vous allez protéger cette décision en verrouillant la ligne de l’exemplaire, puis en contrôlant les emprunts actifs avant toute insertion.

Les transactions et les niveaux d’isolation ont été présentés dans les deux leçons précédentes. Ici, la protection repose sur un verrou explicite pris sur l’exemplaire concerné : la transaction concurrente doit attendre que la première se termine avant de poursuivre son propre contrôle.

Verrouiller la ligne avant de vérifier la disponibilité

Dans InnoDB, SELECT ... FOR UPDATE lit les lignes ciblées et les verrouille pour la transaction en cours. Le verrou est conservé jusqu’au COMMIT ou au ROLLBACK. Une autre transaction qui demande un verrou incompatible sur la même ligne attendra la fin de cette transaction ; elle ne peut donc pas effectuer en parallèle une décision fondée sur cette même ligne non verrouillée.

La requête doit rechercher l’exemplaire par sa clé primaire : elle identifie une ligne précise et évite de verrouiller inutilement une plage plus large. Si aucun exemplaire ne correspond à l’identifiant demandé, l’application doit abandonner l’opération. Après le verrouillage, contrôlez dans emprunts qu’il n’existe pas déjà de prêt actif pour cet exemplaire, défini ici par une valeur NULL dans date_retour.

Déroulement d’un prêt protégé

L’exemple ci-dessous montre les commandes à exécuter sur une connexion MariaDB 11. Remplacez les identifiants et les dates par des valeurs existantes et adaptées à votre jeu de données. Le code de l’application doit examiner le résultat du verrouillage, effectuer le contrôle d’emprunt actif, puis exécuter l’insertion uniquement si l’exemplaire est disponible ; toutes ces étapes restent dans la même transaction.

-- sql/transactions.sql — exécuter sur une connexion dédiée
START TRANSACTION;

-- Verrouille la ligne ciblée jusqu’à la fin de la transaction.
SELECT exemplaire_id
FROM exemplaires
WHERE exemplaire_id = 5001
FOR UPDATE;

-- Si la ligne existe, vérifier qu’aucun prêt n’est encore actif.
SELECT emprunt_id
FROM emprunts
WHERE exemplaire_id = 5001
  AND date_retour IS NULL;

-- L’application n’exécute l’insertion que si le SELECT précédent
-- ne renvoie aucune ligne.
INSERT INTO emprunts (
    adherent_id,
    exemplaire_id,
    date_emprunt,
    date_retour_prevue,
    date_retour
)
VALUES (
    42,
    5001,
    '2025-03-08',
    '2025-03-22',
    NULL
);

-- À exécuter uniquement après confirmation de la réussite du prêt.
COMMIT;

-- En cas d’exemplaire absent, déjà emprunté ou d’erreur SQL :
-- exécuter ROLLBACK à la place de COMMIT.

Les deux requêtes de contrôle ne remplacent pas la décision de l’application : celle-ci doit vérifier leurs résultats et choisir entre poursuivre et annuler. Si l’exemplaire est déjà emprunté, ne lancez pas l’INSERT et terminez par ROLLBACK. En production, utilisez des paramètres liés pour les identifiants et les dates plutôt que de construire le SQL par concaténation.

Pourquoi l’ordre des verrous compte

Le verrou doit être pris avant la vérification de l’emprunt actif. Si vous vérifiez d’abord, puis verrouillez, une autre transaction peut modifier la situation entre ces deux étapes. En verrouillant d’abord l’exemplaire, les demandes qui le ciblent se succèdent : la seconde transaction reprend son contrôle après la fin de la première et constate alors l’emprunt qui vient d’être validé.

Lorsque le traitement doit verrouiller plusieurs exemplaires, demandez les verrous dans un ordre stable, par exemple en ordre croissant de exemplaire_id. Des transactions qui prennent les mêmes verrous dans des ordres différents peuvent se bloquer mutuellement. Le diagnostic de ces interblocages et la stratégie de reprise seront traités dans la prochaine leçon ; ici, retenez surtout qu’une transaction doit rester courte et ne conserver les verrous que pendant les opérations nécessaires.

À retenir

• Verrouillez la ligne de l’exemplaire avec SELECT ... FOR UPDATE avant de vérifier sa disponibilité.
• Vérifiez l’absence d’emprunt actif et insérez le nouveau prêt dans la même transaction.
• Validez avec COMMIT seulement si toutes les étapes réussissent ; sinon, annulez avec ROLLBACK.
• Pour plusieurs exemplaires, prenez les verrous dans un ordre stable et gardez la transaction courte.

La leçon suivante porte sur le diagnostic des interblocages susceptibles de survenir lorsque plusieurs transactions attendent des verrous.