PostgreSQL implementa Serializable Snapshot Isolation (SSI) utilizzando il locking predicativo e il testing del grafo di serializzazione per raggiungere una vera serializzabilità senza le penalità di performance del tradizionale locking a due fasi. L'errore 40001 (serialization_failure) si verifica specificamente durante scrittura distorta o conflitti lettura-scrittura dove due transazioni stabiliscono un ciclo di rw-dipendenza. Ad esempio, la Transazione A legge righe che soddisfano un predicato (ad es., WHERE color = 'red'), la Transazione B legge righe che soddisfano un predicato non sovrapposto (ad es., WHERE color = 'blue'), poi A aggiorna righe a 'blue' mentre B aggiorna righe a 'red'. Nessuna transazione blocca l'altra, ma il risultato è non serializzabile.
Questo schema rappresenta una struttura pericolosa nel grafo di serializzazione: due consecutive rw-antidependenze formano un potenziale ciclo. PostgreSQL rileva questo e annulla una transazione per prevenire stati anomali. Il problema è sottile perché le transazioni potrebbero modificare righe fisiche diverse, rendendo il conflitto invisibile ai meccanismi di locking a livello di riga utilizzati nei livelli di isolamento inferiori.
La soluzione imposta richiede all'applicazione di implementare un loop di retry ottimista. Quando si cattura SQL EXCEPTION '40001', l'applicazione deve effettuare il rollback della transazione corrente e riprovare l'intera operazione con un backoff esponenziale. A differenza dei deadlock, che tipicamente si risolvono riprovando immediatamente, i fallimenti di serializzazione sotto alta contesa beneficiano di ritardi jitterati per prevenire iterazioni di massa.
-- Esempio di logica di retry dell'applicazione in 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 esponenziale END; END LOOP; END $$;
Una piattaforma di scambio di biglietti per concerti consentiva agli utenti di scambiare categorie di posti tramite logica di controllo-allora-azione. La Transazione A verificava che i posti VIP fossero disponibili, poi degradava un posto VIP tenuto a Standard. Contemporaneamente, la Transazione B verificava la disponibilità degli Standard e aggiornava un posto Standard a VIP. Sotto READ COMMITTED, entrambe le transazioni leggevano la disponibilità come vera, eseguivano gli aggiornamenti e il sistema finiva con inventario negativo in entrambe le categorie nonostante ciascuna transazione controllasse i vincoli.
Sono state progettate tre soluzioni. La prima utilizzava locking esplicito SELECT FOR UPDATE, ma questo falliva quando le query di disponibilità restituivano zero righe, senza acquisire lock e lasciando il sistema vulnerabile a inserti fantasma. La seconda approccio implementava ADVISORY LOCKS utilizzando pg_try_advisory_lock() per serializzare l'accesso alle categorie di posto, il che preveniva conflitti ma introduceva rischi complessi di ordinamento dei lock e riduceva il throughput del 40% a causa della serializzazione di tutti i controlli di categoria.
La terza soluzione adottava l'isolamento SERIALE con un loop di retry a livello applicativo. Questa è stata scelta perché garantiva correttezza senza gestione manuale dei lock, e l'overhead del retry era accettabile dato il basso numero di scambi simultanei rispetto alle operazioni di lettura. L'implementazione utilizzava un gestore di retry JDBC che catturava SQLException con SQLState 40001, aspettando 100ms * 2^tentativo, e rieseguendo la transazione. Questo eliminava completamente gli incidenti di overbooking, anche se la latenza p99 aumentava di 15ms durante i picchi di vendita.
Qual è la precisa differenza tra i lock predicativi nell'isolamento Serializable e i lock di riga in Repeatable Read?
Repeatable Read previene letture non ripetibili bloccando le righe effettivamente restituite da una query, ma non previene letture fantasma—nuove righe inserite da altre transazioni che soddisferebbero la clausola WHERE della query. L'isolamento Serializable utilizza predicati locks che bloccano l'intervallo di ricerca stesso, prevenendo qualsiasi inserimento che corrisponderebbe al predicato della query, anche in righe che non esistevano quando la query venne eseguita. I candidati confondono spesso questi concetti, credendo erroneamente che Repeatable Read prevenga letture fantasma o che Serializable bloccasse solo righe esistenti.
Come determina l'algoritmo di testing del grafo di serializzazione quale transazione annullare quando viene rilevato un ciclo?
PostgreSQL utilizza una strategia "il primo a impegnare vince" combinata con la rilevazione di strutture pericolose. Quando si forma un rw-conflitto (dipendenza lettura-scrittura) tra transazioni concorrenti, il sistema tiene traccia se questo bordo completa un ciclo nel grafo di serializzazione. La transazione che completa il ciclo viene annullata con SQLSTATE 40001. La scelta è deterministica basata sulla struttura del grafo piuttosto che sull'età della transazione, favorendo l'annullamento delle transazioni il cui rollback è meno costoso o più recente nel ciclo rilevato. È fondamentale comprendere che questo è un aborto preventivo (per prevenire una storia invalida) piuttosto che un deadlock (in attesa di lock).
Perché SELECT FOR UPDATE potrebbe non riuscire a prevenire i fallimenti di serializzazione in scenari in cui l'isolamento Serializable rileva un conflitto?
SELECT FOR UPDATE acquisisce lock ROW SHARE solo sulle righe che esistono al momento dell'esecuzione. Nei modelli di check-then-act in cui la query iniziale restituisce zero righe (ad es., controllare per zero posti disponibili), FOR UPDATE non acquisisce alcun lock, consentendo a un'altra transazione di inserire la riga in conflitto. L'isolamento Serializable rileva questo come un conflitto predicativo poiché il risultato "zero righe" costituisce un set di lettura valido che è stato invalidato dall'inserimento concorrente. I candidati assumono spesso erroneamente che FOR UPDATE fornisca una protezione completa, non rendendosi conto che non offre alcuna difesa contro inserimenti fantasma quando il predicato inizialmente non corrisponde a nulla.