Réponse à la question

PostgreSQL met en œuvre l'Isolation de la Traduction Serializable (SSI) en utilisant le verrouillage de prédicat et le test de graphe de sérialisation pour atteindre une véritable sérialisation sans les pénalités de performance du verrouillage à deux phases traditionnel. L'erreur 40001 (serialization_failure) se produit spécifiquement lors de write skew ou de conflits lecture-écriture où deux transactions établissent un cycle de dépendance rw. Par exemple, la Transaction A lit des lignes satisfaisant un prédicat (par exemple, WHERE color = 'red'), la Transaction B lit des lignes satisfaisant un prédicat non chevauchant (par exemple, WHERE color = 'blue'), puis A met à jour des lignes en 'blue' tandis que B met à jour des lignes en 'red'. Aucune des transactions ne bloque l'autre, mais le résultat n'est pas sérialisable.

Ce modèle représente une structure dangereuse dans le graphe de sérialisation : deux rw-antidépendances consécutives formant un cycle potentiel. PostgreSQL détecte cela et interrompt une transaction pour prévenir des états anormaux. Le problème est subtil car les transactions peuvent modifier différentes lignes physiques, rendant le conflit invisible aux mécanismes de verrouillage des lignes utilisés dans des niveaux d'isolation inférieurs.

La solution mandatée exige que l'application mette en œuvre une boucle de reprise optimiste. Lors de la capture de SQL EXCEPTION '40001', l'application doit effectuer un rollback de la transaction actuelle et réessayer l'opération entière avec un backoff exponentiel. Contrairement aux blocages, qui se résolvent généralement par une reprise immédiate, les échecs de sérialisation sous forte contention bénéficient de délais aléatoires pour éviter les effectifs de foule.

-- Exemple de logique de reprise d'application en PL/pgSQL
DO $$
DECLARE
    retries INT := 0;
    max_retries INT := 3;
BEGIN
    WHILE retries < max_retries LOOP
        BEGIN
            SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
            
            PERFORM * FROM inventory WHERE category = 'electronics' AND count > 0;
            UPDATE inventory SET count = count - 1 WHERE item_id = 123;
            
            COMMIT;
            EXIT;
        EXCEPTION WHEN SQLSTATE '40001' THEN
            ROLLBACK;
            retries := retries + 1;
            PERFORM pg_sleep(power(2, retries) * 0.1); -- Backoff exponentiel
        END;
    END LOOP;
END $$;

Situation de la vie réelle

Une plateforme d'échange de billets de concert permettait aux utilisateurs d'échanger des catégories de sièges via une logique de vérification puis d'action. La Transaction A vérifiait que des sièges VIP étaient disponibles, puis déklassait un siège VIP réservé en Standard. Simultanément, la Transaction B vérifiait la disponibilité Standard et surclassait un siège Standard en VIP. Sous READ COMMITTED, les deux transactions lisent la disponibilité comme vraie, exécutent les mises à jour, et le système se termine avec un inventaire négatif dans les deux catégories malgré chaque transaction ayant vérifié les contraintes.

Trois solutions ont été architecturées. La première utilisait un verrouillage explicite SELECT FOR UPDATE, mais cela échouait lorsque les requêtes de disponibilité renvoyaient zéro ligne, n'acquérant aucun verrou et laissant le système vulnérable à des insertions fantômes. La deuxième approche implémentait des VERROUX CONSEILS en utilisant pg_try_advisory_lock() pour sérialiser l'accès aux catégories de sièges, ce qui a empêché les conflits mais a introduit des risques complexes de commande de verrou et a réduit le débit de 40 % en raison de la sérialisation de toutes les vérifications de catégories.

La troisième solution a adopté un niveau d'isolation SERIALIZABLE avec une boucle de reprise au niveau de l'application. Ceci a été choisi car il garantissait la correction sans gestion manuelle des verrous, et le surcoût de reprise était acceptable étant donné la faible fréquence des échanges simultanés par rapport aux opérations de lecture. L'implémentation utilisait un gestionnaire de reprise JDBC attrapant SQLException avec SQLState 40001, attendant 100ms * 2^tentative, et réexécutant la transaction. Cela a entièrement éliminé les incidents de sur-réservation, bien que la latence p99 ait augmenté de 15ms pendant les périodes de vente essentielles.

Ce que les candidats oublient souvent

Quelle est la différence précise entre les verrous de prédicat dans l'isolation Serializable et les verrous de ligne dans Repeatable Read ?

Repeatable Read empêche les lectures non répétables en verrouillant les lignes réellement retournées par une requête, mais cela n'empêche pas les lectures fantômes : nouvelles lignes insérées par d'autres transactions qui satisferaient la clause WHERE de la requête. L'isolation Serializable utilise des verrous de prédicat qui verrouillent la plage de recherche elle-même, empêchant toute insertion qui correspondrait au prédicat de requête, même dans des lignes qui n'existaient pas lorsque la requête a été exécutée. Les candidats confondent souvent ces éléments, croyant à tort que Repeatable Read empêche les lectures fantômes ou que Serializable verrouille uniquement les lignes existantes.

Comment l'algorithme de test du graphe de sérialisation détermine-t-il quelle transaction doit être interrompue lorsqu'un cycle est détecté ?

PostgreSQL utilise une stratégie "le premier qui engage gagne" combinée à la détection de structures dangereuses. Lorsque un rw-conflict (dépendance lecture-écriture) se forme entre des transactions concurrentes, le système suit si ce bord complète un cycle dans le graphe de sérialisation. La transaction qui complète le cycle est interrompue avec SQLSTATE 40001. Le choix est déterministe basé sur la structure du graphe plutôt que sur l'âge de la transaction, favorisant l'interruption des transactions dont le rollback est le moins coûteux ou le plus récent dans le cycle détecté. Comprendre qu'il s'agit d'une interruption préventive (prévenant une histoire invalide) plutôt que d'un blocage (attendant des verrous) est essentiel pour une bonne gestion des erreurs.

Pourquoi la commande SELECT FOR UPDATE pourrait-elle échouer à prévenir des échecs de sérialisation dans des scénarios où l'isolation Serializable détecte un conflit ?

SELECT FOR UPDATE acquiert des verrous ROW SHARE uniquement sur les lignes qui existent au moment de l'exécution. Dans des modèles de vérification puis d'action où la requête initiale renvoie zéro ligne (par exemple, vérifiant pour zéro siège disponible), FOR UPDATE n'acquiert aucun verrou, permettant à une autre transaction d'insérer la ligne conflictuelle. L'isolation Serializable détecte ceci comme un conflit de prédicat car le résultat des "zéro lignes" constitue un ensemble de lecture valide qui a été invalidé par l'insertion concurrente. Les candidats supposent souvent à tort que FOR UPDATE offre une protection complète, ne réalisant pas qu'il n'offre aucune défense contre les insertions fantômes lorsque le prédicat ne correspond initialement à rien.