// Référence des erreurs PostgreSQL

PostgreSQL 40P01 : interblocage détecté

Deux transactions ou plus attendaient sur les verrous les unes des autres. PostgreSQL en a annulé une pour que l’autre puisse se terminer ; réessayez celle qui a échoué.

SQLSTATE
40P01
Nom
deadlock_detected
Catégorie
Annulation de transaction
Message d'erreur
deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321.
Process 4321 waits for ShareLock on transaction 8765; blocked by process 1234.

Ce que cela signifie

Un interblocage est un cycle d'attentes de verrous : la transaction A détient un verrou dont B a besoin tout en attendant un verrou que B détient. Aucune des deux ne peut jamais avancer, donc quand une attente de verrou dure deadlock_timeout (1 seconde par défaut) PostgreSQL vérifie la présence d'un cycle et, s'il en trouve un, annule l'une des transactions avec 40P01.

La transaction annulée est entièrement annulée (rollback). L'autre continue. Les DETAIL lignes montrent quels processus étaient impliqués et la CONTEXT indiquant quelle instruction était en attente.

Causes courantes

  • Deux transactions mettent à jour les mêmes lignes dans un ordre différent, par exemple une transfère de l’argent du compte 1 vers le compte 2 tandis qu’une autre va du compte 2 vers le compte 1.
  • Clés étrangères : l'insertion de lignes enfants prend un FOR KEY SHARE verrou sur la ligne parente, ce qui peut entrer en conflit avec une autre transaction mettant à jour ce parent.
  • Transactions longues qui touchent beaucoup de lignes, ce qui élargit la fenêtre propice à des ordres de verrouillage qui se chevauchent.
  • Explicite LOCK TABLE ou SELECT ... FOR UPDATE pris dans des ordres différents selon les chemins de code.

Comment le corriger

  1. Réessayez la transaction qui a échoué. Un interblocage est une issue normale de la concurrence, et l'application doit intercepter SQLSTATE 40P01 (et 40001) et réexécutez toute la transaction.

  2. Prenez des verrous dans un ordre cohérent partout, par exemple toujours verrouiller l'identifiant le plus petit en premier.

    BEGIN;
    SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
  3. Gardez les transactions courtes : effectuez d'abord les lectures et les calculs, puis les écritures, et validez rapidement.

  4. Activer log_lock_waits les attentes supérieures à deadlock_timeout sont enregistrées avec les instructions impliquées, puis consultez le journal du serveur.

    ALTER SYSTEM SET log_lock_waits = on;
    SELECT pg_reload_conf();

Reproduisez-le en toute sécurité

Exécutez les étapes dans deux connexions séparées, dans l'ordre, sur une base de données de test.

-- Session 1:
CREATE TABLE dl (id int PRIMARY KEY, v int);
INSERT INTO dl VALUES (1, 0), (2, 0);
BEGIN; UPDATE dl SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN; UPDATE dl SET v = 2 WHERE id = 2;
UPDATE dl SET v = 2 WHERE id = 1;   -- waits
-- Session 1:
UPDATE dl SET v = 1 WHERE id = 2;   -- one session gets ERROR: deadlock detected

Questions

Quelle transaction PostgreSQL interrompt-il en cas d'interblocage ?

Celle dont la détection d’interblocage a trouvé le cycle, ce qui n’est pas prévisible en général. Écrivez chaque transaction de manière à ce qu’elle puisse être réessayée.

Erreurs liées

Le code et le texte du message sont issus de Documentation PostgreSQL, annexe A : Codes d’erreur PostgreSQL. Parcourir la liste complète Liste des erreurs PostgreSQL ou Rechercher toutes les erreurs de base de données.

// moins d'erreurs, corrections plus rapides

Travailler sur PostgreSQL dans SQLly

Un client SQL natif pour macOS, Windows et Linux avec IntelliSense conscient du schéma, des garde-fous pour les instructions risquées et des messages d'erreur clairs, pour PostgreSQL et les autres bases de données que vous utilisez.